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

This article mainly introduces "deadlock case analysis caused by INSERT sentence". In daily operation, I believe that many people have doubts about deadlock case analysis caused by INSERT statement. 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 "deadlock case analysis caused by INSERT sentence". Next, please follow the editor to study!

Two of the same INSERT sentences unexpectedly caused a deadlock, which is the distortion of human nature, or the loss of morality, let us can not help but sigh: shit! This can also be deadlocked, and then helplessly changed the business code with sad tears in his eyes.

Well, before delving into why two identical INSERT statements can also produce deadlocks, let's introduce some basics.

Prepare the environment.

For the smooth development of the story, we built a new hero table that has been used countless times:

CREATE TABLE hero (number INT AUTO_INCREMENT, name VARCHAR, country varchar, PRIMARY KEY (number), UNIQUE KEY uk_name (name)) Engine=InnoDB CHARSET=utf8

Then insert a few records into the table:

INSERT INTO hero VALUES (1,'l Liu Bei', 'Shu'), (3,'z Zhuge Liang', 'Shu'), (8,'c Cao Cao', 'Wei'), (15,'x Xun Xun', 'Wei'), (20, 'Sun Quan','Wu')

The hero table now has two indexes (a unique secondary index and a clustered index), as shown in the following diagram:

How to lock an INSERT statement

Those of you who have read "how MySQL works: understand MySQL fundamentally" must know that INSERT statements do not generate a lock structure during normal execution, but rely on the trx_id hidden columns that come with clustered index records as implicit locks.

However, in some special scenarios, the INSERT statement will still generate a lock structure, let's list:

1. When the next record inserted into the record has been gap locked by another transaction

Each time you insert a new record, you need to see if the next record to be inserted has been locked by gap. If so, the INSERT statement should be blocked and an insert intention lock should be generated.

For example, for the hero table, transaction T1 runs in the REPEATABLE READ (later referred to as RR, and later referred to as READ COMMITTED for short RC) isolation level, executing the following statement:

# transaction T1mysql > BEGIN;Query OK, 0 rows affected (0.00 sec) mysql > SELECT * FROM hero WHERE number

< 8 FOR UPDATE;+--------+------------+---------+| number | name | country |+--------+------------+---------+| 1 | l刘备 | 蜀 || 3 | z诸葛亮 | 蜀 |+--------+------------+---------+2 rows in set (0.02 sec) 这条语句会对主键值为1、3、8的这3条记录都添加X型next-key锁，不信的话我们使用SHOW ENGINE INNODB STATUS语句看一下加锁情况，图中箭头指向的记录就是number值为8的记录： 小贴士： 至于SELECT、DELETE、UPDATE语句如何加锁，我们已经在之前的文章中分析过了，这里就不再赘述了。 此时事务T2想插入一条主键值为4的聚簇索引记录，那么T2在插入记录前，首先要定位一下主键值为4的聚簇索引记录在页面中的位置，发现主键值为4的下一条记录的主键值是8，而主键值是8的聚簇索引记录已经被添加了gap锁（next-key锁包含了正经记录锁和gap锁），那么事务1就需要进入阻塞状态，并生成一个类型为插入意向锁的锁结构。 我们在事务T2中执行一下INSERT语句验证一下： mysql>

BEGIN;Query OK, 0 rows affected (0.00 sec) mysql > INSERT INTO hero VALUES (4,'g Guan Yu', 'Shu')

At this point, T2 is in a blocking state, so let's take a look at the locking using SHOW ENGINE INNODB STATUS:

It can be seen that T2 adds an insert intention lock (that is, the lock_mode X locks gap before rec insert intention pointed to at the arrow) to the clustered index record with a primary key value of 8, and is in the waiting state.

Well, after verification, let's take a look at how it is implemented in the code:

The lock_rec_insert_check_and_lock function is used to see whether another transaction blocks this INSERT insertion. If so, this transaction generates an insert intention lock for the record with a gap lock added by another transaction, as follows:

Tips:

