MySQL的表級鎖,行級鎖,排它鎖和共享鎖

Posted on by

前言

如果我們和面試官聊到事務的問題，怎麼回答呢？

先說下事務是什麼，因為我們業務是比較復雜的，不可能一個sql就能解決的，涉及多個sql就組成一個事務。事務就是一組sql共同執行，要麼完全成功，要麼完全失敗，不能出現部分成功或者部分失敗的情況。一個事務有ACID特性（可以參考：事務的ACID特性和MySQL事務的隔離級別）：

  • 原子性：要麼全部成功，要麼全部失敗，這樣才能保證事務的一致性；
  • 一致性：比如銀行的轉賬，扣除一個人的錢肯定要給另一個人加錢，不能光扣除不加，這樣業務就存在問題，數據的一致性就破壞瞭；
  • 持久性：當我們數據commit以後，數據是先寫到緩存當中，緩存中的數據還是要慢慢花時間往磁盤上寫，如果此時停電瞭、宕機或者重啟瞭，我們有redo log重做日志來保證數據庫的持久性；
  • 隔離性：這塊可以說下事務為什麼要有隔離性，因為事務要允許並發執行，一個業務涉及瞭很多事務，而我們後臺往往有很多業務，要能夠讓他們並發執行，如果所有的事務都是串行執行的話，那這樣我們寫多線程程序隻有一個線程來做事情，這樣效率很低。所以事務要並發執行，但是並發執行涉及瞭一些問題：事務的安全性&一致性和並發的效率問題，我們以這兩個東西為參考點，才得到瞭MySQL不同等級的並發/隔離，如果事務並發執行時我們完全不隔離的話，就可能會出現臟讀（事務B讀到瞭事務A還未提交的數據然後，然後用事務A未提交的數據去做計算，得到瞭很多其他的結果，然後事務A又把那個數據rollback掉，那麼事務B計算出來的都是有問題的數據，臟讀一定會出現問題）、不可重復讀（以同樣的條件去一個數據，然後再次去查詢的時候發現數據的值有所改變，當然不可重復讀也不一定會有問題，有些業務場景下是允許的，這和業務上數據的安全性和一致性是否嚴格有關）和幻讀（在事務中按照同樣的條件前後兩次查詢的結果數據量不同）這些問題。

那麼我們為瞭解決事務並發執行遇到的問題就給出瞭事務的隔離級別：

  • 串行化，串行化完全用鎖來實現，通過鎖給所有事務排序，按順序執行，這樣做數據的安全性高但並發的效率很低，一般我們不會這樣做的。
  • 未提交讀，對於我們寫的多線程程序來說，對於臨界區代碼段沒有做任何的並發控制，雖然並發性高但數據安全性很低，未提交讀還允許臟讀的存在，這是有問題的所以絕對不會使用未提交讀。串行化和未提交讀在實際項目中是不會用到的，一般數據庫引擎默認工作在已提交讀和可重復讀，這兩個隔離級別就結合瞭數據的安全性&一致性和數據的並發效率，這兩個是由MVCC多版本並發控制機制實現的。
  • 已提交讀，oracle默認工作級別。不允許讀取未commit的數據，這個級別仍然允許不可重復讀和虛讀產生。
  • 可重復讀，MySQL默認工作級別。保證事務再次讀取是依然得到相同的數據，部分解決瞭虛讀，但虛讀是仍然會出現的

註意：

  • 事務隔離級別越高，為避免沖突所花費的性能也就越多，即效率低。
  • 在“可重復讀”級別，實際上可以解決部分的虛讀問題，但是不能防止update更新產生的虛讀問題，要禁止虛讀產生，還是需要設置串行化隔離級別。

事務隔離級別的實現原理：鎖+MVCC。串行化底層實現原理是鎖，鎖有共享鎖、排它鎖、意向共享鎖、意向排它鎖、間隙鎖和死鎖，InnoDB的已提交讀和可重復讀的底層實現原理：MVCC（多版本並發控制），MVCC提供瞭一種並發讀取方式，包括快照讀（同一份數據會有多個版本）、當前讀、undo log和redo log。MVCC是已提交讀和可重復讀的原理，鎖是串行化的原理

