Shulou(Shulou.com)05/31 Report--

This article mainly introduces the innodb how to achieve transaction isolation level, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Preface

In the previous article, we explained the lock mechanism of mysql in detail. The lock mechanism is used to ensure the accuracy of data in the case of concurrency, and transaction support is usually needed to ensure the accuracy of data. Mysql storage engine innodb skillfully implements four isolation levels in the isolation characteristics of transactions through the lock mechanism.

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

We all know that several properties of transactions, consistency and isolation in the database are the basic ideas for realizing transactions. In the case of a large number of concurrent visits in the system, understanding and skillfully applying the transaction isolation level of the database itself plays a key role in writing robust and concurrent code.

1. How transactions interfere with each other

How does one transaction interfere with other affairs? 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, executed first, is 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 (4, zhaoliu) this record, transaction A has an impact on transaction B. this effect is called "read dirty", that is, it reads the record of uncommitted transaction operations.

Demo2:

Transaction A, execute first:

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 time it is the impact of committed transaction B on transaction A, which is called "unrepeatable read", which means that the same query within a transaction gets different results.

Demo3:

Transaction A, execute first:

Select * from t where id > 3

The result set is:

NULL

Transaction B, executed later, and committed:

Insert into t values (4, zhaoliu); commit

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

Insert into t values (4, xxoo)

The result set is:

Error: duplicate key!

You might think. Are you TM kidding me? Check that id > 3 is an empty set, and then tell me about competitive conflicts when insert id=4. → _ →

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

As mentioned above, concurrent transactions may cause other transactions to read dirty, unrepeatable, and phantom. What efforts has innodb made to avoid this?

2. What are the isolation levels of transactions achieved by InnoDB?

InnoDB implements isolation levels for four different transactions:

Read unsubmitted (Read Uncommitted)

Read submit (Read Committed, RC)

Readable (Repeated Read, RR)

Serialization (Serializable)

The isolation level of different transactions is actually a tradeoff between consistency and concurrency.

3. How does innodb implement the isolation levels of the four transactions?

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

a. Read unsubmitted (Read Uncommitted)

At this transaction isolation level, select statements are not locked and are not snapshot reads.

SELECT statements are performed in a nonlocking fashion.

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

b. Read submit (Read Committed, RC)

Ordinary select is a snapshot read.

Locked statements such as select, update, delete, etc., only use record locks at all times, except for locking intervals during foreign key constraint checks (foreign-key constraint checking) and repeated key checks (duplicate-key checking).

Clearance lock (gap lock) and temporary construction 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 reading of the phantom record. This level is the most commonly used. And if it is an unlocked select, it may result in unrepeatable readings.

At this level, snapshot reading is used to prevent dirty reading. Because snapshot reads at this level can always read the latest row data snapshots and, of course, must be written by committed transactions, unrepeatable reads can occur.

c. Readable (Repeated Read, RR)

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

Ordinary select uses snapshot read (snapshot read), which is an unlocked consistent read (Consistent Nonlocking Read), and the underlying MVCC is used to implement it.

Locked select (select... In share mode / select... For update), update, delete, etc., whose locks depend on whether they use a unique query condition on the unique index (unique index) (unique search condition, where record locks are used), or scope query conditions (range-type search condition, where gap locks or key locks are used)

Using a unique query condition on a unique index uses a record lock (record lock) instead of locking the gap between records, that is, no gap lock (gap lock) and key lock (next-key lock)

Range query conditions or non-unique indexes will use gap locks and key locks to lock the range between index records and avoid inserting records between ranges to avoid phantom row records and unrepeatable readings

At that level

Avoid illusory reading and unrepeatable reading through snapshot reading and locking interval

The time of the first read record of a transaction is T, and the records written by committed transactions after T time will not be read in the future to ensure that consecutive identical read reads the same result set, which can prevent non-repeatable readings.

Under RR, the problem of phantom reading is solved by gap lock and key lock.

d. Serialization (Serializable)

Under the isolation level of this transaction, all select statements are implicitly converted to select. In share mode, that is, the shared read lock (S lock) is the default.

Therefore, if transaction A first executes the following sql, it will try to acquire the IS lock of the queried row (compatible with other IS and IX locks), then other transactions can also acquire the IS lock or even S lock of these rows, but if transaction An acquires the X lock if some of the rows are update or delete, other transactions will block even the execution of ordinary select statements because they try to acquire IS locks But the IS lock and the X lock are mutually exclusive, so dirty, unrepeatable, and illusory reads are avoided, and all transactions can only be serial.

Select...

This is the isolation level with the best consistency but the worst concurrency. In high concurrency scenarios, the above two isolation levels, an and d, are rarely used.

4. Summary

The interference between concurrent transactions may lead to some problems such as dirty reading, unrepeatable reading, phantom reading and so on.

InnoDB implements four isolation levels in the SQL92 standard:

Read not submitted: select is not locked. Dirty reading may occur.

Read submission (RC): normal select snapshot read, lock select / update / delete will use record lock, which may be unrepeatable

RR: normal select snapshot read, lock select / update / delete according to query conditions, record lock or gap lock / 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 by update and delete

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

Thank you for reading this article carefully. I hope the article "how to achieve transaction isolation level in innodb" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.