Deadlock caused by gap lock in MySQL InnoDB

First understand what is gap lock

In InnoDB, there are roughly three types of record-level locks: Record, Gap, and next-keylocks. Simply put, recordlock is to lock a row of records; while gaplock locks records in a range; NEXT-KEYLOCK is the effect of the first two.

The following is a link to the MySQL official document.

Http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

 

According to some materials, MySQL's gap lock was initially designed to avoid phantom (phantom reading) problems. I will not explain the phantom reading here. For details, refer to the following link.

Http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

 

But after all, the gap lock causes an increase in the lock range. In some cases, it may cause some non-conforming phenomena. The following is a simple test example. First, we have a perceptual knowledge of gap lock.

Mysql> DESC ts_column_log_test

->;

+ ------------ + ------------- + ------ + ----- + --------------------- + ---------------- +

| FIELD | type | null | key | default | extra |

+ ------------ + ------------- + ------ + ----- + --------------------- + ---------------- +

| ID | int (11) | no | pri | null | auto_increment |

| Col_id | int (11) | no | Mul | null |

| Start_time | timestamp | no | 0000-00-00 00:00:00 |

| End_time | timestamp | no | 0000-00-|

| Data_time | timestamp | no | 0000-00-00 00:00:00 |

| Status | varchar (30) | no | null |

+ ------------ + ------------- + ------ + ----- + --------------------- + ---------------- +

6 rows in SET (0.01 Sec)

 

Mysql> select * From ts_column_log_test;

+ ---- + -------- + --------------------- + --------- +

| ID | col_id | start_time | end_time | data_time | status |

+ ---- + -------- + --------------------- + --------- +

| 1 | 2 | 11:51:11 | 11:51:11 | 00:00:00 | running |

| 2 | 20 | 11:51:16 | 11:51:16 | 00:00:00 | running |

| 3 | 120 | 11:51:20 | 11:51:20 | 00:00:00 | running |

+ ---- + -------- + --------------------- + --------- +

3 rows in SET (0.00 Sec)

 

Start two different sessions and execute some statements to observe the results:

Session1

Mysql> set autocommit = 0;

Mysql> Delete from ts_column_log_testwhere col_id = 10;

Query OK, 0 rows affected (0.00sec) -- at this time, records in the [) range have been locked by gap lock. If you try to insert these values in other transactions, you will wait.

 

Session2

Mysql> set autocommit = 0;

Mysql> insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (1, null, null, '20170101', 'running'); -- Success

...

Mysql> insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (2, null, null, '20140901', 'running'); -- Wait

...

Mysql> insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (19, null, null, '20140901', 'running'); -- Wait

...

 

The experiment above is very simple. You can test it yourself. Here we will explain the cause of this phenomenon: In the delete Statement of session1, specify the condition where col_id = 10. MySQL will then scan the index, however, the delete statement does not obtain a record lock, but a next-key lock. Taking the current value (10) as an example, the record col_id = 2 is scanned to the left, the record col_id = 20 is scanned to the right, and the lock interval is forward, closed, and closed, [2, 20 ).

The following is an excerpt from the official manual:

Delete from... where... sets an exclusivenext-key lock on every record the search encounters.

The following link describes the locks that may be held by different statements in InnoDB.

Http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

 

Now that you understand what the gap lock is, let's take a look at the possible problems.

Sometimes, when multiple processes or threads operate on the same table in parallel and use transactions, there may be problems. For example:

Session1:

Delete from ts_column_log_test wherecol_id = 10;

Insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (10, null, null, '20140901', 'running ');

 

Session2:

Delete from ts_column_log_test wherecol_id = 11;

Insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (11, null, null, '20140901', 'running ');

 

Assume that the preceding operations are required by the two processes of your program and may run normally without concurrency, because each transaction is executed serially in MySQL, there are no other transactions in the middle at the same time; when the concurrency is high, the order of statements actually run in MySQL may change to the following:

Tx_num time statement

111 2011-12-12 10:00:00 Delete from ts_column_log_test wherecol_id = 10;

222 2011-12-12:00:00 Delete from ts_column_log_test where col_id = 11;

111 2011-12-12 10:00:00 insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (10, null, null, '20140901', 'running ');

222 2011-12-12:00:00 insert into ts_column_log_test (col_id, start_time, end_time, data_time, status) values (11, null, null, '20140901', 'running ');

 

At this time, you may get the error message error 1213 (40001): deadlock found when trying toget lock; try restarting transaction.

The reason is that the first two statements have obtained the S lock recorded in the range [), and then the two transactions request the X lock for the position col_id = 10 in the range respectively, in this case, a deadlock occurs. No one requests the X lock because each other holds the S lock.

 

There are two solutions

1. Change the database operation logic in the program

2. Disable the gap lock mechanism.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to read committed orenable the innodb_locks_unsafe_for_binlog system variable.