1. Four isolation levels for transactions

There are four transaction isolation levels in Mysql:

Isolation level Dirty read Unrepeatable read Phantom read
Read uncommitted ✅ ✅ ✅
Reading has been submitted ❌ ✅ ✅
Repeatable read ❌ ❌ ✅
serialization ❌ ❌ ❌
  • Dirty read: a transaction that reads uncommitted data from another transaction.
  • Non-repeatable read: a transaction that reads the same data in different ways.
  • Phantom read: in a transaction, the same range query condition, before and after the read data is inconsistent.

“Read committed” and “repeatable read” in the implementation, the database will create a ReadView, access to the ReadView logical results shall be subject to.

① At the repeatable Read isolation level, this ReadView is created when the transaction is started and is used for the entire life of the transaction.

② At the “read commit” isolation level, this ReadView is created at the start of each SQL statement.

③ The latest value on the record is returned directly under the isolation level of “read uncommitted”, without the concept of view;

④ Under the serialization isolation level, the parallel access can be avoided by locking directly.

2. Read View (snapshot)

InnoDB implements the MVCC consistency read view to support read committed and repeatable read isolation levels.

InnoDB RR isolation boundary, MVCC controls record visibility, and the following key decision logic:

1. The transaction ID is not assigned at the beginning of the transaction, but when the transaction first performs a non-snapshot read (SELECT… FOR UPDATE/IN SHARE MODE, UPDATE, DELETE).

Note: InnoDB has special optimization for snapshot read only transactions if there are only snapshot reads in the transaction. These transactions do not have a transaction ID because they do not leave any changes in the system (even chain is not built), so there is no chance of leaving a transaction ID. SELECT TRX_ID FROM information_schema.innodb_trx WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID(); A large transaction ID (such as 328855902652352) will be output, but this is just a random ID assigned temporarily by MySQL to display.

2. A read_view object is created when a snapshot read is performed for the first time in each transaction. The read_view object is used to control the visible range of the logical snapshot created for the table in the current transaction. After the transaction commits, the read_view object it creates is destroyed.

There are three key fields in the READ_view object that determine the visibility of the record. These are trx_IDS, LOW_limit_id, and up_limit_id. Read_view -> trx_IDS: A collection of ids that record other transactions that are active when this read_view is created. Transaction ids are arranged in descending order in the collection for binary lookup. 2. Read_view ->low_limit_id: Maximum transaction ID+1 (the last unassigned transaction number in the system). Read_view ->up_limit_id: indicates the minimum transaction ID of the current active transaction.Copy the code

  1. If the version number of the record is smaller than read_view->up_limit_id of your own transaction, the current version of the record must be visible. Because the content of these versions was formed before the snapshot was created, their transactions must have been committed. Or if the version number of the record is equal to the transaction ID of its own transaction, the current version of the record must also be visible because the record version was created by the transaction.

  2. If the version number of the record is the same as or greater than your transaction’s read_view-> LOW_limit_id, the current version of the record for that version must not be visible. Because these versions of content are created after the snapshot is created.

Invisible has the following meanings: 1. If the record is a new version record that is added or modified, the record is invisible to the new version record that is added or modified, that is, the latest content cannot be seen. 2. If the record is a new version of the record marked as deleted, the deletion behavior is invisible, that is, the content before the deletion can be seen.Copy the code

  1. If the visibility of the record cannot be quickly determined by 4 and 5, check whether the version number of the record is in the read_view-> trx_IDS list of its own transaction. If yes, the current version of the record is not visible, otherwise, the current version of the record is visible.

  2. If the current version of a record is not visible, use the record’s DB_ROLL_PTR (undo segment pointer) to extract the previous version of the record from the undo segment of the current record for the same visibility judgment in 4 to 6. If possible, the previous version of the record is visible.