ACD特性用事務日志實現，I 特性用共享鎖、排它鎖、MVCC 實現。事務日志分為undo log（回滾日志） 和 redo log（重做日志）

一、表級鎖&行級鎖

  • 表級鎖：對整張表加鎖。開銷小（因為不用去找表的某一行的記錄進行加鎖，要修改這張表，直接申請加這張表的鎖），加鎖快，不會出
  • 現死鎖；鎖粒度大，發生鎖沖突的概率高，並發度低
  • 行級鎖：對某行記錄加鎖。開銷大（需要找到表中相應的記錄，有搜表搜索引的過程），加鎖慢，會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，並發度高

MyISAM存儲引擎隻支持表級鎖，InnoDB支持事務處理，支持行級鎖，並發能力更好

二、排它鎖&共享鎖

  • 排它鎖：又稱為X鎖，寫鎖
  • 共享鎖：又稱為S鎖，讀鎖

讀讀（SS）之間是可以兼容的，但是讀寫（SX、SX）之間，寫寫（XX）之間是互斥的

1. 測試不同事務之間排它鎖和共享鎖的兼容性

我們先查看表SQL及內容

查看隔離級別：

首先開啟一個事務A，給id=7的數據加上排它鎖：

在另一個客戶端開啟事務B：

給id=7不管加排它鎖和共享鎖都阻塞瞭並沒有查詢出來，因為A事務給id=7這一行的數據加瞭排它鎖，就是寫鎖，其他人不能讀也不能寫。

總結：不同事務之間對於數據的鎖，隻有SS鎖可以共存，XX、SX、XS都不能共存

2. 測試行鎖加在索引項上

其實行鎖是加在索引樹上的。

每次做完測試都把剛做的rollback。

用表的無索引字段作為過濾條件

那現在事務2獲取不同行chenwei的記錄

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

InnoDB的行鎖是通過給索引項加鎖來實現的，而不是給表的行記錄加鎖實現的

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

我們給name字段加上索引：

然後再做剛才的操作：

我們發現，給name加上索引後，兩個事務可以獲取到不同行的排它鎖（for update），再一次證明瞭InnoDB的行鎖是加在索引項上的。

因為現在name走的是索引， 通過zhangsan在輔助索引樹上找到它所在行記錄的id是7，然後到主鍵索引樹上，獲取對應行記錄的排他鎖（個人猜測應該是輔助索引樹和主鍵索引樹相應的記錄都加瞭鎖）

三、串行化隔離級別測試

串行化所有事務用的都是共享鎖或者排它鎖，不需用手動添加。select獲取的是共享鎖，insert、delete和update獲取的都是排它鎖。

設置串行化隔離級別：

兩個事務可以同時獲取共享鎖（SS共存：

現在讓事務2插入數據;

由於Insert需要加排它鎖，但是由於事務1已經對整張表加瞭共享鎖，事務2無法再對表成功加鎖（sx不共存）

rollback一下，把所有獲取鎖的狀態都回退掉:

開啟兩個事務:

因為我們給name加上瞭索引，以上的select相當於給name為zhangsan的數據加上瞭行共享鎖

事務2update;

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

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

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

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

我們update id=8的數據，成功瞭。因為我們select的時候，隻是給id=7的數據加上瞭行鎖，我們操作id=8的數據當然可以成功

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

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

串行化玩的就是排它鎖和共享鎖，在可重復讀級別下，不手動加鎖的話，用的就是MVCC機制，實際上並沒有用到鎖，我們也可以手動加鎖。InnoDB如果不創建索引的話，用的是表鎖，如果查詢的時候用到瞭索引項，它用的就是行鎖瞭，行鎖是給索引加鎖，而不是單純給一行數據加鎖。

到此這篇關於MySQL的表級鎖,行級鎖,排它鎖和共享鎖的文章就介紹到這瞭,更多相關MySQL鎖內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：