MySQL中InnoDB的间隙锁问题

 在为一个客户排除死锁问题时我遇到了一个有趣的包括innodb间隙锁的情形。对于一个where子句不匹配任何行的非插入的写操作中，我预期事务应该不会有锁，但我错了。让我们看一下这张表及示例update。
 

mysql> show create table preferences \g
*************************** 1. row ***************************
    table: preferences
create table: create table `preferences` (
 `numericid` int(10) unsigned not null,
 `receivenotifications` tinyint(1) default null,
 primary key (`numericid`)
) engine=innodb default charset=latin1
1 row in set (0.00 sec)
mysql> begin;
query ok, 0 rows affected (0.00 sec)
mysql> select count(*) from preferences;
+----------+
| count(*) |
+----------+
|    0 |
+----------+
1 row in set (0.01 sec)
mysql> update preferences set receivenotifications='1' where numericid = '2';
query ok, 0 rows affected (0.01 sec)
rows matched: 0 changed: 0 warnings: 0

innodb状态显示这个update在主索引记录上持有了一个x锁：
 

---transaction 4a18101, active 12 sec
2 lock struct(s), heap size 376, 1 row lock(s)
mysql thread id 3, os thread handle 0x7ff2200cd700, query id 35 localhost msandbox
trx read view will not see trx with id >= 4a18102, sees < 4a18102
table lock table `test`.`preferences` trx id 4a18101 lock mode ix
record locks space id 31766 page no 3 n bits 72 index `primary` of table `test`.`preferences` trx id 4a18101 lock_mode x

这是为什么呢，heikki在其中做了解释，这很有意义，我知道修复起来很困难，但略带厌恶地我又希望它能被差异化处理。为完成这篇文章，让我证明下上面说到的死锁情况，下面中mysql1是第一个会话，mysql2是另一个，查询的顺序如下：
 

mysql1> begin;
query ok, 0 rows affected (0.00 sec)
mysql1> update preferences set receivenotifications='1' where numericid = '1';
query ok, 0 rows affected (0.00 sec)
rows matched: 0 changed: 0 warnings: 0
mysql2> begin;
query ok, 0 rows affected (0.00 sec)
mysql2> update preferences set receivenotifications='1' where numericid = '2';
query ok, 0 rows affected (0.00 sec)
rows matched: 0 changed: 0 warnings: 0
mysql1> insert into preferences (numericid, receivenotifications) values ('1', '1'); -- this one goes into lock wait
mysql2> insert into preferences (numericid, receivenotifications) values ('2', '1');
error 1213 (40001): deadlock found when trying to get lock; try restarting transaction

现在你看到导致死锁是多么的容易，因此一定要避免这种情况——如果来自于事务的insert部分导致非插入的写操作可能不匹配任何行的话，不要这样做，使用replace into或使用read-committed事务隔离。