MySQL中InnoDB的间隙锁问题
在为一个客户排除死锁问题时我遇到了一个有趣的包括InnoDB间隙锁的情形。对于一个WHERE子句不匹配任何行的非插入的写操作中，我预期事务应该不会有锁，但我错了。让我们看一下这张表及示例UPDATE。
mysql>SHOWCREATETABLEpreferences\G ***************************1.row*************************** Table:preferences CreateTable:CREATETABLE`preferences`( `numericId`int(10)unsignedNOTNULL, `receiveNotifications`tinyint(1)DEFAULTNULL, PRIMARYKEY(`numericId`) )ENGINE=InnoDBDEFAULTCHARSET=latin1 1rowinset(0.00sec) mysql>BEGIN; QueryOK,0rowsaffected(0.00sec) mysql>SELECTCOUNT(*)FROMpreferences; +----------+ |COUNT(*)| +----------+ |0| +----------+ 1rowinset(0.01sec) mysql>UPDATEpreferencesSETreceiveNotifications='1'WHEREnumericId='2'; QueryOK,0rowsaffected(0.01sec) Rowsmatched:0Changed:0Warnings:0InnoDB状态显示这个UPDATE在主索引记录上持有了一个X锁：
---TRANSACTION4A18101,ACTIVE12sec 2lockstruct(s),heapsize376,1rowlock(s) MySQLthreadid3,OSthreadhandle0x7ff2200cd700,queryid35localhostmsandbox Trxreadviewwillnotseetrxwithid>=4A18102,sees<4A18102 TABLELOCKtable`test`.`preferences`trxid4A18101lockmodeIX RECORDLOCKSspaceid31766pageno3nbits72index`PRIMARY`oftable`test`.`preferences`trxid4A18101lock_modeX
这是为什么呢，Heikki在其bug报告中做了解释，这很有意义，我知道修复起来很困难，但略带厌恶地我又希望它能被差异化处理。为完成这篇文章，让我证明下上面说到的死锁情况，下面中mysql1是第一个会话，mysql2是另一个，查询的顺序如下：
mysql1>BEGIN;
QueryOK,0rowsaffected(0.00sec)
mysql1>UPDATEpreferencesSETreceiveNotifications='1'WHEREnumericId='1';
QueryOK,0rowsaffected(0.00sec)
Rowsmatched:0Changed:0Warnings:0
mysql2>BEGIN;
QueryOK,0rowsaffected(0.00sec)
mysql2>UPDATEpreferencesSETreceiveNotifications='1'WHEREnumericId='2';
QueryOK,0rowsaffected(0.00sec)
Rowsmatched:0Changed:0Warnings:0
mysql1>INSERTINTOpreferences(numericId,receiveNotifications)VALUES('1','1');--ThisonegoesintoLOCKWAIT
mysql2>INSERTINTOpreferences(numericId,receiveNotifications)VALUES('2','1');
ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
现在你看到导致死锁是多么的容易，因此一定要避免这种情况——如果来自于事务的INSERT部分导致非插入的写操作可能不匹配任何行的话，不要这样做，使用REPLACEINTO或使用READ-COMMITTED事务隔离。