一个需要避免的 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: 0InnoDB状态显示这个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事务隔离。推荐阅读：InnoDB存储引擎的启动、关闭与恢复 http://www.linuxidc.com/Linux/2013-06/86415.htmMySQL InnoDB独立表空间的配置 http://www.linuxidc.com/Linux/2013-06/85760.htmMySQL Server 层和 InnoDB 引擎层体系结构图 http://www.linuxidc.com/Linux/2013-05/84406.htmInnoDB 死锁案例解析 http://www.linuxidc.com/Linux/2013-10/91713.htmMySQL Innodb独立表空间的配置 http://www.linuxidc.com/Linux/2013-06/85760.htm结合Git实现MySQL差异备份,可用于生产环境初见Oracle SqlLoader工具相关资讯 InnoDB

MySQL误删除InnoDB数据文件恢复（07月14日）
MySQL 5.6.23 InnoDB相关Bugfix （03月02日）
MySQL · 引擎特性 · InnoDB redo （03月02日）

解决数据库Operation not allowed （04月11日）
MySQL · 引擎特性 · InnoDB undo （03月02日）
MySQL · 性能优化· InnoDB buffer （03月02日）

本文评论查看全部评论（0）