Mysql鎖機制--索引失效導致行鎖變表鎖

標籤：font   2.2.3   innodb   info   注意   get   mit   ==   blog   

Mysql 系列文章首頁 

===============

Tips：在閱讀本文前，最好先閱讀 這篇（Mysql鎖機制--行鎖）文章~

在上篇文章中，我們看到InnoDB預設的行鎖可以使得操作不同行時不會產生相互影響、不會阻塞，從而很好的解決了多事務和並發的問題。但是，那得基於一個前提，即 Where 條件中使用上了索引；反之，如果沒有使用上索引，則是全表掃描、全部阻塞。本文就以實際例子來示範這種情景。

1 準備資料

1.1 建表

DROP TABLE IF EXISTS employee;CREATE TABLE IF NOT EXISTS employee (    id INT PRIMARY KEY auto_increment,    name VARCHAR(40),    money INT)ENGINE INNODB;

注意：ENGINE 是 INNODB（因為 InnoDB 才支援行鎖）

1.2 插入資料

INSERT INTO employee(name, money) VALUES(‘1001‘, 10000);INSERT INTO employee(name, money) VALUES(‘1002‘, 10000);

提示：‘1001‘ & ‘1002‘ 既是字串，也是數字（後面會用到）

2 沒有建索引的情形

到現在為止，沒有顯示地為 Employee 表建立索引。

2.1 準備

還是老規矩，兩個會話（終端），左邊是白色背景的，右邊是黑色背景的，並且均設定 autocommit = 0

2.2 測試

2.2.1 在左側會話中執行更新

Sql 語句：

UPDATE employee SET money = money + 10000 WHERE name = ‘1001‘;

結果：

2.2.2 在右側會話中執行更新

Sql 語句：

UPDATE employee SET money = money + 5000 WHERE name = ‘1002‘;

結果：如所示，更新操作被阻塞了！

2.2.3 在左側執行 COMMIT 命令

提示：執行的時候注意查看右邊Sql語句執行情況的變化

2.2.4 右側的Sql語句正常執行了，耗時 18.19 秒

2.2.5 右側也執行一下提交

2.2.6 左側查看一下資料

2.2.7 右側查看一下資料

兩邊結果相同，且正確。

但問題是，左側操作的是 name = ‘1001‘ 記錄而右側操作的是 name = ‘1002‘ 記錄，右側還是被阻塞了，說明，左側的更新操作鎖住了全表！究其原因，是因為 name 欄位上沒有索引！

2.3 結論

當 Where 查詢條件中的欄位沒有索引時，更新操作會鎖住全表！

3 索引失效的情形

現在來示範索引失效的情形；要想索引失效，前提得要有索引啊；於是，先建立索引。

3.1 建立索引

CREATE INDEX idx_name ON employee(name);

3.2 有索引情況下的行鎖示範

提示：還是老規矩，兩個會話（終端），左邊是白色背景的，右邊是黑色背景的，並且均設定 autocommit = 0

3.2.1 在左側會話中執行更新

Sql 語句：

UPDATE employee SET money = money + 10000 WHERE name = ‘1001‘;

結果：

3.2.2 在右側會話中執行更新

Sql 語句：

UPDATE employee SET money = money + 5000 WHERE name = ‘1002‘;

結果：

立即執行，沒有被阻塞！

3.2.3 左右兩側分別執行提交

3.2.4 左側查看結果

3.2.5 右側查看結果

兩邊的結果相同，且正確。

3.2.6 結論

可以看到，在有索引的情況下，更新不同的行，InnoDB 預設的行鎖不會阻塞。

3.3 索引失效情況下的行鎖示範

提示：還是老規矩，兩個會話（終端），左邊是白色背景的，右邊是黑色背景的，並且均設定 autocommit = 0

3.3.1 在左側會話中執行更新

Sql 語句：（注意：name = 1001 的 1001 兩邊沒有加單引號）

UPDATE employee SET money = money + 10000 WHERE name = 1001;

結果：

3.3.2 在右側會話中執行更新

Sql 語句：

UPDATE employee SET money = money + 5000 WHERE name = ‘1002‘;

結果：

被阻塞！說明左側會話鎖住了整張表！

3.3.3 左側執行提交（注意查看右側會話中Sql執行的情況變化）

3.3.4 查看右側會話

右側會話中的更新操作被執行，耗時 20.68 秒

3.3.5 右側也提交

3.3.6 左側查看結果

3.3.7 右側查看結果

兩邊結果相同，且正確。

3.3.8 結論

Where 條件中的查詢欄位雖然有索引，但是索引失效時（本例子中是字串沒有加單引號），InnoDB 預設的行鎖更新操作變為表鎖。 

4 結論

沒有索引或者索引失效時，InnoDB 的行鎖變表鎖

原因：Mysql 的行鎖是通過索引實現的！

 

Mysql鎖機制--索引失效導致行鎖變表鎖