How innodb Cleverly Realizes Transaction Isolation Levels

Preface

In the previous article mysql Lock Mechanism Detailed Explanation, we explained the lock mechanism of innodb in detail. The lock mechanism is used to ensure the accuracy of data under concurrent conditions, and transaction support is usually needed to ensure the accuracy of data. The mysql storage engine innodb skillfully realizes the four isolation levels in the isolation characteristics of transactions through the lock mechanism.

Transaction ACID feature, where I stands for isolation (Isolation). Isolation means that when the concurrent transactions of multiple users access the same database, the transactions of one user should not be disturbed by the transactions of other users, and multiple concurrent transactions should be isolated from each other.

We all know several properties of transactions, and the uniformity and isolation in the database are the basic ideas to realize transactions. Under the condition that the system has a large number of concurrent accesses, understanding and skillfully applying the transaction isolation level of the database itself plays a key role in writing robust and concurrent codes.

1. How transactions interfere with each other

How does 1 transaction interfere with other transactions? For example, there is the following table:

create table lock_example(id smallint(10),name varchar(20),primary key id)engine=innodb;

There are the following data in the table:

1, zhangsan
2, lisi
3, wangwu

demo1:

Transaction A, executed first, in an uncommitted state:

insert into t values(4, 'zhaoliu');

Transaction B, executed later, also not committed:

select * from t;

If transaction B can read the record (4, zhaoliu), transaction A has an impact on transaction B, which is called "read dirty", that is, the record of uncommitted transaction operation is read.

demo2:

Transaction A, first execute:

select * from t where id=1;

The result set is

1,zhangsan

Transaction B, executed later, and committed:

update t set name=xxx where id=1;

commit;

Transaction A, execute the same query again:

select * from t where id=1;

The result set is:

1, xxx

This is the effect of committed transaction B on transaction A, which is called "unrepeatable read", that is, the same query within one transaction gets different results.

demo3:

Transaction A, first execute:

select * from t where id>3;

The result set is:

NULL

Transaction B, executed later, and commit:

insert into t values(4, zhaoliu);

commit;

Transaction A, querying id for the first time > The result of 3 is NULL, so I want to insert a record with 4:

insert into t values(4, xxoo);

The result set is:

Error : duplicate key!

You might think. . . Are you teasing me TM? Checked id > 3 is an empty set, and insert id = 4 tells me PK conflict? → _ →

This is the effect of committed transaction B on transaction A, which is called "phantom reading".

As mentioned above, concurrent transactions may lead to dirty reading, unrepeatable reading and imaginary reading of other transactions. In order to avoid the above situation, what efforts has innodb made?

2. What kinds of transaction isolation levels does InnoDB implement?

InnoDB implements isolation levels for four different transactions:

Read Uncommitted (Read Uncommitted) Read commit (Read Committed, RC) Repeatable Read (Repeated, Read, RR) Serialization (Serializable)

The isolation level of different transactions is actually a trade-off and compromise between uniformity and concurrency.

3. 4 transaction isolation levels. How does innodb implement?

InnoDB uses different locking policies (Locking Strategy) to achieve different isolation levels.

a. Read uncommitted (Read Uncommitted)

At this transaction isolation level, the select statement is not locked and is not a snapshot read.

SELECT statements are performed in a nonlocking fashion.

At this time, it is possible to read data without 1, that is, "read dirty". This is the isolation level with the highest concurrency and the worst uniformity.

b. Read commit (Read, Committed, RC)

Ordinary select is a snapshot read; Lock select, update, delete and other statements, except for blocking the interval during foreign key constraint check (foreign-key constraint checking) and repeated key check (duplicate-key checking), only record locks are used at other times; Gap lock (gap lock) and temporary lock (next-key lock) fail at this level;

At this point, the insertion of other transactions can still be performed, which may lead to the phantom record being read. This level is most commonly used. And if it is an unlocked select, it may produce unrepeatable reads.

At this level, snapshot reading is used to prevent dirty reading. Because a snapshot read at this level always reads the most recent snapshot of row data, which of course must be written by a committed transaction, unrepeatable readings may occur.

c. Repeatable Read (Repeated Read, RR)

This is the default isolation level for InnoDB, under RR:

Ordinary select uses snapshot reading (snapshot read), which is an unlocked one-dimensional reading (Consistent Nonlocking Read), and the bottom layer is implemented by MVCC; Lock select (select... in share mode/select... ES226update), update, delete, etc. whose locks depend on whether they use only 1 query condition (unique search condition, in this case, record lock) or range query condition (range-type search condition, in this case, gap lock or key lock) on only 1 index (unique index); Using only one query condition on only one index will use record lock (record lock) without blocking the interval between records, that is, gap lock (gap lock) and key lock (next-key lock) will not be used; Range query conditions or non-unique indexes will use gap locks and temporary key locks to lock the ranges between index records, avoid inserting records between ranges, avoid phantom row records, and avoid unrepeatable readings;

Under this level

Through snapshot reading and locking interval, phantom reading and unrepeatable reading are avoided; The first read record time of a transaction is T, and the records written by committed transactions after T time will not be read in the future, so as to ensure that the same read reads the same result set continuously, which can prevent unrepeatable reading; Under RR, the phantom reading problem is solved by gap lock and temporary key lock.

d. Serialization (Serializable)

At this transaction isolation level, all select statements are implicitly converted to select... in share mode, which is the shared read lock (S lock) by default.

Therefore, if the transaction A is executed first after the following sql, Attempts to acquire the IS lock of the queried row (compatible with other IS, IX locks), Other transactions can also acquire IS locks or even S locks for these rows, However, if the transaction A acquires the X lock if some rows of update or delete follow, other transactions will block even if they execute a normal select statement because they try to acquire the IS lock, but the IS lock and the X lock are mutually exclusive, thus avoiding dirty, unrepeatable and phantom reads, and all transactions can only be serial.

insert into t values(4, 'zhaoliu');

0

This is the isolation level with the best uniformity but the worst concurrency. The a and d isolation levels mentioned above are rarely used in high concurrency scenarios.

4. Summary

Interference between concurrent transactions may lead to dirty read, unrepeatable read, phantom read and other issues.

InnoDB implements the four isolation levels in the SQL92 standard:

Read uncommitted: select is not locked, which may lead to dirty reading; Read commit (RC): ordinary select snapshot read, lock select/update/delete will use record lock, which may cause unrepeatable read; Repeatable read (RR): Ordinary select snapshot read, lock select/update/delete according to query conditions, record lock or gap lock/temporary key lock will be selected to prevent phantom records from being read; Serialization: select is implicitly converted to select... in share mode will be mutually exclusive by update and delete;

The default isolation level for InnoDB is RR, and the most used isolation level is RC

Summarize