MySQL表鎖、行鎖、排它鎖及共用鎖的使用詳解

前言

事務隔離級別的實現原理：簡單來說就是各種鎖機制和MVCC多版本並行控制

我們學習知識的時候，需要了解知識點出現的原因，什麼情況下能用到這個知識

我們說到事務，就得說到事務的ACID特性，說到隔離性的時候，事務要能夠允許並行執行，並行執行為了同時保證資料的安全性，一致性和並行的效率，就需要設定事務的隔離級別

一、事務隔離機制的選擇

• 如果我們完全不管，使用未提交讀的事務隔離機制，任由這些執行緒並行運算元據庫，那就會出現髒讀（讀取了未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欄位加上索引

我們發現，給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的資料，成功了。因為我們select的時候，只是給id=7的資料加上了行鎖，我們操作id=8的資料當然可以成功

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

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

總結

到此這篇關於MySQL表鎖、行鎖、排它鎖及共用鎖使用的文章就介紹到這了,更多相關MySQL表鎖、行鎖、排它鎖和共用鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！