Doubt in mvcc

Rama:

On Mon, Jul 13, 2020 at 9:52 AM Rama Krishnan <raghuldrag@gmail.com> wrote:

I m preparing for interview one of the recruiter asked me mvcc drawbacks as i told due to mvcc it use more space and need to perform maintenance activity.
Another one is the same data causes an update conflict because two different transactions can update the same version of the row.
he told its wrong, kindly tell me will you please tell me its correct or wrong?

I'm not sure I understand your question too well, you may want to
refresh/expand.

One interpretation is, on a pure MVCC contest, two transactions, say 5
and 6, could try to update a tuple valid for [1,) and end up
generating two new tuples, [5,), [6,) and closing the original at
either [1,5) or [1,6) .

That's why MVCC is just a piece, locking is other. On a MVCC the
tuples are locked while a transaction manipulates them. Other
transactions may read them, which is why readers do not block writers,
but two updates on the same tuple serialize.

Francisco Olarte.

Hi Francisco,

Still, I have a doubt as per your example both are trying to update the
same tuple so it may produce two different copies right?

I read some blocks they mentioned drawback above two things

It may lead to lost update also

For example, two transactions are going to increase the amount on the same
account by $100 . The first transaction reads the current value ($1000) and
then the second transaction reads the same value. The first transaction
increases the amount (this gives $1100) and writes this value. The second
transaction acts the same way: it gets the same $1100 and writes this
value. As a result, the customer lost $100.

Will u please provide more details

Serialize is the solution to this issue.

On Mon, 13 Jul, 2020, 14:12 Francisco Olarte, <folarte@peoplecall.com>
wrote:

On 7/13/2020 4:52 AM, Rama Krishnan wrote:

For example, two transactions are going to increase the amount on the
same account by $100 . The first transaction reads the current value
($1000) and then the second transaction reads the same value. The first
transaction increases the amount (this gives $1100) and writes this
value. The second transaction acts the same way: it gets the same $1100
and writes this value. As a result, the customer lost $100.

Will u please provide more details

Serialize is the solution to this issue.

MVCC applies to read and write situation. Readers do not block writers
and writers do not block read. The example you mentioned above is writer
vs writer and MVCC (both Oracle and PG) is not applicable.

Rama:

1st of all, please do not top post, specially if you want to discuss
examples, I'm not goint to hunt up and down for the relevant sections.

On Mon, Jul 13, 2020 at 10:52 AM Rama Krishnan <raghuldrag@gmail.com> wrote:

Still, I have a doubt as per your example both are trying to update the same tuple so it may produce two different copies right?

First, a loud advise. MY EXAMPLE IS NOT A REAL EXAMPLE OF HOW A REAL
MVCC DATABASE WORKS. It just tried to show that if you just use the
min-max transactions on storage without using more techniques, which
many people will consider part of MVCC, it will not work.

I read some blocks they mentioned drawback above two things
It may lead to lost update also
For example, two transactions are going to increase the amount on the same account by $100 . The first transaction reads the current value ($1000) and then the second transaction reads the same value. The first transaction increases the amount (this gives $1100) and writes this value. The second transaction acts the same way: it gets the same $1100 and writes this value. As a result, the customer lost $100.

PROPER mvcc, like postgres does, will not lead to this if used
properly. If both transactions use UPDATE first will read AND lock the
row, update value, write it, commit and unlock. Second will try to
read and WAIT for lock, read 1100, write 1200.

Some things happens if both use select for update and/or use the
adequate isolation levels to force the engine to use appropiate locks.

BUT if both transactions do a select, wait for a bit, then do an
update set ( NOTE: for a banking application the CORRECT way to do a
deposit is "update accounts set balance=balance+100", not "select
balance from accounts into $B; update accounts set balance=$B+100 ).
You may end up which what look like a lost update, but is really not a
DB problem. If you do it in two ops, the DB does not know they
correlate. For what it knows your bank might be a room with money in
tin boxes, you read it to chek a box, counted the box, noticed the
discrepancy and sent an update to fix it, and did it twice to be sure.
Update correlates more with "I opened the box and put 100$ in without
looking at what was there". Update returning would be "and I counted
the box afterwards", and select for update would be "I took the box to
my table, counted, added 100, counted the new pile, and returned the
box to the safe".

Will u please provide more details

No, I will not. You need to read a lot more than a mail can hold, MVCC
is a complex topic which I just do not fully understand and I'm not
going there. But there are tons of info floating around, more or less
easy to find.

Serialize is the solution to this issue.

That's what locks and isolation levels do.

Francisco Olarte.

On 7/13/20 12:31 AM, Rama Krishnan wrote:

Hi sir,

I m preparing for interview one of the recruiter asked me mvcc drawbacks
as i told due to mvcc it use more space and need to perform maintenance
activity.

Another one is the same data causes an update conflict because two
different transactions can update the same version of the row.
 he told its wrong, kindly tell me will you please tell me its correct
or wrong?

It depends on what you are calling a conflict and what isolation level
you are in. Take a look at this section of the docs:

https://www.postgresql.org/docs/12/transaction-iso.html

Thanks
RK

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 2020-07-13 at 13:01 +0530, Rama Krishnan wrote:

I m preparing for interview one of the recruiter asked me mvcc drawbacks as i told due
to mvcc it use more space and need to perform maintenance activity.

Yes. Generally speaking, you have to pay a price for keeping old versions of the
data around, no matter how you implement it.

Another one is the same data causes an update conflict because two different transactions
can update the same version of the row.
he told its wrong, kindly tell me will you please tell me its correct or wrong?

There is always a certain version (the latest) that can be updated, so this is
the same no matter if you have MVCC or not: if two sessions want to update the same
row, one has to wait until the other is done.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Mon, Jul 13, 2020 at 10:41:28AM +0200, Francisco Olarte wrote:

Rama:

On Mon, Jul 13, 2020 at 9:52 AM Rama Krishnan <raghuldrag@gmail.com> wrote:

I m preparing for interview one of the recruiter asked me mvcc drawbacks as i told due to mvcc it use more space and need to perform maintenance activity.
Another one is the same data causes an update conflict because two different transactions can update the same version of the row.
he told its wrong, kindly tell me will you please tell me its correct or wrong?

I'm not sure I understand your question too well, you may want to
refresh/expand.

One interpretation is, on a pure MVCC contest, two transactions, say 5
and 6, could try to update a tuple valid for [1,) and end up
generating two new tuples, [5,), [6,) and closing the original at
either [1,5) or [1,6) .

That's why MVCC is just a piece, locking is other. On a MVCC the
tuples are locked while a transaction manipulates them. Other
transactions may read them, which is why readers do not block writers,
but two updates on the same tuple serialize.

You might want to look at this:

https://momjian.us/main/presentations/internals.html#mvcc

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

Dear Ramakrishna,

"two different transactions can update the same version of the row"

This answer itself is wrong.

In my point of view, the drawback of MVCC is just holding multiple versions
of tuple in a table which leads to slowness in application access. the more
your table is bloated the more it takes to retrieve data.it has to scan so
much of _VM, so many pages which is time consuming.

The other drawback is anyway space.

There are a couple of workarounds to address the issue is what you should
tell your recruiter.

Any RDBMS has its own mechanism to address Isolation property and each
mechanism has it own flaws.

On Thu, Jul 23, 2020 at 12:16 AM Bruce Momjian <bruce@momjian.us> wrote: