Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how MySQL achieves transaction isolation". In daily operation, I believe many people have doubts about how MySQL achieves transaction isolation. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how MySQL achieves transaction isolation". Next, please follow the editor to study!

Catalogue

Concurrent scenario

Write-write

-read.-read.

Read-write and write-read

Locks in MySQL

Row level lock

Table level lock

Isolation level

Read Committed

Repeatable Read

Concurrent scenario

Recently, I have done some projects on distributed transactions to gain a deeper understanding of the isolation of transactions. I will write an article about distributed transactions later. Today, let's review how the isolation of stand-alone transactions is achieved.

The essence of isolation is to control concurrency if SQL statements are executed serially. Then there will be no concept of isolation among the four characteristics of the database, and there will be no dirty reading, unrepeatable reading, phantom reading and other problems.

There are only four kinds of concurrent operations on the database: write, read, read and write.

Write-write

When transaction A updates a record, can transaction B update the same record at the same time?

The answer is definitely no, otherwise it will cause dirty writing problems, so how to avoid dirty writing? The answer is to lock.

-read.-read.

MySQL reads are not locked by default, so they can be read in parallel

Read-write and write-read

Based on the fact that different scenarios have different tolerance for concurrent operations, MySQL has developed a concept of isolation. You choose the isolation level according to the business scenario.

√ means it will happen, x means it will not happen.

Isolation level dirty read not repeatable read uncommitted (unsubmitted read) √√√ read committed (submitted read) × √√ repeatable read (repeatable) × × √ serializable (serializable) × ×

So you see, MySQL controls concurrency over MySQL through locks and isolation levels.

Lock row level lock in MySQL

There are two types of row-level locks in the InnoDB storage engine

Shared Shared Lock (S lock for short), when a transaction needs to read a record, it needs to acquire the S lock of the modified record first.

Exclusive Lock (X lock), when a transaction wants to change a record, it needs to acquire the X lock of that record first.

If transaction T1 acquires the S lock of a record, transaction T2 also accesses that record. If transaction T2 wants to acquire the S lock of this record again, it can succeed, which is called lock compatibility. If transaction T2 wants to acquire the X lock of this record again, the operation will be blocked until the S lock is released after transaction T1 commits.

If transaction T1 acquires an X lock of a record, no matter whether transaction T2 then wants to acquire the record's S lock or X lock, it will be blocked until transaction 1 commits, which is called lock incompatibility.

Multiple transactions can read records at the same time, that is, shared locks are not mutually exclusive, but shared locks block exclusive locks. Exclusive locks are mutually exclusive

The compatibility relationship between S lock and X lock is as follows

Compatibility X lock S lock X lock mutex S lock mutex compatibility

Update,delete,insert automatically adds exclusive locks to the data involved, and select statements do not add any locks by default.

Under what circumstances will the read operation be locked?

Select... Lock in share mode, add S lock to the read record

Select... For update, add X lock to the read record

Read the record in the transaction and add an S lock to the read record

Transaction isolation level is under SERIALIZABLE, and S lock is added to the read record.

There are three types of locks in InnoDB

Record Lock: lock a single record

Gap Lock: a gap lock that locks the gap in front of the record and does not allow the record to be inserted

Next-key Lock: simultaneously lock the data and the gap in front of the data, that is, the gap in front of the data and the gap in front of the data are not allowed to insert records

Write a Demo to demonstrate.

CREATE TABLE `Jing` (`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

Lock a single record

If you add a Record Lock to the data with id value 8, the schematic diagram is as follows

Record Lock is also divided into S-lock and X-lock, and the compatibility is the same as previously described.

The lock added by SQL execution is restricted by many conditions, such as the isolation level of the transaction, the index used during execution (for example, clustered index, nonclustered index, etc.), so it will not be analyzed in detail, to give a few simple examples.

-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ uses primary key for equivalence query-- add S-type Record Lockselect * from girl where id=8 lock in share mode;-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ to id=8 records for equivalence query using primary key-- add X-type Record Lockselect * from girl where id=8 for update to id=8 records

Gap Lock

Lock the gap in front of the record and do not allow the insertion of the record

MySQL can solve the phantom reading problem through MVCC and locking under the repeatable read isolation level.

Current read: lock

Snapshot reading: 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, which can be solved by adding Gap Lock, that is, locking the gap.

If a transaction adds a gap lock to the id=8 record, it means that other transactions are not allowed to insert new records in front of the id=8 record, that is, records with id values in the range of (5,8) are not allowed to be inserted immediately. Records with id values in the interval (5,8) can not be committed until the transaction with a gap lock is committed.

Let's take a look at the following SQL locking process

-- REPEATABLE READ uses primary key for equivalence query-- but primary key value does not exist-- adds Gap LockSELECT * FROM girl WHERE id= 7 LOCK IN SHARE MODE to id=8 's clustered index record

Since the record of id=7 does not exist, in order to prohibit phantom reading (to avoid id=7 records in the result set obtained by executing the same statement under the same transaction), we need to prevent other transactions from inserting id=7 records before the current transaction is committed. At this time, we can add a Gap Lock to the id=8 records, 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 to lock the gap behind the last record?

In fact, the mysql data exists in the page, and each page has 2 pseudo records.

Infimum record, which represents the smallest record on the page

Upremum record, which represents the largest record on the page

To prevent other transactions from inserting records with id values in the range of (12, + ∞), we can add a gap lock to the Supremum records on the page where the id=12 records are located, which prevents other transactions from inserting new records with id values in the range of (12, + ∞).

Next-key Lock

At the same time, lock the gap between the data and the front of the data, that is, the gap between the data and the front of the data is not allowed to insert records.

So you can understand Next-key Lock=Record Lock+Gap Lock this way.

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

Because you want to solve the problem of phantom reading, you need to prohibit other transactions from inserting records with id > = 8, so

Add S-type Record Lock to the clustered index record of id=8

Add S-type Next-key Lock to all clustered index records with id > 8 (including Supremum pseudo records)

Table level lock

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

When a select,insert,update,delete statement is executed against a table, the innodb storage engine does not add table-level S or X locks to the table.

When DDL statements such as ALTER TABLE,DROP TABLE are executed on a table, an X lock is added to the table, so other transactions execute statements such as SELECT INSERT UPDATE DELETE on the table will block

Manually acquire the S lock or X lock of the table t provided by the InnoDB storage engine when the system variable autocommit=0,innodb_table_locks = 1, 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 the table, then

Other transactions can continue to acquire the S lock of the table

Other transactions can continue to acquire S locks for some records in the table

Other transactions cannot continue to acquire the X lock of the table

Other transactions cannot continue to acquire X locks for some records in the table

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

Other transactions cannot continue to acquire the S lock of the table

Other transactions cannot continue to acquire S locks for some records in the table

Other transactions cannot continue to acquire the X lock of the table

Other transactions cannot continue to acquire X locks for some records in the table

Therefore, you must be careful when modifying the online table, because it will block a large number of transactions. At present, there are many mature methods to modify the online table, so I will not repeat them.

Isolation level

Read unsubmitted: each time the latest record is read, no special treatment is made

Serialization: transactions are executed serially without concurrency

So we focus on reading committed and repeatable isolation implementations!

These two isolation levels are achieved through MVCC (multi-version concurrency control). In essence, MySQL stores multiple versions of historical data through undolog and reads a certain version of historical data according to rules, so that read-write parallelism can be achieved without lock, and database performance can be improved.

So how does undolog store pre-modified records?

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

Trx_id: every time a transaction makes changes to a clustered index record, it assigns the transaction id of the transaction to the trx_id hidden column

Roll_pointer: every time a clustered index record is changed, the old version is written to the undo log. This hidden column is equivalent to a pointer through which the information of the record before modification is found.

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

To determine which version in the version chain is visible to the current transaction, MySQL devised the concept of ReadView. The four important contents are as follows

M_ids: id list of active transactions in the current system when ReadView is generated

Min_trx_id: when generating ReadView, the smallest active transaction id in the current system, that is, the minimum value in m_ids

Max_trx_id: when generating ReadView, the transaction id value that the system should assign to the next transaction

Creator_trx_id: transaction id of the transaction that generated the ReadView

When insert,delete,update statements are executed when changes are made to the records in the table, a unique transaction id is assigned to the transaction, otherwise the transaction id value of a transaction defaults to 0.

Max_trx_id is not the maximum value in m_ids, and the transaction id is allocated incrementally. For example, now there are three transactions with a transaction id of 1 minus 2 and 3, and then a transaction with a transaction id of 3 is committed. When a new transaction generates ReadView, the values of m_ids include 1 and 2, that is, the values of 1 and 2, that is, the value of 1 and 2, that is, 4.

The execution process is as follows:

If the version of trx_id=creator_id is accessed, it means that the current transaction is accessing its own modified records, so the version can be accessed by the current transaction.

If the trx_id=max_trx_id version is accessed, the transaction that generated the version will not be opened until the current transaction generates ReadView, and the version cannot be accessed by the current transaction.

Whether the accessed version of trx_id is in the m_ids list

Yes, when ReadView was created, the version was still active and could not be accessed. Follow the version chain to find the next version of the 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

Well, we know the rules for getting version visibility, so how do we read committed and repeatable readings?

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

For example, first create the following table

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

Read Committed (read submitted), generating a ReadView before each read of the data

The following is the process of execution of three transactions, one line representing a point in time

First analyze the execution process of select at the point in time 5.

There are two transactions in the system, each with an id of 100200.

A ReadView,mids= [100200] and min_trx_id=100,max_trx_id=201,creator_trx_id=0 are generated when the select statement is executed (the transaction select does not perform a change operation, and the transaction id defaults to 0)

The latest version of name is listed as Xishi, and 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 column Wang Zhaojun, whose trx_ id value is 100, is also in the mids list, so it does not meet the requirements. Continue to skip to the next version.

The next version of name is listed as Diao Chan, and the trx_ id value of this version is 10, which is less than min_trx_id, so the last returned name value is Diao Chan.

Then analyze the execution process of select at the time of 8.

There is a transaction with a transaction id of 200in the system (a transaction with an id of 100has been committed)

Generate a ReadView,mids= [200], min_trx_id=200,max_trx_id=201,creator_trx_id=0 when executing the select statement

The latest version of name is listed as Yang Yuhuan. The trx_ id value of this version is 200. in the mids list, it does not meet the visibility requirements. Skip to the next version according to roll_pointer.

The next version of name is listed as Xishi, and the trx_ id value of this version is 100th, which is less than min_trx_id, so the final returned name value is Xishi.

When a transaction with a transaction id of 200is committed, the name obtained by the query is listed as Yang Yuhuan.

Repeatable Read

Repeatable Read (repeatable readable) to generate a ReadView the first time the data is read

Repeatable because ReadView is only generated when the data is read for the first time, so each time I read the same version, that is, the name value has always been Diao Chan. The specific process has been demonstrated twice, so I am not going to repeat it here. I am sure you will analyze it yourself.

At this point, the study on "how MySQL achieves transaction isolation" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.