之前的文章提到MySQL的InnoDB存儲引擎使用的是行級鎖，并且默認的事務隔離級別為可重復讀，而不同于Oracle默認的事務隔離級別提交讀。那么MySQL的InnoDB存儲引擎的鎖機制的具體表現(xiàn)是怎樣的呢？實驗如下：

首先建立一張測試使用的表：

CREATE TABLE`test_innodb_lock` (

? `a` int(11) DEFAULT NULL,

? `b` varchar(16) DEFAULT NULL,

? KEY `test_innodb_lock_a_IDX` (`a`)

) ENGINE=InnoDB

?????? ?然后再往這張表里插入一些數(shù)據(jù)，以備使用，最終表數(shù)據(jù)如下：

+------+------+

| 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執(zhí)行:

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

修改客戶端1的事務提交方式為手動提交；

?

在客戶端2執(zhí)行：

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

同樣修改客戶端2的事務提交方式為手動提交；

?

在客戶端1執(zhí)行：

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更新一條數(shù)據(jù)；

?

在客戶端2執(zhí)行：

mysql> update test_innodb_lock set b ='xxx' where a=1 and b = 'x';

同時使用索引字段a（并且索引值同客戶端1的值相同）和非索引字段 更新另外一條數(shù)據(jù) ；

結果發(fā)現(xiàn)客戶端2的update語句被阻塞，需要客戶端1提交或回滾才能繼續(xù)執(zhí)行。說明， 雖然兩個事務最終更新的數(shù)據(jù)不是同一條數(shù)據(jù)，但然后可能被鎖定，這是因為兩條SQL語句都使用了相同的索引值（a=1），行級鎖上升為頁級鎖。

實驗二：

在客戶端1執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗一的操作；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗一的操作；

?

在客戶端1執(zhí)行：

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更新一條數(shù)據(jù)；

?

在客戶端2執(zhí)行：

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更新一條數(shù)據(jù)；

更新順利進行，執(zhí)行并沒有被阻塞；

說明，同是根據(jù)索引和非索引字段進行更新數(shù)據(jù)，當兩個事務的SQL語句中的索引條件值不一樣時，更新仍然能夠順利進行。

?

實驗三：

在客戶端1執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗一的操作；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗一的操作；

?

在客戶端1執(zhí)行：

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

通過 非索引 字段更新唯一的一條數(shù)據(jù)記錄，

?

在客戶端2執(zhí)行：

mysql> update test_innodb_lock set b='xxx' where b ='e';

通過非索引字段更新另外一條唯一的一條數(shù)據(jù)記錄，update語句被阻塞；

說明， 一個事務根據(jù)非索引字段更新數(shù)據(jù)時，InnoDB會將整個表給鎖住，行級鎖此時上升為表級鎖。

?

實驗四：

在客戶端1執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗三的操作；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗三的操作；

?

在客戶端1執(zhí)行：

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

只使用索引更新數(shù)據(jù)記錄

?

在客戶端2執(zhí)行：

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

只使用索引更新數(shù)據(jù)記錄，同時索引值與客戶端1的索引值相同（a=4），此時，客戶端2的update語句被阻塞。

說明，這個現(xiàn)象的行級鎖，于我們理解的行級鎖一致，即真正只是鎖定了一條記錄。

?

實驗五：

在客戶端1執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗四的操作；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗四的操作；

?

在客戶端1執(zhí)行：

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

只使用索引更新數(shù)據(jù)記錄

?

在客戶端2執(zhí)行：

mysql> update test_innodb_lock set b ='xxx' where b=’g’;

只使用非索引字段更新數(shù)據(jù)記錄，客戶端2的update語句被阻塞，這是因為 客戶端2的update語句由于沒有使用索引，需要在數(shù)據(jù)表上加意向排他鎖，但在a=4這條記錄上，已經(jīng)存在排他鎖了，索引客戶端2的update語句只能被阻塞。

?

以上實驗說明：

1、???????InnoDB的行級鎖在有些情況下是會自動上升為頁級鎖和表級鎖的，此時數(shù)據(jù)庫的寫性能會急劇下降，并可能出現(xiàn)大量的死鎖（關于死鎖的情況，很容易模仿出來，這里不在舉例）；

2、???????真正的行級鎖，只發(fā)生在所有的事務都是通過索引來進行檢索數(shù)據(jù)的。

?

下面我們繼續(xù)實驗與間隙鎖相關的情況：

實驗六：

在客戶端1執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗五的操作；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗五的操作；

?

