如何進行MySQL INNODB存儲引擎中各類型SQL加鎖分析

142次閱讀

共計 2226 個字符，預計需要花費 6 分鐘才能閱讀完成。

這篇文章給大家介紹如何進行 MySQL INNODB 存儲引擎中各類型 SQL 加鎖分析，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

MYSQL5.6
事物隔離級別：RR
innodb_locks_unsafe_for_binlog=OFF

I
NSERT:

當前事務記為 TX1
① 判斷是否主鍵或者唯一建沖突

YES，則請求隱式鎖 - 顯式鎖 X record lock – S record lock
1 記錄已經存在于表中，返回 Duplicate 錯誤 (持有 S record lock 待事務(TX1)commit 或者 rollback 之后釋放)
2 記錄還在其他的事務(TX2) 中處于未提交狀態 (請求 S record lock)，此時稱之為主鍵或者唯一建沖突的不確定狀態(之后 update 也會出現這種情況)
1. 若事務(TX2) 提交，則 TX1 返回 Duplicate 錯誤 (持有 S record lock 待事務(TX1)commit 或者 rollback 之后釋放)
2. 若事務(TX2) 回滾，則 TX1 獲取 S record lock 成功，進而跟以下情況一致

NO，請求 Itention INSERT LOCK(gap)，其中 next_key lock ,gap lock , record lock(S X 都會) 會阻塞之
1 若未被阻塞，獲取到 Itention INSERT LOCK(gap)，進而獲取到 X record lock
2 若被阻塞，則等待

PS : 其中 INSERT 語句會產生的鎖類型有 INSERT Itention LOCK(gap) , X record lock , S record lock(主鍵沖突會將 X 轉為 S 的申請) , IX(意向寫鎖表層面的，這里分析都會忽略掉) , autoinc lock (自增鎖，表級，之后會有分析)

UPDATE：

PK—– 主鍵
UK—– 唯一建
NK—– 非唯一索引
OTHER– 無索引字段

1. 條件為 PK
① update tb set pk=new where pk=old;
② update tb set uk=new where pk=old;
③ update tb set nk=xxx where pk=old;
④ update tb set other=xxx where pk=old;

pk 加上 X record lock 以及對應的 uk 加上 X record lock

對①操作而言，pk=old pk=new uk=tb(pk) 共 3 個 X record lock
對②操作而言，pk=old uk=new uk=tb(pk) 共 3 個 X record lock
對③操作而言，pk=old uk=tb(pk) 共 2 個 X record lock
對④操作而言，pk=old uk=tb(pk) 共 2 個 X record lock

2. 條件為 UK
① update tb set pk=new where uk=old;
② update tb set uk=new where uk=old;
③ update tb set nk=new where uk=old;
④ update tb set other=new where uk=old;
同上

3. 條件為 NK
① update tb set pk=new where nk=old;
② update tb set uk=new where nk=old;
③ update tb set nk=new where nk=old;
④ update tb set other=new where nk=old;

對①操作而言
1 nk 未匹配到記錄，nk 加一個 gap lock , 開區間
2 nk 匹配到記錄
1. 主鍵沖突(分確定狀態和不確定狀態)，nk 加 X record LOCK，對應主鍵加 X record lock , 主鍵 S record lock，不對唯一鍵加鎖
2. 無主鍵沖突，nk 加 X record LOCK，pk=old pk=new 加 X record lock ,nk 兩側加 x gap lock , uk X record lock , uk S record lock , uk S gap lock
對②操作而言
1 nk 未匹配到記錄，nk 加一個 gap lock，開區間
2 nk 匹配到記錄
1. 唯一建沖突(分確定狀態和不確定狀態)，nk 加 X record LOCK，對應主鍵加 X record lock , 唯一鍵 S record lock
2. 無唯一建沖突，nk 加 X record lock，對應主鍵加 X record lock , uk=new 和 uk=old 加 X record lock
對③操作而言，
1 nk 未匹配到記錄，nk 加一個 gap lock，開區間
2 nk 匹配到記錄，nk 加一個 X record lock , nk 兩側加 gap lock 開區間，對應主鍵加 X record lock
對④操作而言
1 nk 未匹配到記錄 , nk 加一個 gap lock，開區間
2 nk 匹配到記錄 , nk 加一個 X record lock，nk 兩側加 gap lock 開區間，對應主鍵加 X record lock

對于相同 gap , X gap lock 直接是不沖突的

4. 條件為 OTHER
① update tb set pk=new where other=old;
② update tb set uk=new where other=old;
③ update tb set nk=new where other=old;
④ update tb set other=new where other=old;

所有主鍵記錄加 X record lock 以及所有 gap 加 X gap lock。

關于如何進行 MySQL INNODB 存儲引擎中各類型 SQL 加鎖分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

正文完