Mysql在InnoDB引擎下索引失效行級鎖變表鎖案例

OldBoy~發表於2018-01-21
MySql索引

先做好準備，建立InnoDB引擎資料表，並新增了相應的索引

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終端，設定autocommit自動提交為0，也就是關閉自動提交功能，事務隔離級別處於可重複讀狀態；檢視一下表資料。

MySQL [test_db]> set autocommit = 0;
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   |
+---+------+

接下來在第一個終端執行update語句

MySQL [test_db]> update innodb_lock set b ='4001' where a = 4;

然後第二個終端執行update語句

MySQL [test_db]> update innodb_lock set b = '4004' where a = 4;

發現第二個終端處於阻塞狀態，因為這裡修改的是同一行資料，只有在第一個終端提交之後，鎖被釋放，第二個終端執行完了SQL，最後第二個終端也commit提交之後資料才會更改，b的值為'4004'。

我們繼續

在第一個終端執行update語句

MySQL [test_db]> update innodb_lock set b='4005' where a = 4;

然後第二個終端執行update語句

MySQL [test_db]> update innodb_lock set b = '9001' where a = 9;

發現兩條語句都執行成功了。因為修改的不是同一行資料。然後分別commit提交，再檢視下資料，發現兩行資料都發生了變化。

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

 

下面是重點，兩個終端還是分別操作不同的行。

在第一個終端執行update操作

MySQL [test_db]> update innodb_lock set a = 41 where b =4005;  //注意這裡的where條件b=4005。要記得當前表的b欄位是字串型別，並且加了索引，加了索引之後如果查詢條件沒加引號會導致失效

然後在第二個終端執行update操作

MySQL [test_db]> update innodb_lock set b='9002' where a = 9; //發現被阻塞，行鎖邊表鎖

一定要注意索引的合理利用！~~

 

相關文章