Mysql事務隔離級別與鎖機制

概述

資料庫一般都會并發執行多個事務，多個事務可能會并發的對相同的資料進行CRUD操作，有時候就會導致臟讀、臟寫、不可重復讀、幻讀這些問題，
為了解決多事務并發問題，Mysql資料庫設計了事務隔離機制、鎖機
制、MVCC多版本并發控制隔離機制，用一整套機制來解決多事務并發問題，

事務及其ACID屬性

事務具有以下4個屬性,通常簡稱為事務的ACID屬性，

原子性(Atomicity) ：事務是一個原子操作單元,其對資料的修改,要么全都執行,要么全都不執行，
一致性(Consistent) ：在事務開始和完成時,資料都必須保持一致狀態，這意味著所有相關的資料規
則都必須應用于事務的修改,以保持資料的完整性，
隔離性(Isolation) ：資料庫系統提供一定的隔離機制,保證事務在不受外部并發操作影響的“獨立”環境執行，這意味著事務處理程序中的中間狀態對外部是不可見的,反之亦然，
持久性(Durable) ：事務完成之后,它對于資料的修改是永久性的,即使出現系統故障也能夠保持，

并發事務處理帶來的問題

更新丟失(Lost Update)或臟寫
當兩個或多個事務選擇同一行資料時，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題–最后的更新覆寫了由其他事務所做的更新，
　　
臟讀（Dirty Reads）
　　一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的資料就處于不一致的狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”資料，并據此作進一步的
處理，就會產生未提交的資料依賴關系，這種現象被形象的叫做“臟讀”，
　　一句話：事務A讀取到了事務B已經修改但尚未提交的資料，還在這個資料基礎上做了操作，此時，如果B事務回滾，A讀取的資料無效，不符合一致性要求，
　　
不可重讀（Non-Repeatable Reads）
　　一個事務在讀取某些資料后的某個時間，再次讀取以前讀過的資料，卻發現其讀出的資料已經發生了改
變、或某些記錄已經被洗掉了！這種現象就叫做“不可重復讀”，
　　一句話：事務A內部的相同查詢陳述句在不同時刻讀出的結果不一致，不符合隔離性
　　
幻讀（Phantom Reads）
　　一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為“幻讀”，
　　一句話：事務A讀取到了事務B提交的新增資料，不符合隔離性
　　
事務隔離級別
“臟讀”、“不可重復讀”和“幻讀”,其實都是資料庫讀一致性問題,必須由資料庫提供一定的事務隔離機制來解決，
資料庫的事務隔離越嚴格,并發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度上“串行化”進行,這顯然與“并發”是矛盾的，同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如多應用對“不可重復讀"和“幻讀”并不敏感,可能更關心資料并發訪問的能力，
當前資料庫的事務隔離級別: show variables like ‘tx_isolation’;
設定事務隔離級別：set tx_isolation=‘REPEATABLE-READ’;
Mysql默認的事務隔離級別是可重復讀，用Spring開發程式時，如果不設定隔離級別默認用Mysql設定的隔離級別，如果Spring設定了就用已經設定的隔離級別

鎖詳解

鎖是計算機協調多個行程或執行緒并發訪問某一資源的機制，在資料庫中，除了傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，資料也是一種供需要用戶共享的資源，如何保證資料并發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發訪問性能的一個重要因素，

鎖分類

• 從性能上分為樂觀鎖(用版本對比來實作)和悲觀鎖
• 從對資料庫操作的型別分，分為讀鎖和寫鎖(都屬于悲觀鎖)
  讀鎖（共享鎖，S鎖(Shared)）：針對同一份資料，多個讀操作可以同時進行而不會互相影響
  寫鎖（排它鎖，X鎖(eXclusive)）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖
• 從對資料操作的粒度分，分為表鎖和行鎖

表鎖
每次操作鎖住整張表，開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低；一般用在整表資料遷移的場景，

基本操作

• 手動增加表鎖
  lock table 表名稱 read(write),表名稱2 read(write);
• 查看表上加過的鎖
  show open tables;
• 洗掉表鎖
  unlock tables;

分析

• 加讀鎖
  當前session和其他session都可以讀該表，當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待
• 加寫鎖
  當前session對該表的增刪改查都沒有問題，其他session對該表的所有操作被阻塞

結論
1、對MyISAM表的讀操作(加讀鎖) ,不會阻寒其他行程對同一表的讀請求,但會阻賽對同一表的寫請求，只有當讀鎖釋放后,才會執行其它行程的寫操作，
2、對MylSAM表的寫操作(加寫鎖) ,會阻塞其他行程對同一表的讀和寫操作,只有當寫鎖釋放后,才會執行其它行程的讀寫操作

行鎖
每次操作鎖住一行資料，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度最高，
InnoDB與MYISAM的最大不同有兩點：

• InnoDB支持事務（TRANSACTION）
• InnoDB支持行級鎖

場景
一個session開啟事務更新不提交，另一個session更新同一條記錄會阻塞，更新不同記錄不會阻塞

總結：

MyISAM在執行查詢陳述句SELECT前，會自動給涉及的所有表加讀鎖,在執行update、insert、delete操作會自動給涉及的表加寫鎖，

