深入理解MySQL——鎖、事務與并發(fā)控制
mysql服務器邏輯架構
每個連接都會在mysql服務端產(chǎn)生一個線程（內(nèi)部通過線程池管理線程），比如一個select語句進入，mysql首先會在查詢緩存中查找是否緩存了這個select的結果集，如果沒有則繼續(xù)執(zhí)行 解析、優(yōu)化、執(zhí)行的過程；否則會之間從緩存中獲取結果集。
mysql并發(fā)控制——共享鎖、排他鎖
共享鎖
共享鎖也稱為讀鎖，讀鎖允許多個連接可以同一時刻并發(fā)的讀取同一資源,互不干擾；
排他鎖
排他鎖也稱為寫鎖，一個寫鎖會阻塞其他的寫鎖或讀鎖，保證同一時刻只有一個連接可以寫入數(shù)據(jù)，同時防止其他用戶對這個數(shù)據(jù)的讀寫。
鎖策略
鎖的開銷是較為昂貴的，鎖策略其實就是保證了線程安全的同時獲取最大的性能之間的平衡策略。
-
mysql鎖策略：talbe lock(表鎖)
表鎖是mysql最基本的鎖策略，也是開銷最小的鎖，它會鎖定整個表；
具體情況是：若一個用戶正在執(zhí)行寫操作，會獲取排他的“寫鎖”，這可能會鎖定整個表，阻塞其他用戶的讀、寫操作；
若一個用戶正在執(zhí)行讀操作，會先獲取共享鎖“讀鎖”，這個鎖運行其他讀鎖并發(fā)的對這個表進行讀取，互不干擾。只要沒有寫鎖的進入，讀鎖可以是并發(fā)讀取統(tǒng)一資源的。
通常發(fā)生在DDL語句\DML不走索引的語句中，比如這個DML update table set columnA=”A” where columnB=“B”.
如果columnB字段不存在索引（或者不是組合索引前綴），會鎖住所有記錄也就是鎖表。如果語句的執(zhí)行能夠執(zhí)行一個columnB字段的索引，那么會鎖住滿足where的行(行鎖)。
-
mysql鎖策略：row lock(行鎖)
行鎖可以最大限度的支持并發(fā)處理，當然也帶來了最大開銷，顧名思義，行鎖的粒度實在每一條行數(shù)據(jù)。
事務
事務就是一組原子性的sql，或者說一個獨立的工作單元。
事務就是說，要么mysql引擎會全部執(zhí)行這一組sql語句，要么全部都不執(zhí)行（比如其中一條語句失敗的話）。
比如，tim要給bill轉賬100塊錢：
1.檢查tim的賬戶余額是否大于100塊；
2.tim的賬戶減少100塊；
3.bill的賬戶增加100塊；
這三個操作就是一個事務，必須打包執(zhí)行，要么全部成功，要么全部不執(zhí)行，其中任何一個操作的失敗都會導致所有三個操作“不執(zhí)行”——回滾。
- 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;
一個良好的事務系統(tǒng)，必須滿足ACID特點：
事務的ACID
-
A:atomiciy原子性
一個事務必須保證其中的操作要么全部執(zhí)行，要么全部回滾，不可能存在只執(zhí)行了一部分這種情況出現(xiàn)。 -
C:consistency一致性
數(shù)據(jù)必須保證從一種一致性的狀態(tài)轉換為另一種一致性狀態(tài)。
比如上一個事務中執(zhí)行了第二步時系統(tǒng)崩潰了，數(shù)據(jù)也不會出現(xiàn)bill的賬戶少了100塊，但是tim的賬戶沒變的情況。要么維持原裝（全部回滾），要么bill少了100塊同時tim多了100塊，只有這兩種一致性狀態(tài)的 -
I：isolation隔離性
在一個事務未執(zhí)行完畢時，通常會保證其他Session 無法看到這個事務的執(zhí)行結果 -
D:durability持久性
事務一旦commit，則數(shù)據(jù)就會保存下來，即使提交完之后系統(tǒng)崩潰，數(shù)據(jù)也不會丟失。
隔離級別
查看系統(tǒng)隔離級別： select @@global.tx_isolation; 查看當前會話隔離級別 select @@tx_isolation; 設置當前會話隔離級別 SET session TRANSACTION ISOLATION LEVEL serializable; 設置全局系統(tǒng)隔離級別 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
READ UNCOMMITTED(未提交讀,可臟讀)
事務中的修改，即使沒有提交，對其他會話也是可見的。
可以讀取未提交的數(shù)據(jù)——臟讀。臟讀會導致很多問題，一般不適用這個隔離級別。
實例：
- -- ------------------------- read-uncommitted實例 ------------------------------
- -- 設置全局系統(tǒng)隔離級別
- 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(提交讀或不可重復讀，幻讀)
一般數(shù)據(jù)庫都默認使用這個隔離級別（mysql不是），這個隔離級別保證了一個事務如果沒有完全成功（commit執(zhí)行完），事務中的操作對其他會話是不可見的。
- -- ------------------------- read-cmmitted實例 ------------------------------
- -- 設置全局系統(tǒng)隔離級別
- 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執(zhí)行了commit，Session B得到如下結果：
- id name
- 2 READ COMMITTED
- 34 READ COMMITTED
也就驗證了read committed級別在事物未完成commit操作之前修改的數(shù)據(jù)對其他Session 不可見，執(zhí)行了commit之后才會對其他Session 可見。
我們可以看到Session B兩次查詢得到了不同的數(shù)據(jù)。
read committed隔離級別解決了臟讀的問題，但是會對其他Session 產(chǎn)生兩次不一致的讀取結果（因為另一個Session 執(zhí)行了事務，一致性變化）。
REPEATABLE READ(可重復讀)
一個事務中多次執(zhí)行統(tǒng)一讀SQL,返回結果一樣。
這個隔離級別解決了臟讀的問題，幻讀問題。這里指的是innodb的rr級別，innodb中使用next-key鎖對”當前讀”進行加鎖，鎖住行以及可能產(chǎn)生幻讀的插入位置，阻止新的數(shù)據(jù)插入產(chǎn)生幻行。
下文中詳細分析。
具體請參考mysql手冊
https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
SERIALIZABLE(可串行化)
最強的隔離級別，通過給事務中每次讀取的行加鎖，寫加寫鎖，保證不產(chǎn)生幻讀問題，但是會導致大量超時以及鎖爭用問題。
多版本并發(fā)控制-MVCC
MVCC(multiple-version-concurrency-control）是個行級鎖的變種，它在普通讀情況下避免了加鎖操作，因此開銷更低。
雖然實現(xiàn)不同，但通常都是實現(xiàn)非阻塞讀，對于寫操作只鎖定必要的行。
-
一致性讀 （就是讀取快照）
select * from table ….; -
當前讀(就是讀取實際的持久化的數(shù)據(jù))
特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數(shù)據(jù)，需要加鎖。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;
注意：select …… from where…… （沒有額外加鎖后綴）使用MVCC，保證了讀快照(mysql稱為consistent read)，所謂一致性讀或者讀快照就是讀取當前事務開始之前的數(shù)據(jù)快照，在這個事務開始之后的更新不會被讀到。詳細情況下文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以后的數(shù)據(jù)都無法插入(next key lock),從而解決了幻讀問題。
這里是next key lock 會包括涉及到的所有行。
next key lock=recordLock+gapLock，不僅鎖住相關數(shù)據(jù)，而且鎖住邊界，從而徹底避免幻讀。
對于沒有索引
鎖表
通常發(fā)生在DDL語句\DML不走索引的語句中，比如這個DML update table set columnA=”A” where columnB=“B”.
如果columnB字段不存在索引（或者不是組合索引前綴），會鎖住所有記錄也就是鎖表。如果語句的執(zhí)行能夠執(zhí)行一個columnB字段的索引，那么會鎖住滿足where的行(行鎖)。
INNODB的MVCC通常是通過在每行數(shù)據(jù)后邊保存兩個隱藏的列來實現(xiàn)(其實是三列，第三列是用于事務回滾，此處略去)，
一個保存了行的創(chuàng)建版本號，另一個保存了行的更新版本號（上一次被更新數(shù)據(jù)的版本號）
這個版本號是每個事務的版本號，遞增的。
這樣保證了innodb對讀操作不需要加鎖也能保證正確讀取數(shù)據(jù)。
MVCC select無鎖操作 與 維護版本號
下邊在mysql默認的Repeatable Read隔離級別下，具體看看MVCC操作：
-
Select（快照讀，所謂讀快照就是讀取當前事務之前的數(shù)據(jù)。）：
a.InnoDB只select查找版本號早于當前版本號的數(shù)據(jù)行，這樣保證了讀取的數(shù)據(jù)要么是在這個事務開始之前就已經(jīng)commit了的（早于當前版本號），要么是在這個事務自身中執(zhí)行創(chuàng)建操作的數(shù)據(jù)（等于當前版本號）。b.查找行的更新版本號要么未定義，要么大于當前的版本號(為了保證事務可以讀到老數(shù)據(jù))，這樣保證了事務讀取到在當前事務開始之后未被更新的數(shù)據(jù)。
注意： 這里的select不能有for update、lock in share 語句。
總之要只返回滿足以下條件的行數(shù)據(jù)，達到了快照讀的效果：
(行創(chuàng)建版本號< =當前版本號 && (行更新版本號==null or 行更新版本號>當前版本號 ) )
-
Insert
InnoDB為這個事務中新插入的行，保存當前事務版本號的行作為行的行創(chuàng)建版本號。
-
Delete
InnoDB為每一個刪除的行保存當前事務版本號，作為行的刪除標記。 -
Update
將存在兩條數(shù)據(jù)，保持當前版本號作為更新后的數(shù)據(jù)的新增版本號，同時保存當前版本號作為老數(shù)據(jù)行的更新版本號。
當前版本號—寫—>新數(shù)據(jù)行創(chuàng)建版本號 && 當前版本號—寫—>老數(shù)據(jù)更新版本號();
臟讀 vs 幻讀 vs 不可重復讀
臟讀：一事務未提交的中間狀態(tài)的更新數(shù)據(jù) 被其他會話讀取到。 當一個事務正在訪問數(shù)據(jù)，并且對數(shù)據(jù)進行了修改，而這種修改還沒有 提交到數(shù)據(jù)庫中(commit未執(zhí)行)，這時，另外會話也訪問這個數(shù)據(jù)，因為這個數(shù)據(jù)是還沒有提交， 那么另外一個會話讀到的這個數(shù)據(jù)是臟數(shù)據(jù)，依據(jù)臟數(shù)據(jù)所做的操作也可能是不正確的。
不可重復讀：簡單來說就是在一個事務中讀取的數(shù)據(jù)可能產(chǎn)生變化，ReadCommitted也稱為不可重復讀。
在同一事務中，多次讀取同一數(shù)據(jù)返回的結果有所不同。換句話說就是，后續(xù)讀取可以讀到另一會話事務已提交的更新數(shù)據(jù)。 相反，“可重復讀”在同一事務中多次讀取數(shù)據(jù)時，能夠保證所讀數(shù)據(jù)一樣，也就是，后續(xù)讀取不能讀到另一會話事務已提交的更新數(shù)據(jù)。
幻讀：會話T1事務中執(zhí)行一次查詢，然后會話T2新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢的條件。然后T1又使用相同 的查詢再次對表進行檢索，但是此時卻看到了事務T2剛才插入的新行。這個新行就稱為“幻像”，因為對T1來說這一行就像突然 出現(xiàn)的一樣。
innoDB的RR級別無法做到完全避免幻讀，下文詳細分析。
- ----------------------------------前置準備----------------------------------------
- prerequisite：
- -- 創(chuàng)建表
- 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) |
- -- 可以看到雖然兩次執(zhí)行結果返回的數(shù)據(jù)一致， |
- -- 但是不能說明沒有幻讀。接著看： |
- |
- mysql> INSERT INTO t_bitfly VALUES (1, 'test'); |
- ERROR 1062 (23000): |
- Duplicate entry '1' for key 'PRIMARY' |
- |
- -- 明明為空的表，為什么說主鍵重復？——幻讀出現(xiàn) ?。。?nbsp; |
如何保證rr級別絕對不產(chǎn)生幻讀？
在使用的select …where語句中加入 for update(排他鎖) 或者 lock in share mode(共享鎖)語句來實現(xiàn)。其實就是鎖住了可能造成幻讀的數(shù)據(jù)，阻止數(shù)據(jù)的寫入操作。
其實是因為數(shù)據(jù)的寫入操作(insert 、update)需要先獲取寫鎖，由于可能產(chǎn)生幻讀的部分，已經(jīng)獲取到了某種鎖，所以要在另外一個會話中獲取寫鎖的前提是當前會話中釋放所有因加鎖語句產(chǎn)生的鎖。
mysql死鎖問題
死鎖，就是產(chǎn)生了循環(huán)等待鏈條，我等待你的資源，你卻等待我的資源，我們都相互等待，誰也不釋放自己占有的資源，導致無線等待下去。
比如：
- //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執(zhí)行到第一條語句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;鎖定了p_name=”tim”的行數(shù)據(jù)；并且試圖獲取p_name=”bill”的數(shù)據(jù)；
，此時，恰好，線程B也執(zhí)行到第一條語句：UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;
鎖定了 p_name=”bill”的數(shù)據(jù)，同時試圖獲取p_name=”tim”的數(shù)據(jù)；
此時，兩個線程就進入了死鎖，誰也無法獲取自己想要獲取的資源，進入無線等待中，直到超時！
innodb_lock_wait_timeout 等待鎖超時回滾事務：
直觀方法是在兩個事務相互等待時，當一個等待時間超過設置的某一閥值時，對其中一個事務進行回滾，另一個事務就能繼續(xù)執(zhí)行。這種方法簡單有效，在innodb中，參數(shù)innodb_lock_wait_timeout用來設置超時時間。
wait-for graph算法來主動進行死鎖檢測：
innodb還提供了wait-for graph算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要并進入等待時，wait-for graph算法都會被觸發(fā)。
如何盡可能避免死鎖
1）以固定的順序訪問表和行。比如兩個更新數(shù)據(jù)的事務，事務A 更新數(shù)據(jù)的順序 為1，2；事務B更新數(shù)據(jù)的順序為2，1。這樣更可能會造成死鎖。
2）大事務拆小。大事務更傾向于死鎖，如果業(yè)務允許，將大事務拆小。
3）在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。
4）降低隔離級別。如果業(yè)務允許，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC，可以避免掉很多因為gap鎖造成的死鎖。
5）為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。
顯式鎖 與 隱式鎖
隱式鎖:我們上文說的鎖都屬于不需要額外語句加鎖的隱式鎖。
顯示鎖：
SELECT ... LOCK IN SHARE MODE(加共享鎖); SELECT ... FOR UPDATE(加排他鎖);
詳情上文已經(jīng)說過。
通過如下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都是一個事務，并不需要顯示的執(zhí)行事務。
如果autoCommit關閉，那么每個sql都默認開啟一個事務，只有顯式的執(zhí)行“commit”后這個事務才會被提交。
