#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采用兩階段加鎖協議實現隔離性和一致性，我們只有深入的去理解這種協議，才能更好的對我們的SQL進行優化，增加系統的吞吐量。