The lock_rec_other_has_conflicting function is used to detect whether there is a conflict between the lock to be acquired this time and the existing lock on the record. Interested students can take a look at it.

two。 When a duplicate key is encountered

If, when inserting a new record, it is found that the primary key or unique secondary index column of the existing record on the page has the same value as the primary key or unique secondary index column of the record to be inserted (although the value of the unique secondary index column of multiple records can be NULL at the same time, this situation is not considered here), the transaction that inserts the new record will acquire the lock of the record with the same key value that already exists on the page.

If the primary key value is duplicated, then:

When the isolation level is not greater than RC, the transaction that inserts the new record adds an S-type formal record lock to the existing clustered index record with duplicate primary key values.

When the isolation level is not less than RR, the transaction that inserts the new record adds an S-type next-key lock to the existing clustered index record with duplicate primary key values.

If the only secondary index column is duplicated, the transaction that inserts the new record will add an S-type next-key lock to the existing secondary index record with duplicate values of the secondary index column, regardless of the isolation level. Again, add a next-key lock! Add a next-key lock! Add a next-key lock! This is one of the few scenarios in the rc isolation level where gap locks are added to records.

Tips:

The uncle who originally designed InnoDB does not want to introduce gap locks at the RC isolation level, but for some reasons, if gap locks are not added, multiple records with the same values of unique secondary index columns will appear in the unique secondary index, which violates the UNIQUE constraint. So the uncle who designed InnoDB reluctantly introduced gap locks at the RC isolation level.

Let's also do an experiment, now assuming that the above T1 and T2 have been rolled back, now change the isolation level to RC and restart the transaction for testing.

Mysql > SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.01sec) # transaction T1mysql > BEGIN;Query OK, 0 rows affected (0.00 sec) mysql > INSERT INTO hero VALUES (30,'x Xun Xun', 'Wei'); ERROR 1062 (23000): Duplicate entry'x Xun 'for key' uk_name'

Then execute the SHOW ENGINE INNODB STATUS statement to see what locks T1 adds:

You can see that even though the isolation level of T1 is now RC,T1, an S-type next-key lock (lock mode S in the red box in the figure) is added to the secondary index record with the name column value of'x Xunqi'.

If our INSERT statement also contains ON DUPLICATE KEY... In such a clause, if a duplicate primary key value or unique secondary index column value is encountered, an X-type lock is added to the records with the same key value already existing in the B + tree, instead of an S-type lock (although the specific type of lock is the same as described earlier).

All right, it's time to check the code again. Let's take a look:

Row_ins_scan_sec_index_for_duplicate is a function that detects whether the values of the only secondary index column are duplicated. The specific locking code is as follows:

As shown in the figure above, when you encounter a duplicate unique secondary index column:

The red box No. 1 indicates that there is an ON DUPLICATE. Clause, specifically adding an X-type lock.

The No. 2 red box indicates the solution to the normal INSERT statement, specifically by adding an S-lock.

In either case, however, the value of the added lock_typed is LOCK_ORDINARY, which represents a next-key lock.

We will not enumerate the locking code of the INSERT statement when the primary key is repeated.

3. When checking foreign keys

When we insert a record into a child table, we discuss it in two cases:

When the foreign key values in the child table can be found in the parent table, then regardless of the isolation level of the current transaction, you only need to add an S-type formal record lock to the corresponding record in the parent table.

When the foreign key value in the child table cannot be found in the parent table: then the parent table record is not locked if the current isolation level is not greater than RC; when the isolation level is not less than RR, a gap lock is added to the next record in the parent table where the foreign key value is located.

The deadlock is coming out.

All right, the basics are over, it's time for deadlock.

Look at the following mediocre INSERT statement:

INSERT INTO hero (name, country) VALUES ('g Guan Yu', 'Shu'), ('d Deng Ai', 'Wei')

This statement is used to insert two records, whether at the RC or RR isolation level, which have a certain chance of triggering a deadlock if two transactions execute them concurrently. In order to steadily reproduce the deadlock, we split the above statement into two statements:

INSERT INTO hero (name, country) VALUES ('g Guan Yu', 'Shu'); INSERT INTO hero (name, country) VALUES ('d Deng Ai', 'Wei')

The effect before and after the split is the same, except that after the split, we can artificially control the timing of inserting the record. If the order of execution of T1 and T2 is as follows:

That is:

T1 first inserts a record with a name value of g Guan Yu, which can be inserted successfully. At this time, the corresponding unique secondary index record is protected by implicit locks. When we execute the SHOW ENGINE INNODB STATUS statement, we find that there is no row lock (row lock) (because SHOW ENGINE INNODB STATUS does not display implicit locks):

Then T2 also inserts a record with a name value of g Guan Yu. Since T1 has inserted a record with a name value of g Guan Yu, T2 will encounter duplicate unique secondary index column values when inserting a secondary index record. T2 wants to acquire an S-type next-key lock, but T1 is not committed. The implicit lock on a record with a name value of g Guan Yu inserted by T1 is equivalent to an X-type formal record lock (RC isolation level), so a lock conflict will be encountered when T2 acquires an S-type next-key lock. T2 enters the blocking state and converts the implicit lock of T1 into an explicit lock (that is, a lock structure that helps T1 generate a proper record lock). Then we execute the SHOW ENGINE INNODB STATUS statement:

It can be seen that the implicit lock held by T1 with a name value of g Guan Yu has been converted to an explicit lock (X-type formal record lock, lock_mode X locks rec but not gap); T2 is waiting to acquire an S-type next-key lock (lock mode S waiting).

Then T1 inserts a record with a name value of d Dunai. When you insert a record, the location of the record is first located on the page. When inserting a secondary index record with a name value of d Deng Ai, it is found that the distribution of records on the page is now as follows:

Obviously, the name value of the next secondary index record with a name value of'd Dengai 'should be' g Guan Yu'(sorted by Hanyu pinyin). Then when T1 inserts a secondary index record with a name value of d Deng Ai, you need to see if the secondary index record with a name value of'g Guan Yu 'has been locked by another transaction.

Some students want to say: at present, only T2 wants to add an S-type next-key lock (next-key lock includes gap lock) to the secondary index record with a name value of'g Guan Yu', but T2 has not acquired the lock and is currently waiting for the status. So T1 can not successfully insert the secondary index record with a name value of'g Guan Yu'?

Let's take a look at the implementation results:

# transaction T2mysql > INSERT INTO hero (name, country) VALUES ('g Guan Yu', 'Shu'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Apparently, a deadlock was triggered and T2 was rolled back by InnoDB.

Why is that? T2 obviously did not acquire the S-type next-key lock on the secondary index record with the name value of'g Guan Yu', why can't T1 insert the secondary index record with the name value d Dengai?

We have to go back to the code to see how to determine whether the lock conflicts when inserting a new record:

Take a look at the comment in the red box, which means that as long as another transaction generates an explicit lock structure of a gap lock, regardless of whether that transaction has acquired the lock (granted) or is waiting for it (waiting), the INSERT operation of the current transaction should be blocked.

Going back to our example, T2 has generated a lock structure of an S-type next-key lock on a secondary index record with a name value of'g Guan Yu'. Although T2 is blocking (the lock has not yet been acquired), T1 still cannot insert a secondary index record with a name value of d Dunai.

This explains the cause of the deadlock:

T1 is waiting for T2 to release the gap lock on the secondary index record with a name value of'g Guan Yu'.

T2 is waiting for T1 to release the X-type normal record lock on the secondary index record with a name value of'g Guan Yu'.

Two transactions wait for each other to release the lock, and a deadlock occurs.

How to solve this deadlock problem?

Two options:

Scenario 1: only one record is inserted into a transaction.

Option 2: first insert a record with a name value of'd Dengai', and then insert a record with a name value of'g Guan Yu'

At this point, the study on "deadlock instance analysis caused by INSERT statements" is over. I hope to be able to solve everyone's 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.