InnoDB RR isolation level insert select two deadlock case analysis, innodb Case Analysis

InnoDB RR isolation level insert select two deadlock case analysis, innodb Case Analysis

Author:Gao Peng (Eight monsters in Chongqing)

School draft:Master Ye (some content is fine-tuned)

Original article: http://blog.itpub.net/7728585/viewspace-2146183/

Some netizens encountered A deadlock in insert A select B where B. COL = ** at the RR isolation level. After analyzing the deadlock log, I have simulated and reproduced two scenarios that may cause deadlocks. This article will describe them in detail.

Conventions

• This article uses the modified version Percona 5.7.14 to print the row lock information structure chain of the firm (excluding the implicit lock );

• All tests in this article are completed at the RR isolation level, and RC does not have such a problem;

• I have limited understanding of the source code. please correct me if any errors exist;

• This article uses a self-made toolInnblockAndBcviewThe former is used to scan the block structure, and the latter is used to conveniently view the binary file information. Two tools:

• Innblock, http://pan.baidu.com/s/1qYnyVWo

• Bcview, http://pan.baidu.com/s/1num76RJ

I would like to thank Mr. Ye Jinrong for reviewing this article. I was also a student of zhishutang.

I. Basic Concepts

Before starting the text, I plan to introduce some basic concepts, especially the lock model and compatibility matrix, which will be of great help to read this article.

1. innodb lock Model

• [LOCK_ORDINARY [next_key_lock]:] Source Code definition:

  #define LOCK_ORDINARY    0/*!< this flag denotes an ordinarynext-key lock in contrast to LOCK_GAPor LOCK_REC_NOT_GAP */

  The default value is LOCK_ORDINARY, that is, next-keylock, which locks the row and the gap before it.

  
  

• [LOCK_GAP:] Source Code definition:

  #define LOCK_GAP    512/*!< when this bit is set, it means that thelock holds only on the gap before the record;for instance, an x-lock on the gap does notgive permission to modify the record on whichthe bit is set; locks of this type are createdwhen records are removed from the index chain

  The gap lock. It locks the gap before the row and does not lock the row.

  
  

• [LOCK_REC_NOT_GAP:] Source Code definition:

  #define LOCK_REC_NOT_GAP 1024/*!< this bit means that the lock is only onthe index record and does NOT block insertsto the gap before the index record; this isused in the case when we retrieve a recordwith a unique key, and is also used inlocking plain SELECTs (not part of UPDATEor DELETE) when the user has set the READCOMMITTED isolation level */

  Row lock, lock the row without locking any gaps.

  
  

• [LOCK_INSERT_INTENTION:] Source Code definition:

  #define LOCK_INSERT_INTENTION 2048/*!< this bit is set when we place a waitinggap type record lock request in order to letan insert of an index record to wait untilthere are no conflicting locks by othertransactions on the gap; note that this flagremains set when the waiting lock is granted,or if the lock is inherited record */

  Insert an intention lock if the inserted record is locked.

2. innodb lock Compatibility Matrix

/* LOCK COMPATIBILITY MATRIX *    IS IX S  X  AI * IS +  +  +  -  + * IX +  +  -  -  + * S  +  -  +  -  - * X  -  -  -  -  - * AI +  +  -  -  -

3. infimum and supremum

A page always contains these two pseudo records. All rows on the page that are not deleted (or deleted but not purged) are logically linked to the two pseudo records, represented as a data structure of a logical linked list, the supremum pseudo record lock is always next-key lock.

4. heap no

Heap no is stored in fixed_extrasize. Heap no is the serial number filled by physical storage. The free space of the page is mounted to the page free linked list (header insertion method). Idle heap can be reused, but heap no remains unchanged when it is reused. If it is insert, heap no is increasing. Heap does not sort by ROWID (Primary Key), but by physical filling.

5. n bits

The size of the lock bitmap related to this page. each row of record has a 1-bit Bitmap information corresponding to it, which indicates whether to lock and always reserve 64bit. For example, my table has nine data records, plus infimum and supremum virtual records, that is, 64 + 9 + 2 bits = 75 bits, however, it must be divisible by 8 bytes (in order to rounded up to one byte), and the final result is 80 bits (8 bytes ).Note:: No matter whether the lock is applied or not, each row will correspond to 1 bit.

6. lock struct

This is the memory structure of the LOCK. Lock_t is used in the source code. There are two types:

lock_table_t    tab_lock;/*!< table lock */lock_rec_t    rec_lock;/*!< record lock */

