欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL中InnoDB的间隙锁问题

程序员文章站 2024-02-26 16:36:28
 在为一个客户排除死锁问题时我遇到了一个有趣的包括innodb间隙锁的情形。对于一个where子句不匹配任何行的非插入的写操作中,我预期事务应该不会有锁,但我错了...

 在为一个客户排除死锁问题时我遇到了一个有趣的包括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事务隔离。