mysql之鎖與事務

一灰灰發表於2018-03-24
MySql

180323_LOCK1.jpg

相關博文推薦：

Mysql之鎖與事務

平時的業務中，頂多也就是寫寫簡單的sql，連事務都用的少，對鎖這一塊的瞭解就更加欠缺了，之前一個大神分享了下mysql的事務隔離級別，感覺挺有意思的，正好發現一個很棒的博文，然後也收集了一些相關知識，正好來學習下，mysql中鎖與事務的神祕面紗，主要內容包括

  1. 共享鎖和排它鎖的區別以及適合範圍
  2. mysql的表鎖和行鎖的區別
  3. 怎麼判斷一個sql是否執行了鎖，執行的是表鎖還是行鎖
  4. 事務是什麼，怎麼用
  5. 事務的特性ACID
  6. 事務的隔離級別 (RU, RC, RR, SER)
  7. 如何檢視mysql使用的隔離級別

I. 鎖

在學習多執行緒時，我們也經常會遇到鎖這個東西，那個時候談的比較多的是樂觀鎖和悲觀鎖，那這兩種鎖和DB中常說的共享鎖和獨佔鎖有什麼區別呢？先給出我們已知的樂觀鎖和悲觀鎖定義

  • 樂觀鎖：多執行緒中的CAS就是一種樂觀鎖，實際上不加鎖，先嚐試去執行，如果失敗則重試（或者根據失敗策略進行處理）
  • 悲觀鎖：上鎖，一次只能有一個執行緒訪問，其他的都只能等待

1. 共享鎖和排它鎖

a. 共享鎖

突出在共享這個關鍵詞上，顧名思義，表示這個鎖可以多人共享，一般又可以稱為讀鎖(S鎖)

在DB中，讀鎖表示所有的讀取資料的小夥伴都不會被鎖阻塞，可以放心大膽的獲取資料，專業一點的說法就是同一時刻，允許多個連線併發的讀取同一資源

b. 排它鎖

排它，表示當某個人持有這個鎖之後，其他的人再來競爭鎖就會失敗，只能等待鎖釋放， 又稱為寫鎖(X鎖)

在DB中，寫鎖表示同一時刻，只能有一個小夥伴操作，其他的不管是讀還是寫，都得排隊，專業說法是寫鎖會阻塞其他的讀鎖或寫鎖請求，確保同一時刻只能有一個連線可以寫入資源，並防止其他連線讀取或者寫資源

c. gapLock 和 next key lock

  • next key lock 主要是範圍匹配的場景下，會鎖某一個範圍區間
  • gapLock 主要用來鎖邊界

如下面的case（說明，columnA是非唯一索引，RR隔離級別）

  • where columnA between 10 and 30, next key lock 確保不會在10, 30 之內插入新的資料行
  • where columnA = 10, gap lock 確保不會再次插入一個columnA=10的行

2. 表鎖和行鎖

對於DB的操作，通常會出現兩種情況，一個是鎖表，一個鎖行

  • 表鎖：表示整個表被某一個連線佔用了寫鎖，導致其他連線的讀鎖或者寫鎖都會阻塞；影響整個表的讀寫
  • 行鎖：表示表中某些行被某個連線佔用了寫鎖，但是其他行，依然可以被其他的連線請求讀鎖、寫鎖；僅影響被鎖的那些行資料

那麼一個問題就來了，什麼sql會導致行鎖，什麼會導致寫鎖？甚至我們如何判斷一個sql是否會請求鎖，請求的是讀鎖還是寫鎖呢？

3. 如何使用鎖

上面一節丟擲了問題，那麼現在就是來看下如何使用和分析鎖了，首先我們是我們最常見的幾個sql

  • select
  • update
  • delete
  • insert

其中很容易得出的結論是 update, delete, insert 三個涉及到寫鎖；而且這種操作絕大部分的場景是操作具體的某些行（想想為什麼?），所以更常見的是行鎖