InnoDB在執行查詢陳述句SELECT時(非串行隔離級別)，不會加鎖，但是update、insert、delete操作會加行鎖，

簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀，而寫鎖則會把讀和寫都阻塞，

讀未提交：set tx_isolation=‘read-uncommitted’
B的事務還沒提交，A就可以查詢到B已經更新的資料，一旦B的事務因為某種原因回滾，所有的操作都將會被撤銷，那A查詢到的資料其實就是臟資料

讀已提交：set tx_isolation=‘read-committed’
A查詢所有記錄，在A的事務提交之前，打開B，更新表，B的事務還沒提交，A不能查詢到B已經更新的資料，解決了臟讀問題，B的事務提交，A執行與上一步相同的查詢，結果 與上一步不一致，即產生了不可重復讀的問題

可重復讀：set tx_isolation=‘repeatable-read’
A查詢所有記錄，在A的事務提交之前，打開B，更新表并提交，在A查詢表所有記錄，與上次查詢結果一致，沒有出現不可重復讀的問題，重新打開B，插入一條新資料后提交，在A查詢表所有記錄，沒有查出新增資料，所以感覺上是沒有出現幻讀，但在A執行update更新新增的資料是可以更新成功的，再次查詢就能查到B新增的資料了，出現了幻讀問題

串行化：set tx_isolation=‘serializable’
A查詢所有記錄，打開B，更新相同的記錄會被阻塞等待，更新不同的記錄可以成功，說明在串行模式下innodb的查詢也會被加上行鎖，
如果A執行的是一個范圍查詢，那么該范圍內的所有行包括每行記錄所在的間隙區間范圍都會被加鎖，此時如果B在該范圍內插入資料都會被阻塞，所以就避免了幻讀，
這種隔離級別并發性極低，開發中很少會用到，

間隙鎖(Gap Lock)
間隙鎖，鎖的就是兩個值之間的空隙，Mysql默認級別是可重復讀（repeatable-read），沒辦法解決幻讀問題，間隙鎖在某些情況下可以解決幻讀問題的，
范圍：范圍所包含的所有行記錄(包括間隙行記錄)以及行記錄所在的間隙都會被加鎖（包右不包左），
間隙鎖是在可重復讀隔離級別下才會生效，

臨鍵鎖(Next-key Locks)
Next-Key Locks是行鎖與間隙鎖的組合（不包含左邊），
　
無索引行鎖會升級為表鎖
鎖主要是加在索引上，如果對非索引欄位更新，行鎖會變表鎖

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖，并且該索引不能失效，否則都會從行鎖升級為表鎖，

鎖定某一行還可以用lock in share mode(共享鎖) 和for update(排它鎖)，例如：select * from test_innodb_lock where a = 2 for update; 這樣其他session只能讀這行資料，修改則會被阻塞，直到鎖定行的session提交

結論
Innodb存盤引擎由于實作了行級鎖定，雖然在鎖定機制的實作方面所帶來的性能損耗可能比表級鎖定會要更高一下，但是在整體并發處理能力方面要遠遠優于MYISAM的表級鎖定的，當系統并發量高的時候，Innodb的整體性能和MYISAM相比就會有比較明顯的優勢了，
但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現可能會更差，

行鎖分析
通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況

1 show status like 'innodb_row_lock%';

對各個狀態量的說明如下：
Innodb_row_lock_current_waits: 當前正在等待鎖定的數量
Innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度
Innodb_row_lock_time_avg: 每次等待所花平均時間
Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花時間
Innodb_row_lock_waits:系統啟動后到現在總共等待的次數
對于這5個狀態變數，比較重要的主要是：
Innodb_row_lock_time_avg （等待平均時長）
Innodb_row_lock_waits （等待總次數）
Innodb_row_lock_time（等待總時長）
尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什么會有如此多的等待，
然后根據分析結果著手制定優化計劃，

查看INFORMATION_SCHEMA系統庫鎖相關資料表

1 ‐‐ 查看事務
2 select * from INFORMATION_SCHEMA.INNODB_TRX;
3 ‐‐ 查看鎖
4 select * from INFORMATION_SCHEMA.INNODB_LOCKS;
5 ‐‐ 查看鎖等待
6 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
7
8 ‐‐ 釋放鎖，trx_mysql_thread_id可以從INNODB_TRX表里查看到
9 kill trx_mysql_thread_id
10
11 ‐‐ 查看鎖等待詳細資訊
12 show engine innodb status\G;

死鎖

set tx_isolation=‘repeatable-read’;
Session_1執行：select * from table where id=1 for update;
Session_2執行：select * from table where id=2 for update;
Session_1執行：select * from table where id=2 for update;
Session_2執行：select * from table where id=1 for update;
查看近期死鎖日志資訊：show engine innodb status\G;
大多數情況mysql可以自動檢測死鎖并回滾產生死鎖的那個事務，但是有些情況mysql沒法自動檢測死鎖

鎖優化建議

• 盡可能讓所有資料檢索都通過索引來完成，避免無索引行鎖升級為表鎖
• 合理設計索引，盡量縮小鎖的范圍
• 盡可能減少檢索條件范圍，避免間隙鎖
• 盡量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql盡量放在事務最后執行
• 盡可能低級別事務隔離