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@xxxxxxxxx> 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.
