深入理解MySQL――鎖、事務與并發控制

opency 6年前發布 | 48K 次閱讀 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鎖對”當前讀”進行加鎖，鎖住行以及可能產生幻讀的插入位置，阻止新的數據插入產生幻行。

下文中詳細分析。

具體請參考mysql手冊

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

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”.

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”后這個事務才會被提交。

來自：http://database.51cto.com/art/201803/568725.htm

本文由用戶 opency 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1521775395921.html

MySQL 數據庫服務器

深入理解MySQL――鎖、事務與并發控制

mysql服務器邏輯架構

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

事務

事務的ACID

隔離級別

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

READ COMMITTED(提交讀或不可重復讀，幻讀)

REPEATABLE READ(可重復讀)

SERIALIZABLE(可串行化)

多版本并發控制-MVCC

寫鎖-recordLock,gapLock,next key lock

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

臟讀 vs 幻讀 vs 不可重復讀

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

mysql死鎖問題

mysql中的事務

相關經驗

相關資訊

相關文檔

目錄

深入理解MySQL――鎖、事務與并發控制

mysql服務器邏輯架構

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

事務

事務的ACID

隔離級別

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

READ COMMITTED(提交讀或不可重復讀，幻讀)

REPEATABLE READ(可重復讀)

SERIALIZABLE(可串行化)

多版本并發控制-MVCC

寫鎖-recordLock,gapLock,next key lock

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

臟讀 vs 幻讀 vs 不可重復讀

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

mysql死鎖問題

mysql中的事務

相關經驗

相關資訊

相關文檔

目錄

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