首頁  >  文章  >  資料庫  >  MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

PHPz
PHPz轉載
2023-06-03 10:49:071254瀏覽

    一、交易隔離機制的選擇

    • #如果我們完全不管,使用未提交讀取的事務隔離機制,任由這些執行緒並發操作資料庫,那就會出現髒讀(讀取了未commit的資料)、不可重複讀取(兩次查詢值不同)、幻讀(兩次查詢資料量不同)等問題,資料的安全性最低,優點是並發效率非常高,一般不會使用

    • 如果我們序列化(靠鎖定實現),透過鎖定給所有的事務都排序,雖然資料的安全性提高了,並發的效率就太低了,一般也不會使用

    • 所以我們一般用的是已提交讀取、可重複讀這兩個隔離級別,平衡了資料的安全性,一致性以及並發的效率,是由MVCC多版本並發控制實現的(MVCC是已提交讀取和可重複讀取的原理,鎖定是串行化的原理)

    二、表格級鎖定&行級鎖定

    #表級鎖:整張表加鎖。開銷小(因為不用去找表的某一行的記錄進行加鎖,要修改這張表,直接申請加這張表的鎖),加鎖快,不會出現死鎖;鎖粒度大,發生鎖衝突的機率高,並發度低

    行級鎖定:對某行記錄加鎖。開銷大(需要找到表中對應的記錄,有搜表搜尋引的過程),加鎖慢,會出現死鎖;鎖定粒度最小,發生鎖衝突的機率最低,並發度高

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    InnoDB儲存引擎支援交易處理,表支援行級鎖定,並發能力更好

    1. InnoDB行鎖是透過給索引上的索引項目加鎖來實現的,而不是給表的行記錄加鎖實現的,這就意味者只有通過索引條件檢索數據,InnoDB才使用行級鎖,否則InnoDB將使用表鎖

    2. 由於InnoDB的行鎖實作是針對索引欄位新增的鎖,不是針對行記錄加的鎖,因此雖然存取的是InnoDB引擎下表的不同行,但如果使用相同的索引字段作為過濾條件,仍然會發生鎖定衝突,只能串行進行,不能並發進行

    3. 即使SQL中使用了索引,但是經過MySQL的優化器後,如果認為全表掃描比使用索引效率高,此時會放棄使用索引,因此也不會
      使用行鎖,而是使用表鎖,例如對一些很小的表,MySQL就不會去使用索引

    三、排它鎖定(Exclusive)和共用鎖定(Shared)

    • 排它鎖,又稱為X鎖,寫鎖

    • 共享鎖,又稱為S鎖,讀鎖

    讀讀(SS)之間是可以相容的,但是讀寫(SX、SX)之間,寫寫(XX)之間是互斥的

    對事務加X和S鎖之間有以下的關係:

    • 一個事務對資料物件A加了S 鎖,可以對A進行讀取操作但不能進行update操作,加鎖期間其它事務能對A加S鎖但不能加X 鎖

    • 一個事務對資料物件A加了X 鎖,就可以對A進行讀取和更新,加鎖期間其它事務不能對A加任何鎖

    顯示加鎖:select … lock in share mode強制取得共享鎖,select … for update取得排它鎖

    1. 測試不同交易之間排它鎖和共享鎖的相容性

    我們先查看表格的SQL以及內容

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    看隔離等級:

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用##首先開啟一個事務,給id=7的資料加上排它鎖定

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用在用另一個客戶端開啟事務

    我們用另一個事務的服務執行緒為id=7的資料加上排它鎖,阻塞了

    我們嘗試給id=7的資料加上共享鎖,還是阻塞了

    總結:不同事務之間對於資料的鎖,只有SS鎖可以共存,XX、SX、XS都不能共存######2. 測試行鎖加在索引項目上############其實行鎖定是加在索引樹上的#######

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    用表格的無索引欄位作為過濾條件

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    #交易2現在同樣想取得這條記錄的排它鎖,可想而知地失敗了;那現在事務2取得chenwei的記錄的排它鎖,試試能不能成功

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    InnoDB是支援行鎖的,剛才以主鍵id為過濾條件時,事務1和事務2取得不同行的鎖定是可以成功的。然而現在我們發現取得name為chenwei的排它鎖也取得不到了,這是為什麼?讓我們解釋一下:

    InnoDB的行鎖是透過將索引項加鎖來實現的,而不是為表的行記錄加鎖實現的

    而我們用name作為篩選條件沒有用到索引,自然就不會使用行鎖,而是使用表鎖。這就意味著只有透過索引檢索數據,InnoDB才使用行級鎖,否則InnoDB都會使用表鎖!!!

    我們給name欄位加上索引

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    ##我們發現,加上索引後,兩個交易可以取得到不同行的排它鎖定(for update),再一次證明了InnoDB的行鎖定是加在索引項目上的

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    因為現在name走的是索引, 透過zhangsan在輔助索引樹上找到它所在行記錄的id是7,然後

    到主鍵索引樹上,取得對應行記錄的排他鎖(個人猜測應該是輔助索引樹和主鍵索引樹對應的記錄都加了鎖)

    四、串列化隔離等級測試

    (所有的交易都使用排它鎖定或共用鎖定,不需要使用者手動加鎖)

    設定串列化隔離等級

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    兩個交易可以同時取得共享鎖定(SS共存)

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    #現在讓事務2插入資料

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    ##此時由於insert需要加排它鎖,但由於事務1已經對整張表添加了共享鎖,事務2無法再對錶成功加鎖(SX不共存)

    rollback一下

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    因為我們為name加上了索引,以上的select相當於為name為zhangsan的資料加上了行共享鎖定

    交易2 update

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    事務2不能update,因為此時已經被事務1的共享鎖定鎖住了整個表

    事務2在輔助索引樹上找zhangsan ,找到對應的主鍵值,然後去主鍵索引樹找到對應的記錄,但是發現這行記錄已經被共享鎖鎖住了,事務2可以獲取共享鎖,但是不能獲取排他鎖

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    我們用主鍵索引id試試看能不能update

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    #依然阻塞住了,雖然我們where後面的欄位現在使用的id而不是name,但是name也是透過輔助索引樹找到對應的主鍵,再到主鍵索引樹上找對應的記錄,而主鍵索引樹上的記錄加了鎖

    (個人猜想應該是輔助索引樹和主鍵索引樹對應的資料都加了鎖)

    我們update id=8的數據,成功了。只為id為7的行資料加上了行鎖,因此我們可以成功操作id為8的資料

    MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用

    #有索引,則使用行鎖;沒有索引,則使用表鎖。 ###

    表級鎖還是行級鎖說的是鎖的粒度,共享鎖和排他鎖說的是鎖的性質,不管是表鎖還是行鎖,都有共享鎖和排他鎖的區分

    以上是MySQL表鎖、行鎖、排它鎖及共用鎖怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章!

    陳述:
    本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除