Mysql鎖機制分析

前言

資料庫鎖定機制簡單來說就是資料庫為了保證資料的一致性而使各種共享資源在被併發訪問訪問變得有序所設計的一種規則；對於任何一種資料庫來說都需要有相應的鎖定機制，Mysql也不例外。

Mysql幾種鎖定機制型別

MySQL 各儲存引擎使用了三種型別（級別）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。

1.行級鎖定

鎖定物件的顆粒度很小，只對當前行進行鎖定，所以發生鎖定資源爭用的概率也最小，能夠給予應用程式儘可能大的併發處理能力；弊端就是獲取鎖釋放鎖更加頻繁，系統消耗更大，同時行級鎖定也最容易發生死鎖；
行級鎖定的主要是Innodb儲存引擎和NDB Cluster儲存引擎；

2.頁級鎖定

鎖定顆粒度介於行級鎖定與表級鎖之間，每頁有多行資料，併發處理能力以及獲取鎖定所需要的資源開銷在兩者之間；
頁級鎖定主要是BerkeleyDB 儲存引擎；

3.表級鎖定

一次會將整張表鎖定，該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小，而且可以避免死鎖問題；弊端就是鎖定資源爭用的概率最高，併發處理能力最低；
使用表級鎖定的主要是MyISAM，Memory，CSV等一些非事務性儲存引擎。

本文重點介紹Innodb儲存引擎使用的行級鎖定；

兩段鎖協議(2PL)

兩段鎖協議規定所有的事務應遵守的規則：
1.在對任何資料進行讀、寫操作之前，首先要申請並獲得對該資料的封鎖；
2.在釋放一個封鎖之後，事務不再申請和獲得其它任何封鎖；

即事務的執行分為兩個階段：
第一階段是獲得封鎖的階段，稱為擴充套件階段；第二階段是釋放封鎖的階段，稱為收縮階段；

begin;insert ...   加鎖1update ...   加鎖2commit;      事務提交時，釋放鎖1，鎖2

如果在加鎖2的時候，加鎖不成功，則進入等待狀態，直到加鎖成功才繼續執行；
如果有另外一個事務獲取鎖的時候順序剛好相反，是有可能導致死鎖的；為此有了一次性封鎖法，要求事務必須一次性將所有要使用的資料全部加鎖，否則就不能繼續執行；

定理：若所有事務均遵守兩段鎖協議，則這些事務的所有交叉排程都是可序列化的（序列化很重要，尤其是在資料恢復和備份的時候）；

行級鎖定（悲觀鎖）

1.共享鎖和排他鎖

Innodb的行級鎖定同樣分為兩種型別：共享鎖和排他鎖；
共享鎖：當一個事務獲得共享鎖之後，它只可以進行讀操作，所以共享鎖也叫讀鎖，多個事務可以同時獲得某一行資料的共享鎖；
排他鎖：而當一個事務獲得一行資料的排他鎖時，就可以對該行資料進行讀和寫操作，所以排他鎖也叫寫鎖，排他鎖與共享鎖和其他的排他鎖不相容；

既然資料庫提供了共享鎖和排他鎖，那具體用在什麼地方：
1.1在資料庫操作中，為了有效保證併發讀取資料的正確性，提出的事務隔離級別，隔離級別就使用了鎖機制；
1.2提供了相關的SQL，可以方便的在程式中使用；

2.事務隔離級別和鎖的關係

資料庫隔離級別：未提交讀(Read uncommitted)，已提交讀(Read committed)，可重複讀(Repeatable read)和可序列化(Serializable)；
未提交讀(Read uncommitted)：可能讀取到其他會話中未提交事務修改的資料，會出現髒讀(Dirty Read)；
已提交讀(Read committed)：只能讀取到已經提交的資料，會出現不可重複讀(NonRepeatable Read)；
可重複讀(Repeatable read)：InnoDB預設級別，不會出現不可重複讀(NonRepeatable Read)，但是會出現幻讀(Phantom Read);
可序列化(Serializable)：強制事務排序，使之不可能相互衝突，從而解決幻讀問題，使用表級共享鎖，讀寫相互都會阻塞；

常用的2種隔離級別是：已提交讀(Read committed)和可重複讀(Repeatable read)；

3.已提交讀

3.1準備測試表

CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
 
mysql> insert into test_lock values(null,'zhaohui',1);
mysql> insert into test_lock values(null,'zhaohui2',2);

3.2檢視和設定隔離級別

mysql> SELECT @@tx_isolation;
+-----------------+| @@tx_isolation  |
+-----------------+| REPEATABLE-READ |
+-----------------+1 row in set
 
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected
 
