《MYSQL數(shù)據(jù)庫MySQL中InnoDB的間隙鎖問題》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫MySQL中InnoDB的間隙鎖問題，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
?在為一個(gè)客戶排除死鎖問題時(shí)我遇到了一個(gè)有趣的包含InnoDB間隙鎖的情形.對(duì)于一個(gè)WHERE子句不匹配任何行的非插入的寫操作中,我預(yù)期事務(wù)應(yīng)該不會(huì)有鎖,但我錯(cuò)了.讓我們看一下這張表及示例UPDATE.
?
MYSQL實(shí)例
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
---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申報(bào)中做了解釋,這很有意義,我知道修復(fù)起來很困難,但略帶厭惡地我又希望它能被差異化處理.為完成這篇文章,讓我證明下上面說到的死鎖情況,下面中mysql1是第一個(gè)會(huì)話,mysql2是另一個(gè),查詢的順序如下：
?
MYSQL實(shí)例
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
現(xiàn)在你看到導(dǎo)致死鎖是多么的容易,因此必定要避免這種情況――如果來自于事務(wù)的INSERT部分導(dǎo)致非插入的寫操作可能不匹配任何行的話,不要這樣做,使用REPLACE INTO或使用READ-COMMITTED事務(wù)隔離.
MYSQL實(shí)例
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/6665.html
