深入理解 MySQL—鎖、事務與併發控制

```範圍查詢
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_new_table_a` (`a`),
  KEY `idx_new_table_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

INSERT INTO `new_table` VALUES (1,1,'1'),(2,3,'2'),(3,5,'3'),(4,8,'4'),(5,11,'5'),(6,2,'6'),(7,2,'7'),(8,2,'8'),(9,4,'9'),(10,4,'10');



######## 事務一 ########
START TRANSACTION;
SELECT * FROM new_table WHERE a between 5 and 8 FOR UPDATE;
##暫不commit

######## 事務二 ########

SELECT * FROM new_table WHERE a = 4 FOR UPDATE;

##順利執行！因為gap鎖可以共存；


######## 事務三 ########

 SELECT * FROM new_table WHERE b = 3 FOR UPDATE;

##獲取鎖超時，失敗。因為事務一的gap鎖定了 b=3的資料。

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

##事務一
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

##事務二

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
##失敗，已被鎖定

mysql> SHOW ENGINE INNODB STATUS

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit關閉
set autocommit=1; //1表示AutoCommit開啟

MySQL 預設採用 AutoCommit 樣式，也就是每個 sql 都是一個事務，並不需要顯示的執行事務。如果 autoCommit 關閉，那麼每個 sql 都預設開啟一個事務，只有顯式的執行“commit”後這個事務才會被提交。

• 顯示事務 (START TRANSACTION…COMMIT)

比如，tim 要給 bill 轉賬100塊錢：

1.檢查 tim 的賬戶餘額是否大於100塊；

2.tim 的賬戶減少100塊；

3.bill 的賬戶增加100塊；

這三個操作就是一個事務，必須打包執行，要麼全部成功， 要麼全部不執行，其中任何一個操作的失敗都會導致所有三個操作“不執行”——回滾。

CREATE DATABASE IF NOT EXISTS employees;
USE employees;

CREATE TABLE `employees`.`account` (
  `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ;
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200'); 
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200'); 

START TRANSACTION;
SELECT p_money FROM account WHERE p_name="tim";-- step1
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";-- step2
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";-- step3
COMMIT;

一個良好的事務系統，必須滿足ACID特點：

3.1、事務的ACID：

• A:atomiciy 原子性：一個事務必須保證其中的操作要麼全部執行，要麼全部回滾，不可能存在只執行了一部分這種情況出現。

• C:consistency 一致性：資料必須保證從一種一致性的狀態轉換為另一種一致性狀態。 比如上一個事務中執行了第二步時系統崩潰了，資料也不會出現 bill 的賬戶少了100塊，但是 tim 的賬戶沒變的情況。要麼維持原裝（全部回滾），要麼 bill 少了100塊同時 tim 多了100塊，只有這兩種一致性狀態的。

• I：isolation 隔離性：在一個事務未執行完畢時，通常會保證其他 Session 無法看到這個事務的執行結果。

• D:durability 永續性：事務一旦 commit，則資料就會儲存下來，即使提交完之後系統崩潰，資料也不會丟失。

4. 隔離級別

檢視系統隔離級別：
select @@global.tx_isolation;
檢視當前會話隔離級別
select @@tx_isolation;
設定當前會話隔離級別
SET session TRANSACTION ISOLATION LEVEL serializable;
設定全域性系統隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4.1、 READ UNCOMMITTED (未提交讀,可臟讀)

事務中的修改，即使沒有提交，對其他會話也是可見的。可以讀取未提交的資料——臟讀。臟讀會導致很多問題，一般不適用這個隔離級別。實體：

-- ------------------------- read-uncommitted實體 ------------------------------
-- 設定全域性系統隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED";
-- commit;

-- Session B
SELECT * FROM USER;

//SessionB Console 可以看到Session A未提交的事物處理，在另一個Session 中也看到了，這就是所謂的臟讀
id    name
2    READ UNCOMMITTED
34    READ UNCOMMITTED

4.2、READ COMMITTED (提交讀或不可重覆讀，幻讀)

一般資料庫都預設使用這個隔離級別（MySQL 不是）， 這個隔離級別保證了一個事務如果沒有完全成功（commit 執行完），事務中的操作對其他會話是不可見的。

-- ------------------------- read-cmmitted實體 ------------------------------
-- 設定全域性系統隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ  COMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ COMMITTED";
-- COMMIT;

