如何進(jìn)行MySQL INNODB存儲引擎中各類型SQL加鎖分析

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

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

I
NSERT:

當(dāng)前事務(wù)記為 TX1 
① 判斷是否主鍵或者唯一建沖突


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

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

PS : 其中 INSERT 語句會產(chǎn)生的鎖類型有  INSERT Itention LOCK(gap) , X record lock , S record lock(主鍵沖突會將 X 轉(zhuǎn)為 S的申請) , IX(意向?qū)戞i 表層面的，這里分析都會忽略掉) , 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 以及對應(yīng)的 uk 加上 X record lock

對①操作而言， pk=old pk=new uk=tb(pk) 共3個(gè) X record lock
對②操作而言， pk=old uk=new uk=tb(pk) 共3個(gè) X record lock
對③操作而言， pk=old uk=tb(pk)  共2個(gè) X record lock
對④操作而言， pk=old uk=tb(pk)  共2個(gè) 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 加一個(gè) gap lock , 開區(qū)間
2> nk 匹配到記錄
1. 主鍵沖突(分確定狀態(tài)和不確定狀態(tài)) ， nk 加 X record LOCK ， 對應(yīng)主鍵加 X record lock , 主鍵 S record lock ， 不對 唯一鍵加鎖
2. 無主鍵沖突 ， nk 加 X record LOCK ，pk=old pk=new 加 X record lock ,nk 兩側(cè)加 x gap lock , uk X record lock , uk S record lock , uk S gap lock
對②操作而言
1> nk 未匹配到記錄 ， nk 加一個(gè) gap lock ， 開區(qū)間
2> nk 匹配到記錄 
1. 唯一建沖突(分確定狀態(tài)和不確定狀態(tài)) ， nk 加 X record LOCK ， 對應(yīng)主鍵加 X record lock , 唯一鍵 S record lock
2. 無唯一建沖突 ， nk 加 X record lock ， 對應(yīng)主鍵加 X record lock , uk=new 和 uk=old 加 X record lock
對③操作而言，
1> nk 未匹配到記錄 ， nk 加一個(gè) gap lock ， 開區(qū)間
2> nk 匹配到記錄 ， nk加一個(gè) X record lock , nk 兩側(cè)加 gap lock 開區(qū)間， 對應(yīng)主鍵 加 X record lock
對④操作而言
1> nk未匹配到記錄 , nk 加一個(gè) gap lock ，開區(qū)間
2> nk 匹配到記錄 ,  nk加一個(gè) X record lock ， nk兩側(cè)加 gap lock 開區(qū)間 ，對應(yīng)主鍵 加 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 以及 所有g(shù)ap 加 X gap lock。

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