select讀操作則有點特殊

a. select分析

MVCC(multiple-version-concurrency-control）是個行級鎖的變種，它在普通讀情況下避免了加鎖操作，因此開銷更低。即下面這個沒有讀鎖也沒有寫鎖

快照讀，不加鎖

select * from table ...
複製程式碼

當前讀，select 語句可以指定讀鎖和寫鎖，如下

-- 讀鎖
select * from table lock in share mode;

-- 寫鎖
select * from table for update;
複製程式碼

說明，insert, update, delete 也是當前讀，理由如下：

1.update和delete操作流程分解：

  • 首先通過where條件查詢到第一個滿足的記錄，並加鎖
  • 對這條記錄進行更新，再讀取下一條記錄
  • 對記錄更新，繼續讀下一條直到完畢

2.insert操作流程分解：

  • unique key 衝突檢測，會有一個當前讀
  • 無衝突時，插入

b. sql例項分析

--- SQL1：
select * from t1 where id = 10;

--- SQL2：
delete from t1 where id = 10;
複製程式碼

在分析上面的sql之前，需要明確幾個前提：

  • id是否為主鍵（id是否有索引）
  • 系統的隔離級別（隔離級別是什麼東西可以先看下下文介紹）

分別說明:

case1: 主鍵+RC級別

  • sql1不加鎖，MySQL是使用多版本併發控制的，讀不加鎖
  • sql2加寫鎖（即X鎖），只鎖 id=10這一行

180323_LOCK2.jpg

case2: 唯一索引+rc級別

  • sql2加寫鎖，如下圖的case，就兩把鎖，一個對應於id unique索引上的id = 10的記錄，另一把鎖對應於聚簇索引上的[name=’d’,id=10]的記錄

180323_LOCK3.jpg

case3: id非唯一索引+RC

  • sql2加寫鎖，如下圖的case，會有四個寫鎖

180323_LOCK4.jpg

case4: 無索引+RC

  • sql2分析：若id列上沒有索引，SQL會走聚簇索引的全掃描進行過濾，由於過濾是由MySQL Server層面進行的。因此每條記錄，無論是否滿足條件，都會被加上寫鎖(X鎖)。
  • 但是，為了效率考量，MySQL做了優化，對於不滿足條件的記錄，會在判斷後放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省

180323_LOCK5.jpg

case5: 主鍵+RR

加鎖同case1

case6: 唯一索引+RR

加鎖同case2

case7: 非唯一索引+RR

RR級別不允許出現幻讀，簡單來說，在加鎖的過程中，不允許在新增or修改滿足條件的記錄

即下圖中，除了圖三中類似的x鎖之外，還會新增一個gap鎖，這個gap鎖主要確保那幾個位置上不能插入新的記錄

180323_LOCK6.jpg

case8: 無索引+RR

  • 在Repeatable Read隔離級別下，如果進行全表掃描的當前讀，那麼會鎖上表中的所有記錄，同時會鎖上聚簇索引內的所有GAP，杜絕所有的併發 更新/刪除/插入 操作

180323_LOCK7.jpg

case9: Serializable級別

  • sql2: Serializable隔離級別。對於SQL2：delete from t1 where id = 10; 來說，Serializable隔離級別與Repeatable Read隔離級別完全一致
  • SQL1: 在RC，RR隔離級別下，都是快照讀，不加鎖。但是在Serializable隔離級別，SQL1會加讀鎖，也就是說快照讀不復存在，MVCC併發控制降級為Lock-Based CC

II. 事務

事務可謂是db中非常重要的一個知識點了，接下來我們的目標就是弄懂什麼是事務，怎麼使用事務，以及事務與鎖之間的關聯是怎樣的

說明：本文的分析主要是以mysql的innordb儲存引擎為標準

1. 定義

事務就是一組原子性的sql，或者說一個獨立的工作單元。

事務就是說，要麼mysql引擎會全部執行這一組sql語句，要麼全部都不執行（比如其中一條語句失敗的話）。

2. ACID特性

a. A:atomiciy 原子性

一個事務必須保證其中的操作要麼全部執行，要麼全部回滾，不可能存在只執行了一部分這種情況出現。

b. C:consistency一致性

資料必須保證從一種一致性的狀態轉換為另一種一致性狀態。

c. I:isolation 隔離性

在一個事務未執行完畢時，通常會保證其他Session 無法看到這個事務的執行結果

d. D:durability 永續性

事務一旦commit，則資料就會儲存下來，即使提交完之後系統崩潰，資料也不會丟失

3. 隔離級別

前面在分析鎖的sql時，就提到了隔離級別，通常有四種： RU, RC, RR, Serializable

在說明這個之前，先了解幾個概念

a. 基本概念

  • 髒讀：讀取到一個事務未提交的資料，因為這個事務最終無法保證一定執行成功，那麼讀取到的資料就無法保證一定準確
  • 不可重複讀：簡單來說就是在一個事務中讀取的資料可能產生變化，同樣的sql，在一個事務中執行多次，可能得到不同的結果
  • 幻讀：會話T1事務中執行一次查詢，然後會話T2新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢的條件。然後T1又使用相同 的查詢再次對錶進行檢索，但是此時卻看到了事務T2剛才插入的新行
  • 加鎖讀：select * from table ... 的執行是否加了讀鎖 (這個可以參考上面的sql加鎖分析）

b. RU: Read Uncommited 未提交讀

事務中的修改，即使沒有提交，對其他會話也是可見的，即表示可能出現髒讀，一般資料庫都不採用這種方案

c. RC: Read Commited 提交讀

這個隔離級別保證了一個事務如果沒有完全成功（commit執行完），事務中的操作對其他會話是不可見的，避免了髒讀的可能

但是可能出現不可重複度的情況，舉例說明：

  • 會話T1, 執行查詢 select * from where id=1，第一次返回一個結果
  • 會話T2, 執行修改 update table set updated=xxx where id=1 並提交
  • 會話T1，再次執行查詢 select * from where id=1，這次返回的結果中update欄位就和前面的不一樣了

實際的生產環境中，這個級別用的比較多，特意查了下公司的db隔離級別就是這個

一個RC級別的演示過程：

  • 會話1，開啟事務，查詢
  • 會話2，開啟事務，更新DB，提交事務
  • 會話1，再次查詢，提交事務
  • 從下面的實際演示結果可以知道，會話1，同一個sql，兩次執行的結果不同

180323_LOCK8.gif

相關的sql程式碼如下:

-- 設定會話隔離級別
set session transaction ioslation read commited;

-- 檢視當前會話隔離級別
select @@tx_isolation;

-- 會話1的操作
start transaction;
select * from newuser where userId=1;


-- 會話2開始操作
start transaction;
select * from newuser where userId=1;
update newuser set updated=1521786092 where userId=1;
select * from newuser where userId=1;
commit;


-- 再次進入會話1，同樣執行上次的sql，對比兩次輸出結果
select * from newuser where userId=1;

-- 注意觀察，會話1，前後兩次這個sql的輸出結果，特別是updated欄位
-- 正常情況會如上面的demo圖，會發生改變


-- 關閉會話
commit;

-- 再次查詢
select * from newuser where userId=1;
複製程式碼

d. RR: Repeatable Read 可重複度

一個事務中多次執行統一讀SQL,返回結果一樣。 這個隔離級別解決了髒讀的問題，幻讀問題

例項演示解決髒讀的過程(將上面的過程同樣來一次）

  • 發現不管會話1同一個sql，返回的結果都是相同的

180323_LOCK9.gif

e. Serializable 可序列化

最強的隔離級別，通過給事務中每次讀取的行加鎖，寫加寫鎖，保證不產生幻讀問題，但是會導致大量超時以及鎖爭用問題。

f. 常用命令

  • 檢視當前會話隔離級別: select @@tx_isolation
  • 檢視系統當前隔離級別: select @@global.tx_isolation
  • 設定當前會話隔離級別: set session transaction isolation level read committed;
  • 設定系統當前隔離級別: set global transaction isolation level read committed;
  • 命令列，
    • 開始事務: start transactioin;
    • 提交: commit;

4. 使用姿勢

前面演示事務隔離級別的時候，給出的例項就演示了事務的使用姿勢，一般作為三步驟：

  • 開始事務 start transaction;
  • 執行你的業務sql
  • 提交事務 commit;

我們現在演示以下一個事務中，讀鎖、寫鎖對另一個事務的影響

a. 讀鎖的影響

我們採用mysql預設的RR級別進行測試，userId為主鍵

-- 會話1
start transaction;
select * from newuser where userId=1 lock in share mode;

-- 轉入會話2
start transaction;
select * from newuser where userId=1; -- 會輸出
select * from newuser where userId=1 lock in share mode; -- 會輸出
update newuser set updated=1521787137 where userId=1; -- 會掛起


-- 轉入會話1
-- 提交, 此時觀察會話2的寫是否完成
commit;

-- 轉入會話2
commit;
複製程式碼

實際執行演示:

180323_LOCK10.gif

b. 寫鎖的影響

-- 會話1
start transaction;
select * from newuser where userId=1 for update;

-- 轉入會話2
start transaction;
select * from newuser where userId=1; -- 會輸出
select * from newuser where userId=1 lock in share mode; -- 會掛住

-- update newuser set updated=1521787137 where userId=1; -- 會掛住

-- 轉入會話1
-- 提交, 此時觀察會話2的寫是否完成
commit;

-- 轉入會話2
commit;
複製程式碼

實際執行演示:

180323_LOCK11.gif

c. 小結

  • 讀鎖，會阻塞其他請求寫鎖的sql執行
  • 寫鎖，會阻塞其他讀鎖和寫鎖的sql執行
  • 事務只有在提交之後，才會釋放鎖
  • 額外注意，上面事務在提交之後才會釋放鎖，因此如果兩個事務迴圈依賴鎖時，可能發生死鎖

III. 小結

鎖和事務可謂是db中非常重要的知識點了，在我們實際的編碼過程中（一般針對mysql, innordb儲存引擎，rr隔離級別），做出下面的一些總結

1. sql分析

  • select * from table where xxx; （讀快照，一般不加鎖）
  • select * from table where xxx lock in share mode; (讀鎖，會阻塞其他的寫鎖請求，但其他的讀鎖請求沒有影響）
  • select * from table where xxx for update; (寫鎖，會阻塞其他的讀寫請求）
  • update tableName set xxx (寫鎖）
  • insert （寫鎖）
  • delete （寫鎖）

2. 事務

簡單來講，事務就是一組sql，要麼全部執行成功，要麼全部失敗

四個特性： A(原子性)C(一致性)I(隔離性)D (永續性)

四種隔離級別：(mysql 預設採用的是RR級別)

隔離級別 髒讀 不可重複讀 幻讀 加鎖讀
read uncommited 可能 可能 可能 無
read commited 不可能 可能 可能 無
repeatable read 不可能 不可能 不可能 無
serializable 不可能 不可能 不可能 有

使用姿勢：

start transaction;

-- xxx 具體的sql

commit;
複製程式碼

IV. 其他

參考

個人部落格： 一灰灰Blog

基於hexo + github pages搭建的個人部落格，記錄所有學習和工作中的博文，歡迎大家前去逛逛

宣告

盡信書則不如，已上內容，純屬一家之言，因本人能力一般，見識有限，如發現bug或者有更好的建議，隨時歡迎批評指正

掃描關注

QrCode

相關文章