在客戶端1執(zhí)行：

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

通過索引更新數(shù)據(jù)記錄，索引值為8；

?

在客戶端2執(zhí)行：

mysql> insert into test_innodb_lock(a,b)values(8,'xxx');

向數(shù)據(jù)表中插入一條數(shù)據(jù)，插入數(shù)據(jù)的索引列的值與客戶端1的SQL語句的索引值相同，都為8，此時，insert語句被阻塞。

?

在客戶端1執(zhí)行：

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

通過索引更新數(shù)據(jù)記錄，索引值為8；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_innodb_lock(a,b)values(5,'xxx');

向數(shù)據(jù)表中插入一條數(shù)據(jù)，插入數(shù)據(jù)的索引列的值小于客戶端1的SQL語句的索引值，但 大于或等于 已有數(shù)據(jù)記錄中最大小于檢索索引（a=8）的索引值5，此時，insert語句被阻塞。

?

在客戶端1執(zhí)行：

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

通過索引更新數(shù)據(jù)記錄，索引值為8；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_innodb_lock(a,b)values(9,'xxx');

向數(shù)據(jù)表中插入一條數(shù)據(jù)，插入數(shù)據(jù)的索引列的值大于客戶端1的SQL語句的索引值，但 小于 已有數(shù)據(jù)記錄中最小大于檢索索引（a=8）的索引值10，此時，insert語句被阻塞。

?

在客戶端1執(zhí)行：

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

通過索引更新數(shù)據(jù)記錄，索引值為8；

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_innodb_lock(a,b)values(10,'xxx');

?

向數(shù)據(jù)表中插入一條數(shù)據(jù)，插入數(shù)據(jù)的索引列的值大于客戶端1的SQL語句的索引值，且 大于或等于 已有數(shù)據(jù)記錄中最小大于檢索索引（a=8）的索引值10，此時，insert語句順利執(zhí)行。

?

以上系列的動作說明，當一個事務在通過索引更新數(shù)據(jù)時，它會將該索引的前后緊緊相鄰的索引記錄鎖住，包括那些根本就不存在的索引值，鎖定的區(qū)間為左閉右開區(qū)間，即[x,y），其中x為小于事務中SQL語句索引值的最大值，y為大于事務中SQL語句索引值的最小值，在本例中，事務中SQL語句索引值為8，索引其鎖定的區(qū)間為[5,10），所以另外一個事務在做insert操作時，索引值大于或等于5且小于10的索引記錄都將被阻塞。需要注意的是，當更新事務的索引值為已有記錄中最大值時，這時所有大于該索引值的記錄，其他事務的insert操作都將被阻塞。這就是InnoDB間隙鎖的具體表現(xiàn)。所以說，InnoDB的間隙鎖避免了部分幻讀，但不是全部，因為它鎖定的是一個區(qū)間，而不是整張表。

?

實驗七：

在客戶端1執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗六的操作

?

在客戶端2執(zhí)行：

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實驗六的操作

?

在客戶端1執(zhí)行：

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執(zhí)行：

mysql> insert into test_innodb_lock(a,b)values(10,'xxx');

插入一條完全不相關的數(shù)據(jù)，該insert語句被阻塞；

說明，當事務1通過 非索引 字段更新一條數(shù)據(jù)是，整張表就會被鎖住，即使是insert操作，也將被阻塞。

?

以上實驗說明：

1、???????InnoDB的間隙鎖是可以避免數(shù)據(jù)出現(xiàn)幻讀，但只是避免部分出現(xiàn)幻讀，當一個事務是通過索引來更新數(shù)據(jù)是，另外一個事務在前一個事務索引值前后的左閉右開區(qū)間是不能并行插入數(shù)據(jù)的，必須等待上一個事務提交或回滾；

2、???????當前一個事務不是通過索引字段來進行更新操作時，那么InnoDB的這種間隙鎖就能夠完全避免幻讀的出現(xiàn)，因為它會將整個表鎖住，在當前事務提交或回滾之前，阻塞所以insert操作。

?

說明：

1、????????以上實驗的所以update操作，更換為delete操作，效果完全一樣；

2、????????如果修改InnoDB的默認事務隔離級別，由可重復讀修改為讀已提交，那么以上現(xiàn)象均不會出現(xiàn)，所以這樣的鎖機制只在可重復讀這一事務隔離級別出現(xiàn)，或者說這是InnoDB可重復讀事務隔離級別的一種實現(xiàn)方式。

?

?

?

MySQL InnoDB存儲引擎鎖機制實驗