How is MySQL transaction isolation achieved?

Update, delete, and insert statements will automatically add exclusive locks to the data involved. Select statements will not add any locks by default.

So under what circumstances will the read operation be locked?

• select ... lock in share mode, add S lock to the read records
• select ... for update , add X lock to the read records
• Read records in a transaction and add S locks to the read records
• When the transaction isolation level is SERIALIZABLE, an S lock is added to the read records.

There are three types of locks in InnoDB:

• Record Lock: Lock a single record
• Gap Lock: Gap lock, locks the gap in front of the record and does not allow records to be inserted
• Next-key Lock: locks both the data and the gap before the data, that is, records are not allowed to be inserted into the data or the gap before the data.

Write a Demo to demonstrate

CREATE TABLE `girl` (
  `id` int(11) NOT NULL,
  `name` varchar(255),
  `age` int(11),
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into girl values
(1, 'Xi Shi', 20),
(5, 'Wang Zhaojun', 23),
(8, 'Diao Chan', 25),
(10, 'Yang Yuhuan', 26),
(12, 'Chen Yuanyuan', 20);

Record Lock

Locking a single record

For example, add a Record Lock to the data with id value 8, as shown below:

Record Lock also has S lock and X lock, and the compatibility is the same as described before.

The type of lock added during SQL execution is subject to many conditions, such as the transaction isolation level and the index used during execution (such as clustered index, non-clustered index, etc.). Therefore, we will not analyze it in detail, but will give a few simple examples.

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ Use the primary key for equal value query -- Add S-type Record Lock to the record with id=8
select * from girl where id = 8 lock in share mode;

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ Use the primary key for equal value query -- Add X-type Record Lock to the record with id=8
select * from girl where id = 8 for update;

Gap Lock

Lock the gap in front of the record and do not allow records to be inserted

MySQL can solve the phantom read problem through MVCC and locking at the repeatable read isolation level

Current read: Locked snapshot read: MVCC

But how to lock it? Because these phantom records do not exist when the read operation is performed for the first time, we have no way to add Record Lock. At this time, we can solve the problem by adding Gap Lock, that is, locking the gap.

For example, if a transaction adds a gap lock to the record with id=8, it means that other transactions are not allowed to insert new records in the gap before the record with id=8, that is, records with id values ​​in the interval (5, 8) are not allowed to be inserted immediately. Records with id values ​​in the range (5, 8) can only be committed after the transaction with the gap lock is committed.

Let's look at the following SQL locking process

-- REPEATABLE READ uses the primary key for equal value query -- But the primary key value does not exist -- Add Gap Lock to the clustered index record with id=8
SELECT * FROM girl WHERE id = 7 LOCK IN SHARE MODE;

Since the record with id=7 does not exist, in order to prevent phantom reading (avoiding the record with id=7 in the result set obtained by executing the same statement in the same transaction), we need to prevent other transactions from inserting records with id=7 before the current transaction is committed. At this time, we can add a Gap Lock to the record with id=8, that is, other transactions are not allowed to insert new records with id values ​​in the range of (5, 8).

Let me ask you a question. Gap Lock can only lock the gap in front of the record, so how do you lock the gap after the last record?

In fact, MySQL data is stored in pages, and each page has 2 pseudo records

• Infimum record, indicating the smallest record in the page
• Upremum record, indicating the largest record in the page

In order to prevent other transactions from inserting records with id values ​​in the interval (12, +∞), we can add a gap lock to the Supremum record on the page where the record with id=12 is located. This will prevent other transactions from inserting new records with id values ​​in the interval (12, +∞).

Next-key Lock

Lock the data and the gap before the data at the same time, that is, no record can be inserted into the data or the gap before the data <br /> So you can understand Next-key Lock=Record Lock+Gap Lock like this

-- REPEATABLE READ uses the primary key for range query -- Add S-type Record Lock to the clustered index record with id=8
-- Add S-type Next-key Lock to all clustered index records with id>8 (including Supremum pseudo records)
SELECT * FROM girl WHERE id >= 8 LOCK IN SHARE MODE;

In order to solve the phantom read problem, it is necessary to prohibit other transactions from inserting records with id>=8, so

• Add an S-type Record Lock to the clustered index record with id=8
• Add S-type Next-key Lock to all clustered index records with id>8 (including Supremum pseudo records)

Table lock

Table locks can also be divided into S locks and X locks

When executing select, insert, update, or delete statements on a table, the InnoDB storage engine will not add a table-level S lock or X lock to the table.

When executing some DDL statements such as ALTER TABLE and DROP TABLE on a table, an X lock will be added to the table, so other transactions executing statements such as SELECT INSERT UPDATE DELETE on the table will be blocked.

When the system variables autocommit = 0 and innodb_table_locks = 1, manually obtain the S lock or X lock of table t provided by the InnoDB storage engine. You can write

Add a table-level S lock to table t

lock tables t read

Add a table-level X lock to table t

lock tables t write

If a transaction adds an S lock to a table, then

• Other transactions can continue to obtain the S lock of the table
• Other transactions can continue to obtain S locks for certain records in the table.
• Other transactions cannot continue to obtain the X lock of the table.
• Other transactions cannot continue to obtain X locks on certain records in the table.

If a transaction adds an X lock to a table, then

• Other transactions cannot continue to obtain the S lock of the table.
• Other transactions cannot continue to obtain S locks for certain records in the table.
• Other transactions cannot continue to obtain the X lock of the table.
• Other transactions cannot continue to obtain X locks on certain records in the table.

Therefore, you must be careful when modifying online tables, because it will block a large number of transactions . There are many mature methods for modifying online tables, which will not be described in detail.

Isolation Level

Read uncommitted: read the latest record each time without doing special processing Serialization: transactions are executed serially without concurrency

So we focus on the isolation implementation of read committed and repeatable read !

These two isolation levels are implemented through MVCC (Multi-version Concurrency Control). In essence, MySQL stores multiple versions of historical data through undolog, and reads data of a certain historical version according to the rules. In this way, read and write can be parallelized without locks, improving database performance.

So how does undolog store records before modification?

For tables using the InnoDB storage engine, the clustered index record contains the following two necessary hidden columns:

trx_id : Every time a transaction modifies a clustered index record, the transaction id of the transaction is assigned to the trx_id hidden column.

roll_pointer : Every time a clustered index record is modified, the old version is written to the undo log. This hidden column is equivalent to a pointer, through which the information before the record is modified can be found.

If the name of a record is changed from Diao Chan to Wang Zhaojun and Xi Shi, there will be the following records. Multiple records constitute a version chain.

In order to determine which version in the version chain is visible to the current transaction, MySQL designed the concept of ReadView . The 4 important contents are as follows

• m_ids : A list of active transaction ids in the current system when generating a ReadView
• min_trx_id : When generating a ReadView, the smallest transaction id currently active in the system, that is, the minimum value in m_ids
• max_trx_id : The transaction id value that the system should assign to the next transaction when generating a ReadView
• creator_trx_id : The transaction id of the transaction that generated the ReadView

When records in a table are modified, insert, delete, or update statements are executed, a unique transaction id is assigned to the transaction. Otherwise, the default transaction id value of a transaction is 0.

max_trx_id is not the maximum value in m_ids. Transaction ids are assigned incrementally. For example, there are three transactions with transaction ids 1, 2, and 3. Later, the transaction with transaction id 3 is committed. When a new transaction generates ReadView, the value of m_ids includes 1 and 2, the value of min_trx_id is 1, and the value of max_trx_id is 4.

The execution process is as follows:

• If the trx_id of the accessed version = creator_id, it means that the current transaction is accessing the record that it has modified, so the version can be accessed by the current transaction.
• If the trx_id of the accessed version is less than min_trx_id, it means that the transaction that generated the version has been committed before the current transaction generates the ReadView, so the version can be accessed by the current transaction.
• The trx_id of the accessed version is greater than or equal to max_trx_id, indicating that the transaction that generated this version was opened after the current transaction generated ReadView, and this version cannot be accessed by the current transaction
• Is the trx_id of the accessed version in the m_ids list?
• 4.1 Yes, when the ReadView is created, this version is still active and cannot be accessed. Follow the version chain to find the next version of data and continue to perform the above steps to determine visibility. If the last version is not visible, it means that the record is completely invisible to the current transaction.
• 4.2 No, when the ReadView is created, the transaction that generated the version has been committed and the version can be accessed
  

OK, now we know the rules for obtaining version visibility, but how do we implement read committed and repeatable read?

In fact, it is very simple, that is, the timing of generating ReadView is different

For example, first create the following table

CREATE TABLE `girl` (
  `id` int(11) NOT NULL,
  `name` varchar(255),
  `age` int(11),
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Read Committed

Read Committed (read committed), a ReadView is generated before each read

Below is the process of executing three transactions, one line represents one time point

First analyze the execution process of select at time point 5

• There are two transactions with transaction IDs 100 and 200 being executed in the system.
• When executing the select statement, a ReadView is generated, mids=[100,200], min_trx_id=100, max_trx_id=201, creator_trx_id=0 (the select transaction does not perform a change operation, and the transaction id defaults to 0)
• The name column of the latest version is Xishi. The trx_id value of this version is 100. In the mids list, it does not meet the visibility requirements. Jump to the next version according to roll_pointer
• The next version of name is Lie Wang Zhaojun. The trx_id value of this version is 100, which is also in the mids list. Therefore, it does not meet the requirements. Continue to jump to the next version
• The name column of the next version is Diao Chan. The trx_id value of this version is 10, which is less than min_trx_id. Therefore, the name value returned at the end is Diao Chan.
  

Let's analyze the execution process of select at time point 8 again

• There is a transaction with transaction ID 200 being executed in the system (transaction with transaction ID 100 has been committed)
• When executing the select statement, a ReadView is generated, mids=[200], min_trx_id=200, max_trx_id=201, creator_trx_id=0
• The name column of the latest version is Yang Yuhuan. The trx_id value of this version is 200. In the mids list, it does not meet the visibility requirements. Jump to the next version according to roll_pointer
• The name column of the next version is Xishi. The trx_id value of this version is 100, which is less than min_trx_id. Therefore, the name value returned at the end is Xishi.
  

When the transaction with transaction id 200 is committed, the name column obtained by query is Yang Yuhuan.

Repeatable Read

Repeatable Read generates a ReadView when reading data for the first time.

Repeatable read generates ReadView only when reading data for the first time, so the same version is read each time, that is, the name value is always Diao Chan. The specific process has been demonstrated twice above, so I will not repeat it here. I believe you will analyze it yourself.

Reference blog

[1] https://souche.yuque.com/bggh1p/8961260/gyzlaf
[2] https://zhuanlan.zhihu.com/p/35477890

This is the end of this article about how to achieve isolation of MySQL transactions. For more information about isolation of MySQL transactions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!