mysql> SELECT @@tx_isolation;
+----------------+| @@tx_isolation |
+----------------+| READ-COMMITTED |
+----------------+

3.3模擬多個事務交叉執行

Session1執行查詢

mysql> begin;Query OK, 0 rows affectedmysql> select * from test_lock where id=1;
+----+---------+------+| id | name    | type |+----+---------+------+|  1 | zhaohui |    1 |
+----+---------+------+1 row in set

Session2更新資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> update test_lock set name='zhaohui_new' where id=1;
  
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected

Session1執行查詢

mysql> select * from test_lock where id=1;
+----+-------------+------+| id | name        | type |+----+-------------+------+|  1 | zhaohui_new |    1 |
+----+-------------+------+1 row in set 
mysql> commit;Query OK, 0 rows affected

Session1中出現了不可重複讀(NonRepeatable Read)，也就是在查詢的時候沒有鎖住相關的資料，導致出現了不可重複讀，但是寫入、修改和刪除資料還是加鎖了，如下所示：

Session1更新資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> update test_lock set name='zhaohui_new2' where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Session2更新資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> update test_lock set name='zhaohui_new3' where id=1;
1205 - Lock wait timeout exceeded; try restarting transaction

Session2更新在更新同一條資料的時候超時了，在更新資料的時候新增了排他鎖；

4.可重複讀

4.1檢視和設定隔離級別

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected
 
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+1 row in set

4.2模擬多個事務交叉執行

Session1執行查詢

mysql> begin;Query OK, 0 rows affected 
mysql> select * from test_lock where type=2;+----+----------+------+| id | name     | type |+----+----------+------+|  2 | zhaohui2 |    2 |
+----+----------+------+1 row in set

Session2更新資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> update test_lock set name='zhaohui2_new' where type=2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> commit;
Query OK, 0 rows affected

Session1執行查詢

mysql> select * from test_lock where type=2;+----+----------+------+| id | name     | type |+----+----------+------+|  2 | zhaohui2 |    2 |
+----+----------+------+1 row in set

可以發現2次查詢的資料結果是一樣的，實現了可重複讀(Repeatable read)，再來看一下是否有幻讀(Phantom Read)的問題；

Session3插入資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> insert into test_lock values(null,'zhaohui3',2);
Query OK, 1 row affected
 
mysql> commit;
Query OK, 0 rows affected

Session1執行查詢

mysql> select * from test_lock where type=2;+----+----------+------+| id | name     | type |+----+----------+------+|  2 | zhaohui2 |    2 |
+----+----------+------+1 row in set

可以發現可重複讀(Repeatable read)隔離級別下，也不會出現幻讀的現象；

分析一下原因：如何通過悲觀鎖的方式去實現可重複讀和不出現幻讀的現象，對讀取的資料加共享鎖，對同樣的資料執行更新操作就只能等待，這樣就可以保證可重複讀，但是對於不出現幻讀的現象無法通過鎖定行資料來解決；
最終看到的現象是沒有幻讀的問題，同時如果對讀取的資料加共享鎖，更新相同資料應該會等待，上面的例項中並沒有出現等待，所以mysql內部應該還有其他鎖機制--MVCC機制；

5.悲觀鎖SQL使用

5.1共享鎖使用(lock in share mode)

Session1查詢資料

mysql> begin;Query OK, 0 rows affected 
mysql> select * from test_lock where type=2 lock in share mode;+----+--------------+------+| id | name         | type |+----+--------------+------+|  2 | zhaohui2_new |    2 |
|  3 | zhaohui3     |    2 |
+----+--------------+------+2 rows in set

Session2查詢資料

mysql> begin;Query OK, 0 rows affected 
mysql> select * from test_lock where type=2 lock in share mode;+----+--------------+------+| id | name         | type |+----+--------------+------+|  2 | zhaohui2_new |    2 |
|  3 | zhaohui3     |    2 |
+----+--------------+------+2 rows in set

Session3更新資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> update test_lock set name='zhaohui3_new' where id=3;
1205 - Lock wait timeout exceeded; try restarting transaction

Session1和Session2使用了共享鎖，所以可以存在多個，並不衝突，但是Session3更新操作需要加上排他鎖，和共享鎖不能同時存在；

5.2排他鎖使用(for update)

Session1查詢資料

mysql> begin;Query OK, 0 rows affected 
mysql> select * from test_lock where type=2 for update;+----+--------------+------+| id | name         | type |+----+--------------+------+|  2 | zhaohui2_new |    2 |
|  3 | zhaohui3     |    2 |
+----+--------------+------+2 rows in set

