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

這篇文章給大家介紹如何進行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加鎖分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。