- 论坛徽章:
- 2
|
在为一个客户排除死锁问题时我遇到了一个有趣的包括InnoDB间隙锁的情形。对于一个WHERE子句不匹配任何行的非插入的写操作中,我预期事务应该不会有锁,但我错了。让我们看一下这张表及示例UPDATE。
01 mysql> SHOW CREATE TABLE preferences \G
02 *************************** 1. row ***************************
03 Table: preferences
04 Create Table: CREATE TABLE `preferences` (
05 `numericId` int(10) unsigned NOT NULL,
06 `receiveNotifications` tinyint(1) DEFAULT NULL,
07 PRIMARY KEY (`numericId`)
08 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
09 1 row in set (0.00 sec)
10 mysql> BEGIN;
11 Query OK, 0 rows affected (0.00 sec)
12 mysql> SELECT COUNT(*) FROM preferences;
13 +----------+
14 | COUNT(*) |
15 +----------+
16 | 0 |
17 +----------+
18 1 row in set (0.01 sec)
19 mysql> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
20 Query OK, 0 rows affected (0.01 sec)
21 Rows matched: 0 Changed: 0 Warnings: 0
InnoDB状态显示这个UPDATE在主索引记录上持有了一个X锁:
1 ---TRANSACTION 4A18101, ACTIVE 12 sec
2 2 lock struct(s), heap size 376, 1 row lock(s)
3 MySQL thread id 3, OS thread handle 0x7ff2200cd700, query id 35 localhost msandbox
4 Trx read view will not see trx with id >= 4A18102, sees < 4A18102
5 TABLE LOCK table `test`.`preferences` trx id 4A18101 lock mode IX
6 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是另一个,查询的顺序如下:
01 mysql1> BEGIN;
02 Query OK, 0 rows affected (0.00 sec)
03 mysql1> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '1';
04 Query OK, 0 rows affected (0.00 sec)
05 Rows matched: 0 Changed: 0 Warnings: 0
06 mysql2> BEGIN;
07 Query OK, 0 rows affected (0.00 sec)
08 mysql2> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
09 Query OK, 0 rows affected (0.00 sec)
10 Rows matched: 0 Changed: 0 Warnings: 0
11 mysql1> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('1', '1'); -- This one goes into LOCK WAIT
12 mysql2> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('2', '1');
13 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
现在你看到导致死锁是多么的容易,因此一定要避免这种情况——如果来自于事务的INSERT部分导致非插入的写操作可能不匹配任何行的话,不要这样做,使用REPLACE INTO或使用READ-COMMITTED事务隔离。
本文转自:开源中国社区
英文原文:One more InnoDB gap lock to avoid
本文来自ChinaUnix新闻频道,如果查看原文请点:http://news.chinaunix.net/opensource/2013/1219/3046771.shtml
|
|