-- Session B
SELECT * FROM USER;

//Console OUTPUT:
id    name
2    READ UNCOMMITTED
34    READ UNCOMMITTED


---------------------------------------------------
-- 當 Session  A執行了commit，Session B得到如下結果：
id    name
2    READ COMMITTED
34    READ COMMITTED

也就驗證了 read committed 級別在事物未完成 commit 操作之前修改的資料對其他 Session 不可見，執行了 commit 之後才會對其他 Session 可見。我們可以看到 Session B 兩次查詢得到了不同的資料。

read committed 隔離級別解決了臟讀的問題，但是會對其他 Session 產生兩次不一致的讀取結果（因為另一個 Session 執行了事務，一致性變化）。

4.3、 REPEATABLE READ (可重覆讀)

一個事務中多次執行統一讀 SQL,傳回結果一樣。這個隔離級別解決了臟讀的問題，幻讀問題。這裡指的是 innodb 的 rr 級別，innodb 中使用 next-key 鎖對”當前讀”進行加鎖，鎖住行以及可能產生幻讀的插入位置，阻止新的資料插入產生幻行。下文中詳細分析。具體請參考 MySQL 手冊：

https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

4.4、 SERIALIZABLE (可序列化)

最強的隔離級別，透過給事務中每次讀取的行加鎖，寫加寫鎖，保證不產生幻讀問題，但是會導致大量超時以及鎖爭用問題。

5. 併發控制 與 MVCC

