#MySql-兩階段加鎖協(xié)議 ##前言此篇博客主要是講述MySql(僅限innodb)的兩階段加鎖(2PL)協(xié)議,而非兩階段提交(2PC)協(xié)議,區(qū)別如下:

• 2PL,兩階段加鎖協(xié)議:主要用于單機事務(wù)中的一致性與隔離性。
• 2PC,兩階段提交協(xié)議:主要用于分布式事務(wù)。

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

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

引入2PL是為了保證事務(wù)的隔離性，即多個事務(wù)在并發(fā)的情況下等同于串行的執(zhí)行。 在數(shù)學(xué)上證明了如下的封鎖定理:

如果事務(wù)是良構(gòu)的且是兩階段的，那么任何一個合法的調(diào)度都是隔離的。

具體的數(shù)學(xué)推到過程可以參照<<事務(wù)處理:概念與技術(shù)>>這本書的7.5.8.2節(jié).

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

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

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

其余時間為加鎖階段。

如下圖所示:

這樣的話，在實際的數(shù)據(jù)庫中就很容易實現(xiàn)了。 ##兩階段加鎖對性能的影響

上面很好的解釋了兩階段加鎖，現(xiàn)在我們分析下其對性能的影響?？紤]下面兩種不同的扣減庫存的方案:

方案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;  

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

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

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

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

值得注意的是：

在更新到數(shù)據(jù)庫的那個時間點才算鎖成功

提交到數(shù)據(jù)庫的時候才算解鎖成功

這兩個round_trip的前半段是不會計算在內(nèi)的

如下圖所示:

當(dāng)前只考慮網(wǎng)絡(luò)時延，不考慮數(shù)據(jù)庫和應(yīng)用本身的時間消耗。 ##依據(jù)S2PL的性能優(yōu)化

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

放到事務(wù)***，這樣可以顯著的提高吞吐量。更進(jìn)一步:

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

筆者認(rèn)為，先后順序如下圖:

###避免死鎖這也是任何SQL加鎖不可避免的。上文提到了按照記錄Key的熱度在事務(wù)中倒序排列。那么寫代碼的時候任何可能并發(fā)的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在執(zhí)行過程中對符合謂詞條件的記錄加的是和select for update一致的排它鎖

(具體的鎖類型較為復(fù)雜，不在這里描述),所以兩者效果一樣。

#總結(jié) MySql采用兩階段加鎖協(xié)議實現(xiàn)隔離性和一致性，我們只有深入的去理解這種協(xié)議，才能更好的對我們的SQL進(jìn)行優(yōu)化，增加系統(tǒng)的吞吐量。