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
copiesright?
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
readsthe current value ($1000) and then the second transaction reads the same value. The first transaction increases
theamount (this gives $1100) and writes this value. The second transaction acts the same way: it gets the same $1100
andwrites 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.