MySQL并發時經典常見的死鎖原因及解決方法

分類:小喵學堂     發布時間:2018-08-21 17:28:56
分享到 :

MySQL并發時經典常見的死鎖原因及解決方法

  1. mysql都有什么鎖

    MySQL有三種鎖的級別:頁級、表級、行級。

      表級鎖:開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖沖突的概率最高,并發度最低。

      行級鎖:開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖沖突的概率最低,并發度也最高。
      頁面鎖:開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間,并發度一般


算法:

     next KeyLocks鎖,同時鎖住記錄(數據),并且鎖住記錄前面的Gap    

     Gap鎖,不鎖記錄,僅僅記錄前面的Gap

     Recordlock鎖(鎖數據,不鎖Gap)

     所以其實 Next-KeyLocks=Gap鎖+ Recordlock鎖


2.    什么情況下會造成死鎖

      所謂死鎖<DeadLock>: 是指兩個或兩個以上的進程在執行過程中,

      因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.

      此時稱系統處于死鎖狀態或系統產生了死鎖,這些永遠在互相等竺的進程稱為死鎖進程.

      表級鎖不會產生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB.


死鎖的關鍵在于:兩個(或以上)的Session加鎖的順序不一致。

那么對應的解決死鎖問題的關鍵就是:讓不同的session加鎖有次序


3.    一些常見的死鎖案例

案例一:

     需求:將投資的錢拆成幾份隨機分配給借款人。

     起初業務程序思路是這樣的:

     投資人投資后,將金額隨機分為幾份,然后隨機從借款人表里面選幾個,然后通過一條條select for update 去更新借款人表里面的余額等。

     抽象出來就是一個session通過for循環會有幾條如下的語句:

     Select * from xxx where id='隨機id' for update

     基本來說,程序開啟后不一會就死鎖。

     這可以是說最經典的死鎖情形了。

     例如兩個用戶同時投資,A用戶金額隨機分為2份,分給借款人1,2

     B用戶金額隨機分為2份,分給借款人2,1

     由于加鎖的順序不一樣,死鎖當然很快就出現了。

     對于這個問題的改進很簡單,直接把所有分配到的借款人直接一次鎖住就行了。

     Select * from xxx where id in (xx,xx,xx) for update

     在in里面的列表值mysql是會自動從小到大排序,加鎖也是一條條從小到大加的鎖

QQ圖片20180821170913.png

案例2:

      在開發中,經常會做這類的判斷需求:根據字段值查詢(有索引),如果不存在,則插入;否則更新。

image.png

    當對存在的行進行鎖的時候(主鍵),mysql就只有行鎖。

    當對未存在的行進行鎖的時候(即使條件為主鍵),mysql是會鎖住一段范圍(有gap鎖)


鎖住的范圍為:

    (無窮小或小于表中鎖住id的最大值,無窮大或大于表中鎖住id的最小值)

     如:如果表中目前有已有的id為(11 , 12)

     那么就鎖?。?2,無窮大)

     如果表中目前已有的id為(11 , 30)

     那么就鎖?。?1,30)

對于這種死鎖的解決辦法是:

    insert into t3(xx,xx) on duplicate key update `xx`='XX';

    用mysql特有的語法來解決此問題。因為insert語句對于主鍵來說,插入的行不管有沒有存在,都會只有行鎖。


案例3:

    直接上情景:

QQ圖片20180821172303.png

    這個跟案例一其它是差不多的情況,只是session1不按常理出牌了,

    Session2在等待Session1的id=9的鎖,session2又持了1到8的鎖(注意9到19的范圍并沒有被session2鎖?。?,最后,session1在插入新行時又得等待session2,故死鎖發生了。

    這種一般是在業務需求中基本不會出現,因為你鎖住了id=9,卻又想插入id=7的行,這就有點跳了,當然肯定也有解決的方法,那就是重理業務需求,避免這樣的寫法。