在为一个客户排除死锁问题时我遇到了一个有趣的包括innodb间隙锁的情形。对于一个where子句不匹配任何行的非插入的写操作中，我预期事务应该不会有锁，但我错了。让我们看一下这张表及示例update。
mysql> show create table preferences \g*************************** 1. row *************************** table: preferencescreate table: create table `preferences` ( `numericid` int(10) unsigned not null, `receivenotifications` tinyint(1) default null, primary key (`numericid`)) engine=innodb default charset=latin11 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 sec2 lock struct(s), heap size 376, 1 row lock(s)mysql thread id 3, os thread handle 0x7ff2200cd700, query id 35 localhost msandboxtrx read view will not see trx with id >= 4a18102, sees 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: 0mysql2> 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: 0mysql1> insert into preferences (numericid, receivenotifications) values ('1', '1'); -- this one goes into lock waitmysql2> 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事务隔离。