MVCC (multiple-version-concurrency-control）

它是個行級鎖的變種， 在普通讀情況下避免了加鎖操作，因此開銷更低。雖然實現不同，但通常都是實現非阻塞讀，對於寫操作只鎖定必要的行。

• 一致性讀 （就是讀取快照）select * from table ….

• 當前讀(就是讀取實際的持久化的資料)特殊的讀操作，插入/更新/刪除操作，屬於當前讀，處理的都是當前的資料，需要加鎖。select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete;

註意：select …… from where…… （沒有額外加鎖字尾）使用MVCC，保證了讀快照(MySQL 稱為 consistent read)，所謂一致性讀或者讀快照就是讀取當前事務開始之前的資料快照，在這個事務開始之後的更新不會被讀到。詳細情況下文 select 的詳述。

對於加鎖讀 SELECT with FOR UPDATE (排他鎖) or LOCK IN SHARE MODE (共享鎖)、 update、delete陳述句，要考慮是否是唯一索引的等值查詢。

INNODB 的 MVCC 通常是透過在每行資料後邊儲存兩個隱藏的列來實現(其實是三列，第三列是用於事務回滾，此處略去)，一個儲存了行的建立版本號，另一個儲存了行的更新版本號（上一次被更新資料的版本號） 這個版本號是每個事務的版本號，遞增的。這樣保證了 innodb 對讀操作不需要加鎖也能保證正確讀取資料。

5.1、MVCC select無鎖操作 與 維護版本號

下邊在 MySQL 預設的 Repeatable Read 隔離級別下，具體看看 MVCC 操作：

• Select（快照讀，所謂讀快照就是讀取當前事務之前的資料。）：

  a.InnoDB 只 select 查詢版本號早於當前版本號的資料行，這樣保證了讀取的資料要麼是在這個事務開始之前就已經 commit 了的（早於當前版本號），要麼是在這個事務自身中執行建立操作的資料（等於當前版本號）。

  b.查詢行的更新版本號要麼未定義，要麼大於當前的版本號(為了保證事務可以讀到老資料)，這樣保證了事務讀取到在當前事務開始之後未被更新的資料。

  註意：這裡的 select 不能有 for update、lock in share 陳述句。總之要只傳回滿足以下條件的行資料，達到了快照讀的效果：

(行建立版本號當前版本號 && (行更新版本號==null or 行更新版本號>當前版本號 ) )

• Insert

  InnoDB為這個事務中新插入的行，儲存當前事務版本號的行作為行的行建立版本號。

• Delete InnoDB 為每一個刪除的行儲存當前事務版本號，作為行的刪除標記。

• Update

  將存在兩條資料，保持當前版本號作為更新後的資料的新增版本號，同時儲存當前版本號作為老資料行的更新版本號。

當前版本號—寫—>新資料行建立版本號 && 當前版本號—寫—>老資料更新版本號();

5.2、臟讀 vs 幻讀 vs 不可重覆讀

臟讀：一事務未提交的中間狀態的更新資料 被其他會話讀取到。

當一個事務正在訪問資料，並且對資料進行了修改， 而這種修改還沒有 提交到資料庫中(commit 未執行)， 這時，另外會話也訪問這個資料，因為這個資料是還沒有提交， 那麼另外一個會話讀到的這個資料是臟資料，依據臟資料所做的操作也可能是不正確的。

不可重覆讀：簡單來說就是在一個事務中讀取的資料可能產生變化，ReadCommitted 也稱為不可重覆讀。

在同一事務中，多次讀取同一資料傳回的結果有所不同。換句話說就是，後續讀取可以讀到另一會話事務已提交的更新資料。相反，“可重覆讀”在同一事務中多次讀取資料時，能夠保證所讀資料一樣， 也就是，後續讀取不能讀到另一會話事務已提交的更新資料。

幻讀：會話T1事務中執行一次查詢，然後會話T2新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢的條件。然後T1又使用相同 的查詢再次對錶進行檢索，但是此時卻看到了事務T2剛才插入的新行。這個新行就稱為“幻像”，因為對T1來說這一行就像突然 出現的一樣。innoDB 的 RR 級別無法做到完全避免幻讀，下文詳細分析。

5.3、 如何保證 rr 級別絕對不產生幻讀？

在使用的 select …where 陳述句中加入 for update (排他鎖) 或者 lock in share mode (共享鎖)陳述句來實現。其實就是鎖住了可能造成幻讀的資料，阻止資料的寫入操作。

其實是因為資料的寫入操作(insert 、update)需要先獲取寫鎖，由於可能產生幻讀的部分，已經獲取到了某種鎖，所以要在另外一個會話中獲取寫鎖的前提是當前會話中釋放所有因加鎖陳述句產生的鎖。

5.4、 從另一個角度看鎖：顯式鎖、隱式鎖

隱式鎖：我們上文說的鎖都屬於不需要額外陳述句加鎖的隱式鎖。

顯示鎖：

SELECT ... LOCK IN SHARE MODE(加共享鎖);
SELECT ... FOR UPDATE(加排他鎖);

詳情上文已經說過。

5.5、檢視鎖情況

透過如下 sql 可以檢視等待鎖的情況

select * from information_schema.innodb_trx where trx_state="lock wait";
或
show engine innodb status;

6、MySQL 死鎖問題

死鎖，就是產生了迴圈等待鏈條，我等待你的資源，你卻等待我的資源，我們都相互等待，誰也不釋放自己佔有的資源，導致無線等待下去。比如：

//Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
//Thread B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;

當執行緒A執行到第一條陳述句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;鎖定了p_name=”tim” 的行資料；並且試圖獲取 p_name=”bill” 的資料；

此時，恰好，執行緒B也執行到第一條陳述句：UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;鎖定了 p_name=”bill” 的資料，同時試圖獲取 p_name=”tim” 的資料；

此時，兩個執行緒就進入了死鎖，誰也無法獲取自己想要獲取的資源，進入無線等待中，直到超時！

innodb_lock_wait_timeout  等待鎖超時回滾事務：

直觀方法是在兩個事務相互等待時，當一個等待時間超過設定的某一閥值時，對其中一個事務進行回滾，另一個事務就能繼續執行。

這種方法簡單有效，在i nnodb 中，引數 innodb_lock_wait_timeout 用來設定超時時間。

wait-for graph 演演算法來主動進行死鎖檢測：innodb 還提供了 wait-for graph 演演算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要併進入等待時，wait-for graph 演演算法都會被觸發。

6.1、如何盡可能避免死鎖

• 以固定的順序訪問表和行。比如兩個更新資料的事務，事務A 更新資料的順序 為1，2；事務B更新資料的順序為2，1。這樣更可能會造成死鎖；

• 大事務拆小。大事務更傾向於死鎖，如果業務允許，將大事務拆小；

• 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖機率；

• 降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。（我司 MySQL 規範做法）；

• 為表新增合理的索引。可以看到如果不走索引將會為表的每一行記錄新增上鎖，死鎖的機率大大增大。