Generally, IX is added to the table during innodb table locking, which occupies a struct. Then, the secondary index and primary key are respectively locked. Each BLOCK occupies such a struct.

7. row lock (s)

This information describes the number of rows locked by the current transaction. It is the sum of all lock records excluding table lock in all lock struct structures and contains infimum and supremum pseudo records.

8. Gradually lock

Careful friends should find that in the show engine innodb status output, when locking a large number of rows, the row lock in the transaction information will continue to increase. This is because the row lock will eventually callLock_rec_lockLocks row by row, which increases the possibility of triggering deadlocks when locking large amounts of data.

Ii. Locking mode for the SELECT table in INSERT SELECT

Insert A select B where B at the RR isolation level. COL = **, the data that meets the conditions in Table B is locked, but the records in Table B in RC mode are not locked at the innodb layer.

The specific performance is as follows:

1. If B. COL is the NON-UNIQUE SECONDARY KEY and is not overwriting the index (no using index in the execution plan)

• • In table B, the secondary index adds LOCK_S to the selected record | LOCK_ORDINARY [next-key lock] And LOCK_S to the next record | LOCK_GAP

  • Add LOCK_S to primary key in Table B | LOCK_REC_NOT_GAP

2. If B. COL is a UNIQUE SECONDARY KEY and does not overwrite the index

• • Add LOCK_S | LOCK_REC_NOT_GAP to the selected record in the secondary index of Table B.

  • Add LOCK_S to PRIMARY in Table B | LOCK_REC_NOT_GAP

3. If B. COL does not have a secondary index

• • Add LOCK_S to all records in Table B | LOCK_ORDINARY [next_key_lock]

Iii. Lock test for SELECT table in INSERT SELECT

We test the lock information in several cases:

3.1, B. COL is the NON-UNIQUE SECONDARY KEY, and the index is not covered

Test Environment preparation:

mysql> create table t1(id int primary key,n1 varchar(20),n2 varchar(20),key(n1));mysql> create table t2 like t1;mysql> insert into t1 values(1,'gao1','gao'),(2,'gao1','gao'),(3,'gao1','gao'),(4,'gao2','gao'),(5,'gao2','gao'),(6,'gao2','gao'),(7,'gao3','gao'),(8,'gao4','gao');

View the execution plan:

mysql> desc select * from t1 force index(n1) where n1='gao2’\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: n1          key: n1      key_len: 23          ref: const         rows: 3     filtered: 100.00        Extra: NULL

Run the test SQL:

mysql> begin;insert into t2 select * from t1 force index(n1) where n1='gao2';

Observe the locking result of Table B.

• B. Add LOCK_S to col | LOCK_ORDINARY [next_key_lock]

• B. Add LOCK_S to PRIMARY | LOCK_REC_NOT_GAP

• Add LOCK_S | LOCK_GAP to the next record of B. Secondary Index

The red part is the record to be locked.

3.2. B. COL is the UNIQUE SECONDARY KEY and is not a covered index.

Test Environment preparation:

mysql> create table t1(id int primary key,n1 varchar(20),n2 varchar(20),unique key(n1));mysql> create table t2 like t1;mysql> insert into t1 values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

View the execution plan:

mysql> desc select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4’)\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: rangepossible_keys: n1          key: n1      key_len: 23          ref: NULL         rows: 3     filtered: 100.00        Extra: Using index condition

Run the test SQL:

mysql> begin;insert into t2 select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4');

Observe the locking result of Table B.

• B. Add LOCK_S to col | LOCK_REC_NOT_GAP

• B. Add LOCK_S to PRIMARY | LOCK_REC_NOT_GAP

The red part is the record to be locked.

3.3. B. COL does not have a secondary index

Test Environment preparation:

mysql> create table t1(id int primary key,n1 varchar(20),n2 varchar(20));mysql> create table t2 like t1;mysql> insert into t1 values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

View the execution plan:

