詳解Mysql事務隔離級別與鎖機制
一.概述
我們的數據庫一般都會並發執行多個事務，多個事務可能會並發的對相同的一批數據進行增刪改查操作，可能 就會導致我們說的臟寫、 脹讀和不可重複讀、幻讀這些問題。 這些問題的本質都是數據庫的多事務並發問題，為了解決多事務並發問題，數據庫設計了事務隔離機制、鎖機制、MVCC多版本並發控制隔離機制，用一整套機制來解決多事務並發問題。接下來，我們會深入講解這些機制，讓大家徹底理解數據庫內部的執行原理。
二.事務及其ACID屬性
事務是由一組SQL語句組成的邏輯處理單元,事務具有以下4個屬性,通常簡稱為事務的ACID屬性。
1.原子性(Atomicity) ：事務是一個原子操作單元,其對數據的修改,要麼全都執行,要麼全都不執行。
2.一致性(Consistent)：在事務開始和完成時,數據都必須保持一致狀態。這意味着所有相關的數據規 則都必須應用於事務的修改,以保持數 據的完整性。
3.隔離性(Isolation) ：數據庫系統提供一定的隔離機制,保證事務在不受外部並發操作影響的「獨立」環境執行。這意味着事務處理過程中的中間狀態對外部是不可見的,反之亦然。
4.持久性(Durable)：事務完成之後,它對於數據的修改是永久性的,即使出現系統故障也能夠保持。
原子性和一致性有點類似, 原子性表現在操作層面,一致性表現在數據層面.
三.並發事務處理帶來的問題 **
- 更新丟失(Lost Update)或臟寫
當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題–最 後的更新覆蓋了由其他事務所做的更新。 - 臟讀（Dirty Reads）
一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的數據就處於不一致的狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「臟」數據，並據此作進一步的處理，就會產生未提交的數據依賴關係。這種現象被形象的叫做「臟讀」。 一句話：事務A讀取到了事務B已經修改但尚未提交的數據，還在這個數據基礎上做了操作。此時，如果B事務回滾，A讀取的數據無效，不符合一致性要求。
3.不可重讀（Non-Repeatable Reads）
一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做「不可重複讀」。 一句話：事務A內部的相同查詢語句在不同時刻讀出的結果不一致，不符合隔離性
4.幻讀（Phantom Reads）
一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為「幻讀」。 一句話：事務A讀取到了事務B提交的新增數據，不符合隔離性
「臟讀」、「不可重複讀」和「幻讀」,其實都是數據庫讀一致性問題,必須由數據庫提供一定的事務隔離機制 來解決。
四.事務隔離級別
1.數據庫的事務隔離越嚴格,並發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度 上「串行化」進行,這顯然與「並發」是矛盾的。
2.同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如許多應用對「不可重複讀”和「幻讀」並不 敏感,可能更關心數據並發訪問的能力。
3.查看當前數據庫的事務隔離級別: show variables like ‘tx_isolation’; 設置事務隔離級別：set tx_isolation=’REPEATABLE-READ’;
4.Mysql默認的事務隔離級別是可重複讀，用Spring開發程序時，如果不設置隔離級別默認用Mysql設置的隔 離級別，如果Spring設置了就用 已經設置的隔離級別
五.鎖詳解
1.概述
鎖是計算機協調多個進程或線程並發訪問某一資源的機制。 在數據庫中，除了傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供需要用戶共享的資源。如何保證數據並發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖衝突也是影響數據庫並發 訪問性能的一個重要因素。
2.鎖分類
2.1從性能上分為樂觀鎖(用版本對比來實現)和悲觀鎖
2.2從對數據庫操作的類型分，分為讀鎖和寫鎖(都屬於悲觀鎖)
讀鎖（共享鎖，S鎖(Shared)）：針對同一份數據，多個讀操作可以同時進行 而不會互相影響
寫鎖（排它鎖，X鎖(eXclusive)）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖
2.3從對數據操作的粒度分，分為表鎖和行鎖
3.表鎖
3.1概述
每次操作鎖住整張表。開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低； 一般用在整表數據遷移的 場景。但在實際工作中,表鎖用得少.
3.2 基本操作
–建表SQL
CREATE TABLE mylock (
id INT (11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
–插入數據
INSERT INTOtest.mylock (id, NAME) VALUES (‘1’, ‘a’);
INSERT INTOtest.mylock (id, NAME) VALUES (‘2’, ‘b’);
INSERT INTOtest.mylock (id, NAME) VALUES (‘3’, ‘c’);
INSERT INTOtest.mylock (id, NAME) VALUES (‘4’, ‘d’);
手動增加表鎖
lock table 表名稱 read(write),表名稱2 read(write);
查看錶上加過的鎖
show open tables;
刪除表鎖
unlock tables;
案例分析(加讀鎖）
當前session和其他session都可以讀該表
當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待
案例分析(加寫鎖）
當前session對該表的增刪改查都沒有問題，其他session對該表的所有操作被阻塞
案例結論
對MyISAM表的讀操作(加讀鎖) ,不會阻塞其他進程對同一表的讀請求,但會阻塞對同一表的寫請求。只有當讀鎖釋放後,才會執行其它進程的寫操作。
對MylSAM表的寫操作(加寫鎖) ,會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放後,才會執行其它進程的讀寫操作
4.行鎖
4.1概述:
每次操作鎖住一行數據。開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度最高。
4.2InnoDB與MYISAM的最大不同有兩點：
InnoDB支持事務（TRANSACTION）InnoDB支持行級鎖
4.3行鎖演示
一個session開啟事務更新不提交，另一個session更新同一條記錄會阻塞，更新不同記錄不會阻塞
4.4總結：
MyISAM在執行查詢語句SELECT前，會自動給涉及的所有表加讀鎖,在執行update、insert、delete操作會自動給涉及的表加寫鎖。
InnoDB在執行查詢語句SELECT時(非串行隔離級別)，不會加鎖。但是update、insert、delete操作會加行鎖。
簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把讀和寫都阻塞
4.5行鎖與事務隔離級別案例分析
4.5.1 先創建表
CREATE TABLE account (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
balance int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test.account (name, balance) VALUES (‘lilei’, ‘450’);
INSERT INTO test.account (name, balance) VALUES (‘hanmei’, ‘16000’);
INSERT INTO test.account (name, balance) VALUES (‘lucy’, ‘2400’);
4.5.2 讀未提交：
（1）打開一個客戶端A，並設置當前事務模式為read uncommitted（未提交讀），查詢表account的初始值：
set tx_isolation=’read-uncommitted’;
（2）在客戶端A的事務提交之前，打開另一（2）在客戶端A的事務提交之前，打開另一個客戶端B，更新表account：
（3）這時，雖然客戶端B的事務還沒提交，但是客戶端A就可以查詢到B已經更新的數據：
（4）一旦客戶端B的事務因為某種原因回滾，所有的操作都將會被撤銷，那客戶端A查詢到的數據其實就是臟數據：
（5）在客戶端A執行更新語句update account set balance = balance – 50 where id =1，lilei的balance沒有變成350，居然是400，是不是很奇怪，數據不一致啊，
如果你這麼想就太天真 了，在應用程序中，我們會用400-50=350，並不知道其他會話回滾了，要想解決這個問題可以採用讀已提交的隔離級別
4.5.3讀已提交
（1）打開一個客戶端A，並設置當前事務模式為read committed（未提交讀），查詢表account的所有記錄：
set tx_isolation=’read-committed’;
（2）在客戶端A的事務提交之前，打開另一個客戶端B，更新表account：
（3）這時，客戶端B的事務還沒提交，客戶端A不能查詢到B已經更新的數據，解決了臟讀問題：
（4）客戶端B的事務提交
（5）客戶端A執行與上一步相同的查詢，結果 與上一步不一致，即產生了不可重複讀的問題
4.5.4可重複讀
（1）打開一個客戶端A，並設置當前事務模式為repeatable read，查詢表account的所有記錄
set tx_isolation=’repeatable-read’;
2）在客戶端A的事務提交之前，打開另一個客戶端B，更新表account並提交
3）在客戶端A查詢表account的所有記錄，與步驟（1）查詢結果一致，沒有出現不可重複讀的問題
4）在客戶端A，接着執行update account set balance = balance – 50 where id = 1，balance沒有變成400-50=350，lilei的balance值用的是步驟2中的350來算的，
所以是300，數據的一致性倒是沒有被破壞。可重複讀的隔離級別下使用了MVCC(multi-version concurrency control)機制，select操作不會更新版本號，是快照讀（歷史版本）；
insert、update和delete會更新版本號，是當前讀（當前版本）。
（5）重新打開客戶端B，插入一條新數據後提交
(6）在客戶端A查詢表account的所有記錄，沒有查出新增數據，所以沒有出現幻讀
（7)驗證幻讀
在客戶端A執行update account set balance=888 where id = 4;能更新成功，再次查詢能查到客戶端B新增的數據
4.5.5、串行化
（1）打開一個客戶端A，並設置當前事務模式為serializable，查詢表account的初始值：set tx_isolation=’serializable’;
（2）打開一個客戶端B，並設置當前事務模式為serializable，更新相同的id為1的記錄會被阻塞等待，更新id為2的記錄可以成功，說明在串行模式下innodb的查詢也會被加上行鎖。
如果客戶端A執行的是一個範圍查詢，那麼該範圍內的所有行包括每行記錄所在的間隙區間範圍(就算該行數據還未被插入也會加鎖，這種是間隙鎖)都會被加鎖。此時如果客戶端B在該範圍內插入數據都會被阻塞，所以就避免了幻讀。
這種隔離級別並發性極低，開發中很少會用到。
5.間隙鎖(Gap Lock)
間隙鎖，鎖的就是兩個值之間的空隙。Mysql默認級別是repeatable-read，有辦法解決幻讀問題嗎？間隙鎖在某些情況下可以解決幻讀問題。
假設account表裡數據如下：
那麼間隙就有 id 為 (3,10)，(10,20)，(20,正無窮) 這三個區間，
在Session_1下面執行 update account set name = ‘zhuge’ where id > 8 and id <18;，則其他Session沒法在這個範圍所包含的所有行記錄(包括間隙行記錄)
以及行記錄所在的間隙里插入或修改任何數據，即id在(3,20]區間都無法修改數據，注意最後那個20也是包含在內的。
間隙鎖是在可重複讀隔離級別下才會生效。
6.臨鍵鎖(Next-key Locks)
Next-Key Locks是行鎖與間隙鎖的組合。像上面那個例子里的這個(3,20]的整個區間可以叫做臨鍵鎖。
7.注意點:
無索引行鎖會升級為表鎖(RR級別會升級為表鎖，RC級別不會升級為表鎖)
鎖主要是加在索引上，如果對非索引字段更新，行鎖可能會變表鎖
session1 執行：update account set balance = 800 where name = ‘lilei’;
session2 對該表任一行操作都會阻塞住
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的整體性能表現不僅不能比MYISAM高，甚至可能會更差。
七.鎖優化建議
儘可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖
合理設計索引，盡量縮小鎖的範圍
儘可能減少檢索條件範圍，避免間隙鎖
盡量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql盡量放在事務最後執行
儘可能低級別事務隔離
