We know that transactions have four characteristics atomicity, consistency, isolation, and persistence. Today we’ll look at isolation and the relationship between multi-version concurrency control (MVCC) and isolation in Mysql.

Isolation of transactions

  • Read uncommitted: Changes made by a transaction can be seen by other transactions before it is committed. (Possibly dirty read, unrepeatable read, possibly phantom read)

  • Read Commit: After a transaction commits, its changes are seen by other transactions. (Non-repeatable, possibly unreal)

  • Repeatable read: The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. That is, when a transaction queries a record multiple times, it must see the same record. (Possibly unreal)

  • Serialization: As the name implies, for the same row, “write” will add “write lock”, “read” will add “read lock”. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue.

Note: Mysql’s default isolation level is repeatable. And InnoDB’s repeatable read level solves the illusory problem by gap locking.

Multi-version concurrency control

To put it simply, it is no longer a simple use of row lock to control the concurrency of the database, instead, it is to combine the row lock of the database with multiple versions of the row, with only a small cost, it can achieve unlocked read.

Note: Multi-version concurrency control only takes effect at read commit and repeatable read isolation levels.

Advantage:

  • Read without lock, read and write without conflict.

  • In OLTP applications where there are too many reads and too few writes, read and write conflicts are very important, which greatly improves the concurrency performance of the system.

What is multi-version illustrated graphically below

In addition, you may ask the undo log can not always exist, when to delete? The answer is that InnoDB decides that there are no transactions visible to it in the system, as you can see below.

The following diagram illustrates how InnoDB code controls version visibility through transactions.






The important thing is that InnoDB constructs an array for each transaction, which holds the ids of the current system and all started but uncommitted transactions at the moment the transaction is started.


Let’s illustrate this process in a diagram below, emphasizing that the following analysis is at the repeatable read isolation level

  1. If transaction A is enabled and transaction ID 100 is applied, all transaction IDS in the system are obtained at this time, assuming that there are 99,100, so transaction array maintained by transaction A is [99, 100], then according to the rules we said above, it can only see the updated data below 99, and its transaction 100, then it is about to check the record
  2. Transaction B is enabled, the requested transaction ID is 101, the transaction array maintained by transaction B is [99, 100, 101], is about to update the field value k = k + 1
  3. Transaction C (k = k + 1); transaction C (k = k + 1); transaction C (k = k + 1)
  4. After transaction C has performed a commit, transaction B has started to execute, and the data updated by transaction 102 (1,2) has become the historical version. The current version becomes the latest data updated by transaction # 101 (1,3).
  5. Assuming transaction B completes and commits (of course, it does not affect our results), and transaction A formally starts querying for row changes, what value should we get using multi-version concurrency control? Right, that’s 1,1.

Why (1, 1)? Because according to the rule above although 102 transactions, 101 transactions have been committed. Because of the multi-version concurrency control rule, transaction A can only see the data updated by transactions below 99 and its own transaction 100. According to this rule, InnoDB can query the data changed by transaction (1,3). More undo log rollback to (1,2) found transaction 102 update, continue rollback rollback to (1,1) found transaction 90 update, visible, return (1,1).

Above is our introduction to the entire content of the multi version concurrency control, thus the undo log only does not exist in the system did not commit the transaction, may be visible to its, will eliminate, to undo the log collection is not very friendly, the other foreign affairs also occupy lock resources, also have a great influence on the system, we must avoid long transaction oh.