mysql> desc select * from t1  where n1 in ('gao2','gao3','gao4’)\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 8     filtered: 37.50        Extra: Using where

Run the test SQL:

mysql> begin;insert into t2 select * from t1  where n1 in ('gao2','gao3','gao4');

Observe the locking result of Table B.

The red part is the record to be locked.

Now, we confirm that at the RR isolation level, insert select will add the LOCK_S lock to the data that meets the conditions in the SELECT table.

Iv. deadlock caused by the SELECT Table in the insert select statement

I have summarized the conditions for deadlocks:

1. At least two independent threads (sessions );

2. The unit operation contains multiple relatively independent locking steps, with a certain time difference;

3. Lock objects between multiple threads (sessions) must wait for each other and wait for a ring.

Because the LOCK_S lock is applied to SELECT data that meets the conditions, the deadlock probability of insert select in RR mode is undoubtedly higher. I simulated the deadlock result through the test,Strictly speaking, this is the result of two deadlocks in the case of high concurrency of the same statement.

Test Environment preparation:

mysql> create table b(id int primary key,name1 varchar(20),name2 varchar(20),key(name1));mysql> DELIMITER //  mysql> CREATE PROCEDURE test_i()begin   declare num int;  set num = 1; while num <= 3000 do  insert into b values(num,concat('gao',num),'gaopeng');  set num=num+1;end while;end// mysql> call test_i()//create table a like b//

Simulate the following two concurrent transactions:

TX1	TX2
Begin;	-
Update B set name2 = 'test' where id = 2999;	-
-	Insert into a select * from B where id in (996,997,998,999,299, 2999 );
Update B set name2 = 'test' where id = 999;	-

But in high concurrency, the same concurrent statement shows different deadlocks.

See the detailed process analysis below.

4.1 scenario 1

• TX1: Execute update to add LOCK_X to the record with table B's primary key id = 2999

• TX2: Execute insert... select statement B. The record (996,997,998,999,299, 2999, 2996, 2997,2998, 2999) will apply for adding LOCK_S, but id = has already been added with LOCK_X, obviously cannot be obtained and can only wait.

• TX1: to execute the update operation, you need to obtain the LOCK_X with the table B primary key id = 999. Obviously, this record has been locked by TX2 LOCK_S and can only wait, triggering the Deadlock Detection.

For example, the red record indicates that the lock cannot be obtained:

4.2. Scenario 2

This situation is more extreme and can only occur in high concurrency.

• TX1: Execute update to add LOCK_X to the record with table B's primary key id = 2999

• TX2: Execute insert... the record (996,997,998,999,299, 2996, 2997,2998, 2999) in the select statement B will apply for the addition of LOCK_S, because locking is a process of gradual locking. Assuming that TX2 will not wait until the lock reaches 2997.

• TX1: To execute update, You need to obtain the LOCK_X of Table B's primary key id = 999. Obviously, this record has been locked by TX2 LOCK_S and can only wait.

• TX2: Continue to lock LOCK_S 2997, 2998, 2999 found that 2999 has been locked by TX1 LOCK_X, can only wait, triggering Deadlock Detection

For example, the red record indicates that the lock cannot be obtained:

5. source code modification and parameter Addition

Scenario 2 appears only at a specific high concurrencyIn high concurrency scenarios, it is difficult to think that the insert select (Row-by-row locking) process cannot be controlled to stop it under specific conditions so that we can observe it.

Therefore, in order to be able to simulate this situation, I have added four parameters to innodb to set the lock breakpoint (temporary sleep during the lock process ):

mysql> show variables like '%gaopeng%';+---------------------------+-------+| Variable_name             | Value |+---------------------------+-------+| innodb_gaopeng_sl_heap_no | 0     || innodb_gaopeng_sl_ind_id  | 0     || innodb_gaopeng_sl_page_no | 0     || innodb_gaopeng_sl_time    | 0     |+---------------------------+-------+

These parameters are both 0 by default, that is, they are not enabled. Their functions are as follows:

• Innodb_gaopeng_sl_heap_no: the heap no where the record is located

• Innodb_gaopeng_sl_ind_id: The index_id of the record

• Innodb_gaopeng_sl_page_no: the record's page_no

• Innodb_gaopeng_sl_time: the number of seconds after a sleep has index_id, page_no, and heap no, which can uniquely limit a certain record, and the sleep wait time can also be specified manually.

Add the following code at the beginning of the source code lock_rec_lock:

In this way, once determined to be a qualified record, this record will be sleep for a specified duration before being locked. If we set to sleep for 30 seconds before LOCK_S: id = 2997, the locking process shown in scenario 2 must occur:

Vi. Actual test 6.1. Scenario 1

TX1	TX2
Begin;
Update B set name2 = 'test' where id = 2999; apply the LOCK_X lock to id: 2999
	Insert into a select * from B where id in (996,997,998,999,299, 2996, 2997,2998, 2999); Apply LOCK_S lock to id: 996,997,998,999,299, 2996, 2997,2998, but apply LOCK_S lock to id: 2999 when LOCK_S is applied, the LOCK_X lock has been applied and you need to wait.
Update B set name2 = 'test' where id = 999; apply the LOCK_X lock to id: 999, but find that the LOCK_S lock has been applied. Wait and trigger the Deadlock Detection.
TX1 triggers a deadlock, and TX1 rolls back under weight Determination

Deadlock error statement:

mysql> update b set name2='test' where id=999;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock log:

The deadlock information is extracted as follows:

6.1. Scenario 2

We set the following statement to add a breakpoint:

mysql> insert into a  select * from b
where id in (996,997,998,999,2995,2996,2997,2998,2999)

When a record in Table B is locked for 30 seconds before id = 2997 is locked, I need to find three pieces of information for primary key 2997 in Table B: index_id, page_no, and heap_no, my innblock tool is used here

Because data is inserted sequentially during initialization, id = 2997 must be included in page 18. Scan page 18:

We can infer from the insertion order that heap_no 84 is the record with id = 2997. We use another tool bcview for verification:

 ./bcview b.ibd 16 3326 4current block:00000018--Offset:03326--cnt bytes:04--data is:80000bb5

Of course, the hexadecimal 0Xbb5 value is 2997.

Therefore, set the parameter:

innodb_gaopeng_sl_heap_no=84;innodb_gaopeng_sl_ind_id=121;innodb_gaopeng_sl_page_no=18;innodb_gaopeng_sl_time=30;mysql> show variables like '%gaopeng%';+---------------------------+-------+| Variable_name             | Value |+---------------------------+-------+| innodb_gaopeng_sl_heap_no | 84    || innodb_gaopeng_sl_ind_id  | 121   || innodb_gaopeng_sl_page_no | 18    || innodb_gaopeng_sl_time    | 30    |+---------------------------+-------+

The execution sequence of Scenario 2 is as follows:

TX1	TX2
Begin;
Update B set name2 = 'test' where id = 2999; apply the LOCK_X lock to id: 2999
	Insert into a select * from B where id in (996,997,998,999,299, 2996, 2997,2998, 2999); apply the LOCK_S lock to id: in the lock to 996,997,998,999,299, 2996, apply the LOCK_S lock to id: 2997 sleep for 30 seconds before locking, freeing up time for the following update Statement)
