MySQL中InnoDB的间隙锁问题

最新推荐文章于 2024-06-27 19:38:12 发布

hrbeuwhw

最新推荐文章于 2024-06-27 19:38:12 发布

阅读量361

点赞数

点击打开链接 http://www.jb51.net/article/68878.htm

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

1

2

3

4

5

6

 
         ---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