How InnoDB cleverly implements transaction isolation levels

Preface

In the previous article Detailed Explanation of MySQL Lock Mechanism, we explained in detail the lock mechanism of InnoDB. The lock mechanism is used to ensure the accuracy of data in concurrent situations. To ensure data accuracy, transactions are usually required. The MySQL storage engine InnoDB cleverly implements the four isolation levels in the isolation characteristics of transactions through the lock mechanism.

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

We all know the properties of transactions. Consistency and isolation in the database are the basic ideas for implementing transactions. When the system has a large number of concurrent accesses, understanding and skillfully applying the database's own transaction isolation level plays a key role in writing robust code with strong concurrent processing capabilities.

1. How transactions interfere with each other

How does one 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;

The table contains the following data:

1, zhangsan
2, lisi
3, wangwu

demo1:

Transaction A is executed first and is in an uncommitted state:

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

Transaction B, executed later, is also not committed:

select * from t;

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

demo2:

Transaction A, execute first:

select * from t where id=1;

The result set is

1,zhangsan

Transaction B is executed later and committed:

update t set name=xxx where id=1;

commit;

Transaction A executes the same query again:

select * from t where id=1;

The result set is:

1, xxx

This time, the committed transaction B has an impact on transaction A. This impact is called "non-repeatable read", that is, the same query within a transaction produces different results.

demo3:

Transaction A, execute first:

select * from t where id>3;

The result set is:

NULL

Transaction B is executed later and committed:

insert into t values(4, zhaoliu);

commit;

Transaction A first queries for id>3 and the result is NULL, so it wants to insert a record with id 4:

insert into t values(4, xxoo);

The result set is:

Error: duplicate key!

You might be thinking. . . Are you fucking kidding me? I checked and it was an empty set if id>3, but when I insert id=4 it told me there was a PK conflict? →_→

This time, the impact of the committed transaction B on transaction A is called "phantom read".

As mentioned above, concurrent transactions may cause dirty reads, non-repeatable reads, and phantom reads in other transactions. In order to avoid the above situation, what efforts has InnoDB made?

2. What transaction isolation levels does InnoDB implement?

InnoDB implements four different transaction isolation levels:

• Read Uncommitted
• Read Committed (RC)
• Repeatable Read (RR)
• Serializable

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

3. How to implement the four transaction isolation levels in InnoDB?

InnoDB uses different locking strategies to implement different isolation levels.

a. Read Uncommitted

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

SELECT statements are performed in a nonlocking fashion.

At this time, inconsistent data may be read, which is called "dirty read". This is the isolation level with the highest concurrency and the worst consistency.

b. Read Committed (RC)

• Normal select is snapshot read;
• Locked select, update, delete statements use only record locks except when foreign-key constraint checking and duplicate-key checking are in progress.
• Gap lock and next-key lock are invalid at this level.

At this time, the insertion of other transactions can still be executed, which may cause phantom records to be read. This level is the most commonly used. And if it is an unlocked select, non-repeatable read may occur.

At this level, dirty reads are prevented through snapshot reads. Because snapshot reads at this level can always read the latest row data snapshot, of course, it must be written by a committed transaction, so non-repeatable reads may occur.

c. Repeatable Read (RR)

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

• Ordinary select uses snapshot read, which is a consistent nonlocking read, and is implemented using MVCC at the bottom layer.
• Locked select (select ... in share mode / select ... for update), update, delete statements, etc., their locks depend on whether they use a unique search condition (in this case, record locks are used) or a range-type search condition (in this case, gap locks or adjacent key locks are used) on a unique index.
• When a unique query condition is used on a unique index, a record lock is used instead of blocking the intervals between records, that is, a gap lock and a next-key lock are not used.
• For range query conditions or non-unique indexes, gap locks and adjacent key locks are used to lock the range between index records to prevent inserting records between ranges, thereby avoiding the generation of phantom row records and non-repeatable reads.

At this level

• Avoid phantom reads and non-repeatable reads by using snapshot reads and locking intervals.
• The time when a transaction first reads a record is T. In the future, records written by transactions that have been committed after T will not be read to ensure that consecutive reads will read the same result set, which can prevent non-repeatable reads.
• Under RR, the phantom read problem is solved by gap lock and adjacent key lock;

d. Serializable

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

Therefore, if transaction A executes the following SQL statement first, it will try to obtain the IS lock of the queried row (which is compatible with other IS and IX locks). At this time, other transactions can also obtain the IS lock or even the S lock of these rows. However, if transaction A updates or deletes some of the rows next, it will obtain the X lock. Other transactions will be blocked even if they execute ordinary select statements because they try to obtain the IS lock. However, the IS lock and the X lock are mutually exclusive. This avoids dirty reads, non-repeatable reads, and phantom reads, and all transactions can only be executed serially.

select ... ;

This is the most consistent, but least concurrent isolation level. In high-concurrency scenarios, the above two isolation levels a and d are rarely used.

4. Conclusion

Mutual interference between concurrent transactions may cause problems such as dirty reads, non-repeatable reads, and phantom reads.

InnoDB implements four isolation levels in the SQL92 standard:

• Read uncommitted: select is not locked, which may result in dirty reads;
• Read Committed (RC): Ordinary select snapshot read, lock select/update/delete will use record lock, which may cause non-repeatable read;
• Repeatable Read (RR): Ordinary select snapshot read, lock select/update/delete. Depending on the query conditions, record lock, gap lock/immediate key lock will be selected to prevent phantom records from being read;
• Serialization: select is implicitly converted to select ... in share mode, which will be mutually exclusive with update and delete;

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.