mysql 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.