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

一、交易隔離機制的選擇

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

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

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

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

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

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

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以及內容

看隔離等級：

##首先開啟一個事務，給id=7的資料加上排它鎖定

在用另一個客戶端開啟事務

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

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

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

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

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

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

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

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

我們給name欄位加上索引

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

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

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

四、串列化隔離等級測試

（所有的交易都使用排它鎖定或共用鎖定，不需要使用者手動加鎖）

設定串列化隔離等級

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

#現在讓事務2插入資料

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

rollback一下

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

交易2 update

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

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

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

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

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

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

#有索引，則使用行鎖；沒有索引，則使用表鎖。 ###

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

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