Update B set name2 = 'test' where id = 999; apply the LOCK_X lock to id: 999, but find that the LOCK_S lock has been applied. Wait.
	After waking up, the LOCK_S lock is applied to 2997, 2998, and 2999, but the LOCK_X lock is applied to id: 2999. Wait to trigger the Deadlock Detection.
TX1 weight rollback

Deadlock error statement:

mysql> update b set name2='test' where id=999;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock log:

The deadlock information is extracted as follows:

The deadlock log clearly shows that the deadlock information reported by the same statement is different,It is confirmed that the same statement in high concurrency may occur in both deadlock scenarios..

VII. Summary

To analyze the deadlock, you must obtain the following information from the deadlock log:

• 1. Lock occurs in the primary key or secondary index;

• 2. What is the locking mode;

• 3. Whether to lock a single row or multiple rows;

• 4. the final statement of the deadlock transaction is triggered;

• 5. What is the transaction sequence in the deadlock information;

When recreating the deadlock process, you must completely match the online deadlock information. This deadlock scenario is considered successful. From this example, we find that the deadlock information generated by the same statement is different. Of course, we need to consider it according to different scenarios. The scenario 2 in this article is complicated. Generally, it only appears at the beginning of high concurrency, and testing is also relatively troublesome. This article tests by modifying the source code, otherwise it will be difficult to reproduce.

Finally, find the cause of the deadlock and take necessary measures. For example, the example in this article needs to consider several solutions:

• Whether to promptly submit changes to the SELECT table in the insert select statement;

• Whether the insert select statement can be replaced by other methods, because this statement also has a certain risk in the auto-increment lock;

• Whether to use the RC isolation level. The SELECT Table record is not locked at the RC isolation level.

At last, I would like to emphasize that it is best to analyze LOCK_S locks in depth, because these locks are rare.

If you have any questions about this article, scan the QR code to add the original author.