MySQL Forums
Forum List  »  InnoDB

How does Innodb cleverly avoid read-write conflicts when implementing mvcc?
Posted by: Y M
Date: September 03, 2021 08:54AM

Insert into tableA(age,num) values(1,1)
Assume that there is a piece of data in the database.

At this time, under the read commit, two of the following transactions run concurrently at the same time:
Transaction 1: select * from tableA
Transaction 2: update tableA set age=2

The steps they run in the database are as follows:
1. Transaction 1 accesses the only piece of data in the page: the transaction id that accesses the row of data, and judges that the data is visible through the visibility rule
2. Transaction 2 locates the row of data and finds that the written age field is the same as the size occupied by the current data, so it starts to execute the replacement logic
3. Transaction 2 copies the value of the age field in the current data to undo, then points the undo pointer to the past, and the transaction id is updated
4. Transaction 2 writes the value 2 back to the age field of the current data
5. Transaction 1 starts to access the age field, reads that the current data value is 2, and accesses the value of the num field to 1, and returns (2, 1)
6. Transaction 2 commit

Through the operation of the above steps, you can see that the result returned by transaction 1 is not expected, and the root cause of this is that the action of reading the transaction id and the action of writing data (undo, transaction id, new data) are not mutually exclusive

mvcc is an access design, but the database engine will still compete for the same area when accessing and writing tuples. How does Innodb cleverly avoid read-write conflicts when implementing mvcc?

Options: ReplyQuote

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.