MySQL InnoDB存儲引擎鎖機制實驗 MySQL的InnoDB存儲引擎使用的是行級鎖，並且默認的事務隔離級別為可重復讀，而不同於Oracle默認的事務隔離級別提交讀。那麼MySQL的InnoDB存儲引擎的鎖機制的具體表現是怎樣的呢？實驗如下： 首先建立一張測試使用的表： CREATE TABLE`test_innodb_lock` ( `a` int(11) DEFAULT NULL, `b` varchar(16) DEFAULT NULL, KEY `test_innodb_lock_a_IDX` (`a`) ) ENGINE=InnoDB 然後再往這張表裡插入一些數據，以備使用，最終表數據如下： +------+------+ | a | b | +------+------+ | 1 | a | | 1 | x | | 1 | y | | 2 | b | | 2 | w | | 2 | z | | 3 | c | | 4 | d | | 5 | e | | 8 | ff | | 8 | f | | 10 | g | +------+------+ 首先我們來看看行級鎖的情況： 實驗一： 打開兩個MySQL客戶端， 在客戶端1執行: mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) 修改客戶端1的事務提交方式為手動提交； 在客戶端2執行： mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) 同樣修改客戶端2的事務提交方式為手動提交； 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where a = 1 and b = 'y'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 同時使用索引字段a和非索引字段b更新一條數據； 在客戶端2執行： mysql> update test_innodb_lock set b ='xxx' where a=1 and b = 'x'; 同時使用索引字段a（並且索引值同客戶端1的值相同）和非索引字段更新另外一條數據； 結果發現客戶端2的update語句被阻塞，需要客戶端1提交或回滾才能繼續執行。說明，雖然兩個事務最終更新的數據不是同一條數據，但然後可能被鎖定，這是因為兩條SQL語句都使用了相同的索引值（a=1），行級鎖上升為頁級鎖。 實驗二： 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗一的操作； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗一的操作； 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where a = 1 and b = 'a'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 同時使用索引字段a和非索引字段b更新一條數據； 在客戶端2執行： mysql> update test_innodb_lock set b ='xxx' where a=2 and b = 'b'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 同時使用索引字段a（索引值不同於客戶端1SQL語句的索引值）和非索引字段b更新一條數據； 更新順利進行，執行並沒有被阻塞； 說明，同是根據索引和非索引字段進行更新數據，當兩個事務的SQL語句中的索引條件值不一樣時，更新仍然能夠順利進行。 實驗三： 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗一的操作； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗一的操作； 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where b = 'd'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 通過非索引字段更新唯一的一條數據記錄， 在客戶端2執行： mysql> update test_innodb_lock set b='xxx' where b ='e'; 通過非索引字段更新另外一條唯一的一條數據記錄，update語句被阻塞； 說明，一個事務根據非索引字段更新數據時，InnoDB會將整個表給鎖住，行級鎖此時上升為表級鎖。 實驗四： 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗三的操作； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗三的操作； 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where a=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 只使用索引更新數據記錄 在客戶端2執行： mysql> update test_innodb_lock set b ='xxx' where a=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 只使用索引更新數據記錄，同時索引值與客戶端1的索引值相同（a=4），此時，客戶端2的update語句被阻塞。 說明，這個現象的行級鎖，於我們理解的行級鎖一致，即真正只是鎖定了一條記錄。 實驗五： 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗四的操作； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗四的操作； 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where a=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 只使用索引更新數據記錄 在客戶端2執行： mysql> update test_innodb_lock set b ='xxx' where b=’g’; 只使用非索引字段更新數據記錄，客戶端2的update語句被阻塞，這是因為客戶端2的update語句由於沒有使用索引，需要在數據表上加意向排他鎖，但在a=4這條記錄上，已經存在排他鎖了，索引客戶端2的update語句只能被阻塞。 以上實驗說明： 1、 InnoDB的行級鎖在有些情況下是會自動上升為頁級鎖和表級鎖的，此時數據庫的寫性能會急劇下降，並可能出現大量的死鎖（關於死鎖的情況，很容易模仿出來，這裡不在舉例）； 2、 真正的行級鎖，只發生在所有的事務都是通過索引來進行檢索數據的。 下面我們繼續實驗與間隙鎖相關的情況： 實驗六： 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗五的操作； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗五的操作； 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where a=8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 通過索引更新數據記錄，索引值為8； 在客戶端2執行： mysql> insert into test_innodb_lock(a,b)values(8,'xxx'); 向數據表中插入一條數據，插入數據的索引列的值與客戶端1的SQL語句的索引值相同，都為8，此時，insert語句被阻塞。 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> update test_innodb_lock set b ='xxx' where a=8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 通過索引更新數據記錄，索引值為8； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_innodb_lock(a,b)values(5,'xxx'); 向數據表中插入一條數據，插入數據的索引列的值小於客戶端1的SQL語句的索引值，但大於或等於已有數據記錄中最大小於檢索索引（a=8）的索引值5，此時，insert語句被阻塞。 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> update test_innodb_lock set b ='xxx' where a=8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 通過索引更新數據記錄，索引值為8； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_innodb_lock(a,b)values(9,'xxx'); 向數據表中插入一條數據，插入數據的索引列的值大於客戶端1的SQL語句的索引值，但小於已有數據記錄中最小大於檢索索引（a=8）的索引值10，此時，insert語句被阻塞。 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> update test_innodb_lock set b ='xxx' where a=8; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 通過索引更新數據記錄，索引值為8； 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_innodb_lock(a,b)values(10,'xxx'); 向數據表中插入一條數據，插入數據的索引列的值大於客戶端1的SQL語句的索引值，且大於或等於已有數據記錄中最小大於檢索索引（a=8）的索引值10，此時，insert語句順利執行。 以上系列的動作說明，當一個事務在通過索引更新數據時，它會將該索引的前後緊緊相鄰的索引記錄鎖住，包括那些根本就不存在的索引值，鎖定的區間為左閉右開區間，即[x,y），其中x為小於事務中SQL語句索引值的最大值，y為大於事務中SQL語句索引值的最小值，在本例中，事務中SQL語句索引值為8，索引其鎖定的區間為[5,10），所以另外一個事務在做insert操作時，索引值大於或等於5且小於10的索引記錄都將被阻塞。需要注意的是，當更新事務的索引值為已有記錄中最大值時，這時所有大於該索引值的記錄，其他事務的insert操作都將被阻塞。這就是InnoDB間隙鎖的具體表現。所以說，InnoDB的間隙鎖避免了部分幻讀，但不是全部，因為它鎖定的是一個區間，而不是整張表。 實驗七： 在客戶端1執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗六的操作 在客戶端2執行： mysql> rollback; Query OK, 0 rows affected (0.00 sec) 回滾實驗六的操作 在客戶端1執行： mysql> update test_innodb_lock set b ='xxx' where b=’a’; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 通過非索引字段更新一條記錄； 在客戶端2執行： mysql> insert into test_innodb_lock(a,b)values(10,'xxx'); 插入一條完全不相關的數據，該insert語句被阻塞； 說明，當事務1通過非索引字段更新一條數據是，整張表就會被鎖住，即使是insert操作，也將被阻塞。 以上實驗說明： 1、 InnoDB的間隙鎖是可以避免數據出現幻讀，但只是避免部分出現幻讀，當一個事務是通過索引來更新數據是，另外一個事務在前一個事務索引值前後的左閉右開區間是不能並行插入數據的，必須等待上一個事務提交或回滾； 2、 當前一個事務不是通過索引字段來進行更新操作時，那麼InnoDB的這種間隙鎖就能夠完全避免幻讀的出現，因為它會將整個表鎖住，在當前事務提交或回滾之前，阻塞所以insert操作。 說明： 1、 以上實驗的所以update操作，更換為delete操作，效果完全一樣； 2、 如果修改InnoDB的默認事務隔離級別，由可重復讀修改為讀已提交，那麼以上現象均不會出現，所以這樣的鎖機制只在可重復讀這一事務隔離級別出現，或者說這是InnoDB可重復讀事務隔離級別的一種實現方式。
