mysql innodb 间隙锁_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在其bug报告中做了解释，这很有意义，我知道修复起来很困难，但略带厌恶地我又希望它能被差异化处理。为完成这篇文章，让我证明下上面说到的死锁情况，下面中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事务隔离。

您可能感兴趣的文章:MySQL的InnoDB引擎入门学习教程MySQL存储引擎中的MyISAM和InnoDB区别详解Mysql存储引擎InnoDB和Myisam的六大区别MySQL数据库InnoDB引擎主从复制同步经验总结mysql事件的开启和调用如何开启mysql中的严格模式MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法