mysql gap lock作用_MYSQL INNODB中GAP LOCK引起死锁的问题

先体会一下甚么是GAP

LOCK

正在INNODB中，record-level lock大年夜致有三种：Record, Gap, and

Next-KeyLocks。简单的讲，RECORDLOCK就是锁住某一止纪录；而GAPLOCK会锁住某一段领域中的纪录；NEXT-KEYLOCK则是前两者减起去的效果。

上面是MYSQL官方文档中相关内容的链接

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

有资料里讲MYSQL的GAP LOCK最初是为了停止Phantom

(幻象读)的题目，关于幻象读那里就没有多做注释了，能够参考以下链接

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

但是事实效果GAPLOCK导致了锁定领域的删大年夜，正在某些状态下可能会构成一些没有契合预期的征象。上面是一个简单的测试例子，先对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-0000:00: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| 2011-12-13 11:51:11 | 2011-12-13 11:51:11 | 2011-12-09

00:00:00 | running |

| 2 | 20 |2011-12-13 11:51:16 | 2011-12-13 11:51:16 | 2011-12-09

00:00:00 | running |

| 3 | 120 |2011-12-13 11:51:20 | 2011-12-13 11:51:20 |

2011-12-09 00:00:00 | running |

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

3 rows in set (0.00 sec)

开启两个没有同的会话，辞行施止一些语句察看一下成绩：

session1

mysql> set autocommit=0;

mysql> delete from ts_column_log_testwhere col_id=10;

Query OK, 0 rows affected (0.00sec) --此时[2,20)谁人区间内的纪录皆曾经被GAP

LOCK锁住了，若是正在其他事务中检建测验插进那些值，则会等待

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,"20111209",

"running"); --成功

...

mysql> INSERT INTO ts_column_log_test(col_id, start_time,

end_time, data_time, status) VALUES (2, NULL, NULL,"20111209",

"running"); --等待

...

mysql> INSERT INTO ts_column_log_test(col_id, start_time,

end_time, data_time, status) VALUES (19, NULL, NULL,"20111209",

"running"); --等待

...

上面的尝试很简单，大年夜家能够自己测一下。那里注释一下会收生那种征象的本因：session1中的delete语句中指定条件where

col_id=10，其时MYSQL会去扫描索引，但是谁人时分delete语句获得的没有是一个RECORD

LOCK，而是一个NEXT-KEY

LOCK。以当前值(10)为例，会背左扫描至col_id=2那条纪录，背右扫描至col_id=20那条纪录，锁定区间为前闭后开，即[2,20)。

上面是摘自官方脚册里的一句话：

DELETE FROM ... WHERE ... sets an exclusivenext-key lock on

every record the search encounters.

上面的链接里面有INNODB中种种没有同的语句可能持有哪些锁的注释

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

领略了GAPLOCK是若何回事，上面看下可能收生的题目吧

有时分我们会多个历程或线程并止的对同一张表进止操作，并且应用了事务，那么便可能会有题目，举个例子：

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,

"20111209","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,

"20111209","running");

假设上面是您次序的两个历程需求做的操作，正在没有并收的状态下，可能运止正常，由于每一个事务正在MYSQL中终极皆是串止施止，中心并没有其他事务同时进止；可并收高了当前，可能正在MYSQL中现实运止的语句次第就会酿成上面谁人样子：

tx_num time statement

111 2011-12-12 10:00:00 delete from ts_column_log_test

wherecol_id=10;

222 2011-12-1210: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, "20111209","running");

222 2011-12-1210:00:00 INSERT INTO ts_column_log_test (col_id,

start_time, end_time,data_time, status) VALUES (11, NULL, NULL,

"20111209", "running");

谁人时分，您可能就会获得弊端提示ERROR 1213 (40001): Deadlock found when trying

toget lock; try restarting transaction。

本因是前两条语句皆曾经获得了[2,20)谁人区间内纪录的S锁，然后两个事务又辞行对该区间段内的col_id=10谁人位置请求X锁，其时就收作死锁，谁皆请求没有到X锁，由于彼此皆持有S锁。

解决打算有两种

1、改变次序中数据库操作的逻辑

2、消除GAP LOCK机制

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.