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

神一樣的程式設計發表於2018-09-11
MySql

本文對鎖、事務、併發控制做一個總結，看了網上很多文章，描述非常不準確。如有與您觀點不一致，歡迎有理有據的拍磚！

mysql伺服器邏輯架構

這裡寫圖片描述

每個連線都會在mysql服務端產生一個執行緒（內部通過執行緒池管理執行緒），比如一個select語句進入，mysql首先會在查詢快取中查詢是否快取了這個select的結果集，如果沒有則繼續執行 解析、優化、執行的過程；否則會之間從快取中獲取結果集。

mysql併發控制——共享鎖、排他鎖

共享鎖

共享鎖也稱為讀鎖，讀鎖允許多個連線可以同一時刻併發的讀取同一資源,互不干擾；

排他鎖

排他鎖也稱為寫鎖，一個寫鎖會阻塞其他的寫鎖或讀鎖，保證同一時刻只有一個連線可以寫入資料，同時防止其他使用者對這個資料的讀寫。

鎖策略

鎖的開銷是較為昂貴的，鎖策略其實就是保證了執行緒安全的同時獲取最大的效能之間的平衡策略。

  • mysql鎖策略：talbe lock(表鎖)

表鎖是mysql最基本的鎖策略，也是開銷最小的鎖，它會鎖定整個表；

具體情況是：若一個使用者正在執行寫操作，會獲取排他的“寫鎖”，這可能會鎖定整個表，阻塞其他使用者的讀、寫操作；

若一個使用者正在執行讀操作，會先獲取共享鎖“讀鎖”，這個鎖執行其他讀鎖併發的對這個表進行讀取，互不干擾。只要沒有寫鎖的進入，讀鎖可以是併發讀取統一資源的。

通常發生在DDL語句\DML不走索引的語句中，比如這個DML update table set columnA=”A” where columnB=“B”.
如果columnB欄位不存在索引（或者不是組合索引字首），會鎖住所有記錄也就是鎖表。如果語句的執行能夠執行一個columnB欄位的索引，那麼會鎖住滿足where的行(行鎖)。

  • mysql鎖策略：row lock(行鎖)

行鎖可以最大限度的支援併發處理，當然也帶來了最大開銷，顧名思義，行鎖的粒度實在每一條行資料。

事務

事務就是一組原子性的sql，或者說一個獨立的工作單元。
事務就是說，要麼mysql引擎會全部執行這一組sql語句，要麼全部都不執行（比如其中一條語句失敗的話）。

比如，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特點：

事務的ACID

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

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

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

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

隔離級別

這裡寫圖片描述

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

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
複製程式碼

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 執行了事務，一致性變化）。

REPEATABLE READ(可重複讀)

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

SERIALIZABLE(可序列化)

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

多版本併發控制-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語句，要考慮是否是唯一索引的等值查詢。

寫鎖-recordLock,gapLock,next key lock

對於使用到唯一索引 等值查詢：比如，where columnA=”…” ，如果columnA上的索引被使用到，
那麼會在滿足where的記錄上加行鎖(for update是排他鎖,lock in shared 是共享鎖，其他寫操作加排他鎖)。這裡是行級鎖，record lock。

對於範圍查詢（使用非唯一的索引）：
比如(做範圍查詢)：where columnA between 10 and 30 ,會導致其他會話中10以後的資料都無法插入(next key lock),從而解決了幻讀問題。

這裡是next key lock 會包括涉及到的所有行。
next key lock=recordLock+gapLock，不僅鎖住相關資料，而且鎖住邊界，從而徹底避免幻讀。

對於沒有索引
鎖表
通常發生在DDL語句\DML不走索引的語句中，比如這個DML update table set columnA=”A” where columnB=“B”.
如果columnB欄位不存在索引（或者不是組合索引字首），會鎖住所有記錄也就是鎖表。如果語句的執行能夠執行一個columnB欄位的索引，那麼會鎖住滿足where的行(行鎖)。

INNODB的MVCC通常是通過在每行資料後邊儲存兩個隱藏的列來實現(其實是三列，第三列是用於事務回滾，此處略去)，
一個儲存了行的建立版本號，另一個儲存了行的更新版本號（上一次被更新資料的版本號）
這個版本號是每個事務的版本號，遞增的。

這樣保證了innodb對讀操作不需要加鎖也能保證正確讀取資料。

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

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

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

髒讀 vs 幻讀 vs 不可重複讀

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

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

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

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

----------------------------------前置準備----------------------------------------
prerequisite：
-- 建立表
mysql>
CREATE TABLE `t_bitfly` (
   `id` bigint(20) NOT NULL DEFAULT '0',
   `value` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
 )

-- 確保當前隔離級別為預設的RR級別

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
---------------------------------------開始--------------------------------------------- 


session A                                           |   session B
                                                    |
                                                    |
mysql> START TRANSACTION;                           |   mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)                |   Query OK, 0 rows affected (0.00 sec)                                        
                                                    |   
                                                    |
mysql> SELECT * FROM test.t_bitfly;                 |   mysql> SELECT * FROM test.t_bitfly; 
Empty set (0.00 sec)                                |   Empty set (0.00 sec)
                                                    |
                                                    |   mysql> INSERT INTO t_bitfly VALUES (1, 'test');
                                                    |   Query OK, 1 row affected (0.00 sec)
                                                    |
                                                    |
mysql> SELECT * FROM test.t_bitfly;                 |
Empty set (0.00 sec)                                |
                                                    |
                                                    |   mysql> commit;
                                                    |   Query OK, 0 rows affected (0.01 sec)                                                
mysql> SELECT * FROM test.t_bitfly;                 |
Empty set (0.00 sec)                                |
-- 可以看到雖然兩次執行結果返回的資料一致，         |
-- 但是不能說明沒有幻讀。接著看：                   |
                                                    |
mysql> INSERT INTO t_bitfly VALUES (1, 'test');     |
ERROR 1062 (23000):                                 |
Duplicate entry '1' for key 'PRIMARY'               |
                                                    |
-- 明明為空的表，為什麼說主鍵重複？——幻讀出現 ！！！       |
複製程式碼

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

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

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

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 等待鎖超時回滾事務：
直觀方法是在兩個事務相互等待時，當一個等待時間超過設定的某一閥值時，對其中一個事務進行回滾，另一個事務就能繼續執行。這種方法簡單有效，在innodb中，引數innodb_lock_wait_timeout用來設定超時時間。

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

如何儘可能避免死鎖

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

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

3）在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖概率。

4）降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。

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

顯式鎖 與 隱式鎖
隱式鎖:我們上文說的鎖都屬於不需要額外語句加鎖的隱式鎖。
顯示鎖：

SELECT ... LOCK IN SHARE MODE(加共享鎖);
SELECT ... FOR UPDATE(加排他鎖);複製程式碼

詳情上文已經說過。

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

select * from information_schema.innodb_trx where trx_state="lock wait";
或
show engine innodb status;複製程式碼

mysql中的事務

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit關閉
set autocommit=1; //1表示AutoCommit開啟
複製程式碼
  • 自動提交（AutoCommit，mysql預設）

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

相關文章