mysql鎖機制使用總結

標籤：

1.隔離等級

(1)讀不提交(Read Uncommited，RU)

這種隔離等級下，事務間完全不隔離，會產生髒讀，可以讀取未提交的記錄，實際情況下不會使用。

(2)讀提交(Read commited，RC)

僅能讀取到已提交的記錄，這種隔離等級下，會存在幻讀現象，所謂幻讀是指在同一個事務中，多次執行同一個查詢，返回的記錄不完全相同的現象。幻讀產生的根本原因是，在RC隔離等級下，每條語句都會讀取已提交事務的更新，若兩次查詢之間有其他事務提交，則會導致兩次查詢結果不一致。雖然如此，讀提交隔離等級在生產環境中使用很廣泛。

(3)可重複讀(Repeatable Read, RR)

可重複讀隔離等級解決了不可重複讀取的問題，但依然沒有解決幻讀的問題。那麼不可重複讀取與幻讀有什麼區別呢？不可重複讀取重點在修改，即讀取過的資料，兩次讀的值不一樣；而幻讀則側重於記錄數目變化【插入和刪除】。一般教科書上告訴我們只有到序列化隔離等級才解決幻讀問題，但mysql的innodb比較特殊，RR即解決了幻讀問題，主要通過GAP鎖實現。另外，不是所有的資料庫都實現了該隔離等級，後面會簡單介紹下mysql是如何?可重複讀隔離等級的。

(4)序列化(Serializable)

在序列化隔離模式下，消除了髒讀，幻象，但事務並發度急劇下降，事務的隔離等級與事務的並發度成反比，隔離等級越高，事務的並發度越低。實際生產環境下，dba會在並發和滿足業務需求之間作權衡，選擇合適的隔離等級。

2.並發調度方式

與隔離等級緊密聯絡的另外一個東西是並發調度，通過並發調度實現隔離等級。對於並發調度，不同的資料庫廠商有不同的實現機制，但基本原理類似，都是通過加鎖來保護資料對象不同時被多個事務修改。多版本的並發控制(MVCC)相對於傳統的基於鎖的並發控制主要特點是讀不上鎖，這種特性對於讀多寫少的情境，大大提高了系統的並發度，因此大部分關係型資料庫都實現了MVCC。

3.兩階段鎖協議

兩階段鎖協議的含義是，事務分為兩個階段，第一個階段是獲得封鎖，第二個階段是釋放封鎖。兩階段封鎖保證並發調度的正確性。兩階段封鎖相對於一階段封鎖（一次性獲得事務需要的所有鎖），提高了並發度，但同時也帶來了死結的可能。

4.死結

所謂死結是指兩個或多個事務，各自佔有對方的期望獲得的資源，形成的迴圈等待，彼此無法繼續執行的一種狀態。

5.鎖類型

根據鎖的類型分，可以分為共用鎖定，獨佔鎖定，意圖共用鎖和意向獨佔鎖定。根據鎖的粒度分，又可以分為行鎖，表鎖。對於mysql而言，事務機制更多是靠底層的儲存引擎來實現，因此，mysql層面只有表鎖，而支援事務的innodb儲存引擎則實現了行鎖(記錄鎖)，gap鎖，next-key鎖。Mysql的記錄鎖實質是索引記錄的鎖，因為innodb是索引組織表；gap鎖是索引記錄間隙的鎖，這種鎖只在RR隔離等級下有效；next-key鎖是記錄鎖加上記錄之前gap鎖的組合。mysql通過gap鎖和next-key鎖實現RR隔離等級。

說明：

1. 對於更新操作(讀不上鎖)，只有走索引才可能上行鎖；否則會對聚簇索引的每一行上寫鎖，實際等同於對錶上寫鎖。
2. 若多個物理記錄對應同一個索引，若同時訪問，也會出現鎖衝突；
3. 當表有多個索引時，不同事務可以用不同的索引鎖住不同的行，另外innodb會同時用行鎖對資料記錄(聚簇索引)加鎖。
4. MVCC並發控制機制下，任何操作都不會阻塞讀操作，讀操作也不會阻塞任何操作，只因為讀不上鎖。

6. 鎖互斥與相容關係矩陣   

	X   (獨佔鎖定)	S   (共用鎖定)	IX (意向獨佔鎖定)	IS(意圖共用鎖)
X	n	n	n	n
S	n	y	n	y
IX	n	n	y	y
IS	n	y	y	y

 (y表示相容，n表示不相容):

7.mysql鎖相關操作

(1).查看最近一次死結資訊

show innodb engine status;

查看LATEST DETECTED DEADLOCK這一欄的相關資訊。

(2)查看正在使用的鎖

SELECT r.trx_id             waiting_trx_id, 
       r.trx_query           waiting_query, 
       b.trx_id                blocking_trx_id, 
       b.trx_query           blocking_query, 
       b.trx_mysql_thread_id blocking_thread, 
       b.trx_started, 
       b.trx_wait_started 
FROM   information_schema.innodb_lock_waits w 
       INNER JOIN information_schema.innodb_trx b 
               ON b.trx_id = w.blocking_trx_id 
       INNER JOIN information_schema.innodb_trx r 
               ON r.trx_id = w.requesting_trx_id 

(3)查看事務的隔離等級

show variables like ‘tx_isolation‘;

8.典型SQL語句鎖分析

(1).select ......lock in share mode 獲得共用鎖定。【對於表上意圖共用鎖；對於讀取的每一個行，上行級共用鎖定】
(2).select ...... for update 獲得獨佔鎖定【對於表上意向獨佔鎖定；對於讀取的每一個行，會上行級獨佔鎖定】
(3).insert into target_tab select * from source_tab where ...
(4).create table new_tab as select ... From source_tab where ...
(3)和(4)在RR隔離等級下，會對source_tab上鎖，防止出現幻讀；RC隔離等級下，不上鎖。
(5).FLUSH TABLES WITH READ LOCK
全域讀鎖定，鎖定資料庫中的所有庫中的所有表，mysqldump會用到這個命令。

mysql鎖機制使用總結