Every RDBMS got their own method of implementing Multi Version Concurrency Control (MVCC) to provide concurrent access to their respective Database Infrastructure.
How MySQL implemented MVCC?
MySQL keeps the copy of everything it changed in the form of undo record inside undo log so every time a record is changed in MySQL the current (old) version of data is transferred to undo log. Every record in MySQL maintains a reference to most recent undo record, It’s called rollback pointer or ROLL_PTR and every undo record carries reference to previous undo record, This chain goes long till the under record exist in undo log
Isolation Levels Explained!
- READ UNCOMMITTED: Expect transaction inconsistencies in this isolation level because it always read the newest record in index which isn’t even committed, This causes “dirty read"
- READ COMMITTED: Read committed records only based on the current maximum transaction ID at statement start. This look like good fit many times and many database systems (including Oracle) have default isolation level “ READ COMMITTED” but there is something to worry (not that serious though !) about here, the statement to statement user will see new data because the locks that did not match the scan are released after the statement completes
- REPEATABLE READ: This address the concern in READ COMMITTED isolation level by guaranteeing the consistent view of data through out the transaction because every lock acquired during transaction is held for the entire duration of the transaction
- SERIALIZATION: This make every transaction isolated, as though all transactions are executed serially. InnoDB implicitly converts all plain SELECT statements to “SELECT … LOCK IN SHARE MODE.