之前的文章提到MySQL的InnoDB存儲(chǔ)引擎使用的是行級鎖，并且默認(rèn)的事務(wù)隔離級別為可重復(fù)讀，而不同于Oracle默認(rèn)的事務(wù)隔離級別提交讀。那么MySQL的InnoDB存儲(chǔ)引擎的鎖機(jī)制的具體表現(xiàn)是怎樣的呢？實(shí)驗(yà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??? |

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

?

首先我們來看看行級鎖的情況：

實(shí)驗(yàn)一：

打開兩個(gè)MySQL客戶端，

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

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

修改客戶端1的事務(wù)提交方式為手動(dòng)提交；

?

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

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

同樣修改客戶端2的事務(wù)提交方式為手動(dòng)提交；

?

在客戶端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

同時(shí)使用索引字段a和非索引字段b更新一條數(shù)據(jù)；

?

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

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

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

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

實(shí)驗(yàn)二：

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)一的操作；

?

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)一的操作；

?

在客戶端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

同時(shí)使用索引字段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

同時(shí)使用索引字段a（索引值不同于客戶端1SQL語句的索引值）和非索引字段b更新一條數(shù)據(jù)；

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

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

?

實(shí)驗(yàn)三：

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)一的操作；

?

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)一的操作；

?

在客戶端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語句被阻塞；

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

?

實(shí)驗(yàn)四：

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)三的操作；

?

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)三的操作；

?

在客戶端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ù)記錄，同時(shí)索引值與客戶端1的索引值相同（a=4），此時(shí)，客戶端2的update語句被阻塞。

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

?

實(shí)驗(yàn)五：

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)四的操作；

?

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)四的操作；

?

在客戶端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語句被阻塞，這是因?yàn)? 客戶端2的update語句由于沒有使用索引，需要在數(shù)據(jù)表上加意向排他鎖，但在a=4這條記錄上，已經(jīng)存在排他鎖了，索引客戶端2的update語句只能被阻塞。

?

以上實(shí)驗(yàn)說明：

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

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

?

下面我們繼續(xù)實(shí)驗(yàn)與間隙鎖相關(guān)的情況：

實(shí)驗(yàn)六：

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)五的操作；

?

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)五的操作；

?

在客戶端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，此時(shí)，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，此時(shí)，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，此時(shí)，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，此時(shí)，insert語句順利執(zhí)行。

?

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

?

實(shí)驗(yàn)七：

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)六的操作

?

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

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

回滾實(shí)驗(yàn)六的操作

?

在客戶端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');

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

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

?

以上實(shí)驗(yàn)說明：

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

2、???????當(dāng)前一個(gè)事務(wù)不是通過索引字段來進(jìn)行更新操作時(shí)，那么InnoDB的這種間隙鎖就能夠完全避免幻讀的出現(xiàn)，因?yàn)樗鼤?huì)將整個(gè)表鎖住，在當(dāng)前事務(wù)提交或回滾之前，阻塞所以insert操作。

?

說明：

1、????????以上實(shí)驗(yàn)的所以update操作，更換為delete操作，效果完全一樣；

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

?

?

?

MySQL InnoDB存儲(chǔ)引擎鎖機(jī)制實(shí)驗(yàn)