MySQL的兩階段加鎖協議是什么

MySQL的兩階段加鎖協議是什么，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

MySql(僅限innodb)的兩階段加鎖(2PL)協議,而非兩階段提交(2PC)協議,區別如下:

• 2PL,兩階段加鎖協議:主要用于單機事務中的一致性與隔離性。

• 2PC,兩階段提交協議:主要用于分布式事務。

MySql本身針對性能，還有一個MVCC(多版本控制)控制,本文不考慮此種技術，僅僅考慮MySql本身的加鎖協議。  ##什么時候會加鎖在對記錄更新操作或者(select for update、lock in share  model)時，會對記錄加鎖(有共享鎖、排它鎖、意向鎖、gap鎖、nextkey鎖等等),本文為了簡單考慮，不考慮鎖的種類。  ##什么是兩階段加鎖在一個事務里面，分為加鎖(lock)階段和解鎖(unlock)階段,也即所有的lock操作都在unlock操作之前,如下圖所示:

##為什么需要兩階段加鎖

引入2PL是為了保證事務的隔離性，即多個事務在并發的情況下等同于串行的執行。 在數學上證明了如下的封鎖定理:

如果事務是良構的且是兩階段的，那么任何一個合法的調度都是隔離的。

具體的數學推到過程可以參照<<事務處理:概念與技術>>這本書的7.5.8.2節.

此書乃是關于數據庫事務的圣經，無需解釋(中文翻譯雖然晦澀，也能堅持讀下去,強烈推薦)

##工程實踐中的兩階段加鎖-S2PL  在實際情況下，SQL是千變萬化、條數不定的,數據庫很難在事務中判定什么是加鎖階段，什么是解鎖階段。于是引入了S2PL(Strict-2PL),即:

在事務中只有提交(commit)或者回滾(rollback)時才是解鎖階段，

其余時間為加鎖階段。

如下圖所示:

這樣的話，在實際的數據庫中就很容易實現了。 ##兩階段加鎖對性能的影響

上面很好的解釋了兩階段加鎖，現在我們分析下其對性能的影響。考慮下面兩種不同的扣減庫存的方案:

方案1:

begin; // 扣減庫存 update t_inventory set count=count-5 where id=${id} and count >= 5; // 鎖住用戶賬戶表 select * from t_user_account where user_id=123 for update; // 插入訂單記錄 insert into t_trans; commit;

方案2:

begin;  // 鎖住用戶賬戶表  select * from t_user_account where user_id=123 for update;  // 插入訂單記錄  insert into t_trans;  // 扣減庫存  update t_inventory set count=count-5 where id=${id} and count >= 5;  commit;

由于在同一個事務之內，這幾條對數據庫的操作應該是等價的。但在兩階段加鎖下的性能確是有比較大的差距。兩者方案的時序如下圖所示:

由于庫存往往是最重要的熱點，是整個系統的瓶頸。那么如果采用第二種方案的話,

tps應該理論上能夠提升3rt/rt=3倍。這還僅僅是業務就只有三條SQL的情況下，

多一條sql就多一次rt,就多一倍的時間。

值得注意的是：

在更新到數據庫的那個時間點才算鎖成功

提交到數據庫的時候才算解鎖成功

這兩個round_trip的前半段是不會計算在內的

如下圖所示:

當前只考慮網絡時延，不考慮數據庫和應用本身的時間消耗。 ##依據S2PL的性能優化

從上面的例子中,可以看出，需要把最熱點的記錄，

放到事務***，這樣可以顯著的提高吞吐量。更進一步:

越熱點記錄離事務的終點越近(無論是commit還是rollback)

筆者認為，先后順序如下圖:

###避免死鎖這也是任何SQL加鎖不可避免的。上文提到了按照記錄Key的熱度在事務中倒序排列。那么寫代碼的時候任何可能并發的SQL都必須按照這種順序來處理，不然會造成死鎖。如下圖所示:

###select for update和update where  謂詞計算我們可以直接將一些簡單的判斷邏輯寫到update的謂詞里面，以減少加鎖時間，考慮下面兩種方案:

方案1:

begin:  int count = select count from t_inventory for update;  if count >= 5:     update t_inventory set count=count-5 where id =123     commit   else     rollback

方案2:

begin:     int rows = update t_inventory set count=count-5 where id =123 and count >=5     if rows > 0:         commit;     ele          rollback;

時延如下圖所示:

可以看到，通過在update中加謂詞計算，少了1rt的時間。

由于update在執行過程中對符合謂詞條件的記錄加的是和select for update一致的排它鎖

看完上述內容，你們掌握MySQL的兩階段加鎖協議是什么的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！