Session2查詢資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> select * from test_lock where type=2 for update;
Empty set

Session3更新資料

mysql> begin;
Query OK, 0 rows affected
 
mysql> update test_lock set name='zhaohui3_new' where id=3;
1205 - Lock wait timeout exceeded; try restarting transaction

排他鎖只能有一個同時存在，所有Session2和Session3都將等等超時；

多版本併發控制MVCC

多版本併發控制(Multiversion Concurrency Control)：每一個寫操作都會建立一個新版本的資料，讀操作會從有限多個版本的資料中挑選一個最合適的結果直接返回；讀寫操作之間的衝突就不再需要被關注，而管理和快速挑選資料的版本就成了MVCC需要解決的主要問題。
為什麼要引入此機制，首先通過悲觀鎖來處理讀請求是很耗效能的，其次資料庫的事務大都是隻讀的，讀請求是寫請求的很多倍，最後如果沒有併發控制機制，最壞的情況也是讀請求讀到了已經寫入的資料，這對很多應用完全是可以接受的；

再來看一下可重複讀(Repeatable read)現象，通過MVCC機制讀操作只讀該事務開始前的資料庫的快照(snapshot), 這樣在讀操作不用阻塞寫操作，寫操作不用阻塞讀操作的同時，避免了髒讀和不可重複讀;

當然並不是說悲觀鎖就沒有用了，在資料更新的時候資料庫預設還是使用悲觀鎖的，所以MVCC是可以整合起來一起使用的(MVCC+2PL)，用來解決讀-寫衝突的無鎖併發控制；
MVCC使用快照讀的方式，解決了不可重複讀和幻讀的問題，如上面的例項所示：select查詢的一直是快照資訊，不需要新增任何鎖；
以上例項中使用的select方式把它稱為快照讀(snapshot read)，其實事務的隔離級別的讀還有另一層含義：讀取資料庫當前版本資料–當前讀(current read)；

當前讀和Gap鎖

區別普通的select查詢，當前讀對應的sql包括：

select ...for update,select ...lock in share mode,insert,update,delete;

以上sql本身會加悲觀鎖，所以不存在不可重複讀的問題，剩下的就是幻讀的問題；
Session1執行當前讀

mysql> select * from test_lock where type=2 for update;+----+----------------+------+| id | name           | type |+----+----------------+------+|  2 | zhaohui2_new   |    2 |
|  3 | zhaohui3_new_1 |    2 |
+----+----------------+------+2 rows in set

Session2執行插入

mysql> begin;
Query OK, 0 rows affected
 
mysql> insert into test_lock values(null,'zhaohui_001',1);1205 - Lock wait timeout exceeded; try restarting transaction

為什麼明明鎖住的是type=2的資料，當插入type=1也會鎖等待，因為InnoDB對於行的查詢都是採用了Next-Key鎖，鎖定的不是單個值，而是一個範圍(GAP);
如果當前type型別包括：1，2，4，6，8，10鎖住type=2，那麼type=1，2，3會被鎖住，後面的不會，鎖住的是一個區間；這樣也就保證了當前讀也不會出現幻讀的現象；
注：type欄位新增了索引，如果沒有新增索引，gap鎖會鎖住整張表；

樂觀鎖

樂觀鎖是一種思想，認為事務間爭用沒有那麼多，和悲觀鎖是相對的，樂觀鎖在java的併發包中大量的使用；一般採用以下方式：使用版本號(version)機制來實現，版本號就是為資料新增一個版本標誌，一般在表中新增一個version欄位；當讀取資料的時候把version也取出來，然後version+1，更新資料庫的時候對比第一次取出來的version和資料庫裡面的version是否一致，如果一致則更新成功，否則失敗進入重試，具體使用大致如下：

begin;select id,name,version from test_lock where id=1;
....update test_lock set name='xxx',version=version+1 where id=1 and version=${version};commit;

先查詢後更新，需要保證原子性，要麼使用悲觀鎖的方式，對整個事務加鎖；要麼使用樂觀鎖的方式，如果在讀多寫少的系統中，樂觀鎖效能更好；

總結

本文首先從Mysql的悲觀鎖出發，然後介紹了悲觀鎖和事務隔離級別之間的關係，並分析為什麼沒有使用悲觀鎖來實現隔離級別；然後從問題出發分別介紹了MVCC和Gap鎖是如何解決了不可重複讀的問題和幻讀的問題；最後介紹了樂觀鎖經常被用在讀資料遠大於寫資料的系統中。

公眾號推薦: