瞭解下Mysql的間隙鎖及產生的原因

OldBoy~發表於2018-01-21
MySql

什麼是間隙鎖
當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但不存在的記錄，叫做“間隙(GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖(NEXT-KEY)鎖。
危害
因為Query執行過程中通過範圍查詢的話，他會鎖定整個範圍內所有的索引鍵值，即使這個鍵值並不存在。
間隙鎖有一個比較致命的弱點，就是當鎖定一個範圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定值範圍內的任何資料，在某些場景下這可能會針對性造成很大的危害。

準備

DROP TABLE IF EXISTS `innodb_lock`;
CREATE TABLE `innodb_lock` (
  `a` int(10) NOT NULL,
  `b` varchar(255) NOT NULL DEFAULT '',
  KEY `index_a` (`a`),
  KEY `index_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of innodb_lock
-- ----------------------------
INSERT INTO `innodb_lock` VALUES ('1', 'b2');
INSERT INTO `innodb_lock` VALUES ('3', '3');
INSERT INTO `innodb_lock` VALUES ('4', '4000');
INSERT INTO `innodb_lock` VALUES ('5', '5000');
INSERT INTO `innodb_lock` VALUES ('6', '6000');
INSERT INTO `innodb_lock` VALUES ('7', '7000');
INSERT INTO `innodb_lock` VALUES ('8', '8000');
INSERT INTO `innodb_lock` VALUES ('9', '9000');
INSERT INTO `innodb_lock` VALUES ('1', 'b1');
MySQL [test_db]> select * from innodb_lock;
+---+------+
| a | b    |
+---+------+
| 1 | b2   |
| 3 | 3    |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1   |
+---+------+

注意表中的資料，a是int型，假如表示id，包含1,3,4,5,6,6...當然缺少2，沒有連續下來。一般在公司裡做雲端計算大資料分析最好是連續的，有可能有的公司資料很珍貴，所謂的刪除其實是邏輯刪除，只是做了狀態更改。

操作（開啟兩個Mysql終端，分別設定autocommit自動提交為0，也就是關閉自動提交功能，事務隔離級別處於可重複讀狀態）

在第一個終端執行update操作，執行成功

MySQL [test_db]> update innodb_lock set b = '1234' where a > 1 and a < 6; //也就是對1,3,4,5這四條資料做修改。注意這裡沒有2

在第二個終端執行insert操作，發現被阻塞

MySQL [test_db]> insert into innodb_lock values (2,'2000');

反思：按說在InnoDB的行級鎖，兩個不同的終端操作不同的行資料，不會造成阻塞。可以看看這篇文章詳細瞭解下http://www.cnblogs.com/wt645631686/p/8323963.html。但是阻塞出現了。

接下來在第一個終端執行commit提交之後發現，第二個終端也執行成功了，然後第二個終端也commit提交。查詢表資料，觀察下資料的變化。

MySQL [test_db]> select * from innodb_lock;
+---+------+
| a | b    |
+---+------+
| 1 | b2   |
| 3 | 1234 |
| 4 | 1234 |
| 5 | 1234 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1   |
| 2 | 2000 |
+---+------+

通過最開始的介紹和案例操作，也就是Mysql會寧可錯殺也不會放過，沒有無所謂，有就不放過，只要給定的範圍，統統鎖了。

 

相關文章