並發編程（四）：也談談資料庫的鎖機制

首先聲明，本次文章基本上都是從其他人的文章中或者論壇的回複中整理而來。我把我認為的關鍵點提取出來供自己學習。所有的引用都附在文後，在這裡也就不一一表謝了。

第二個聲明，我對於Internel DB並沒有研究過，所使用的也是簡單的寫寫SQL，截止到現在最多的一個經驗也就是SQL的效能調優，具體點就是通過Postgresql的執行計畫，來調整最佳化SQL陳述式完成在特定情境下的資料庫調優。對於鎖，由於資料庫支援的鎖機制已經能夠滿足平時的開發需要。因為所從事的行業並不是互連網，沒有即時性高並發的應用情境，因此也沒有速到過資料庫的複雜問題；對於線上應用的死結問題，那更是沒有研究過了。本文算是自己學習資料庫鎖機制的一個讀書筆記。再次感謝各位同仁的分享。

    鎖機製為什麼是資料庫非常重要的內容，那麼看一下資料庫並發的問題你就知道為什麼了：

1. 資料庫並發的問題

資料庫帶來的並發問題包括：   

 　　1. 丟失更新。

 　　2. 未確認的相關性（髒讀）。

 　　3. 不一致的分析（非重複讀）。

        4. 幻像讀。

詳細描述如下：

1.1.丟失更新

當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題。每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所做的更新，這將導致資料丟失。 　　

e.g.事務A和事務B同時修改某行的值，

這時資料的值為2，事務A所做的更新將會丟失。

看下面一段sql：

select old_attributes  from table where primary_key = ? ---step1attributes = merge(old_attributes,new_attributes)       ----step2update table set attributes_column = attributes where primary_key = ?   ----step3 

但是這樣的話，存在一個丟失更新的問題，兩個線程ThreadA 和 ThreadB 同時運行到了step1得到相同的old_attributes,

然後同時做step2,最後ThreadA先做step3,而ThreadB後做step3,這樣ThreadB就把ThreadA的屬性更新給丟失了！

如何解決呢？基本兩種思路，一種是悲觀鎖，另外一種是樂觀鎖； 簡單的說就是一種假定這樣的問題是高機率的，最好一開始就鎖住，免得更新老是失敗；另外一種假定這樣的問題是小機率的，最後一步做更新的時候再鎖住，免得鎖住時間太長影響其他人做有關操作。

1.1.1 悲觀鎖

　　a）傳統的悲觀鎖法（不推薦）：

　　以上面的例子來說明，在彈出修改工資的頁面初始化時（這種情況下一般會去從資料庫查詢出來），在這個初始化查詢中使用select ……for update nowait， 通過添加for update nowait語句，將這條記錄鎖住，避免其他使用者更新，從而保證後續的更新是在正確的狀態下更新的。然後在保持這個連結的狀態下，在做更新提交。當然這個有個前提就是要保持連結，就是要對連結要佔用較長時間，這個在現在web系統高並發高頻率下顯然是不現實的。

　　b）現在的悲觀鎖法（推薦優先使用）：

　　在修改工資這個頁面做提交時先查詢下，當然這個查詢必須也要加鎖（select ……for update nowait），有人會說，在這裡做個查詢確認記錄是否有改變不就行了嗎，是的，是要做個確認，只是你不加for update就不能保證你在查詢到更新提交這段時間裡這條記錄沒有被其他會話更新過，所以這種方式也需要在查詢時鎖定記錄，保證在這條記錄沒有變化的基礎上再做更新，若有變化則提示告知使用者。

1.1.2. 樂觀鎖

　　a）舊值條件（前鏡像）法：

　　就是在sql更新時使用舊的狀態值做條件，SQL大致如下 Update table set col1 = newcol1value， col2 = newcol2value…。 where col1 = oldcol1value and col2 = oldcol2value…。，在上面的例子中我們就可以把當前工資作為條件進行更新，如果這條記錄已經被其他會話更新過，則本次更新了0行，這裡我們應用系統一般會做個提示告知使用者重新查詢更新。這個取哪些舊值作為條件更新視具體系統實際情況而定。（這種方式有可能發生阻塞，如果應用其他地方使用悲觀鎖法長時間鎖定了這條記錄，則本次會話就需要等待，所以使用這種方式時最好統一使用樂觀鎖法。）

　　b）使用版本列法（推薦優先使用）：

　　其實這種方式是一個特殊化的前鏡像法，就是不需要使用多箇舊值做條件，只需要在表上加一個版本列，這一列可以是NUMBER或 DATE/TIMESTAMP列，加這列的作用就是用來記錄這條資料的版本（在表設計時一般我們都會給每個表增加一些NUMBER型和DATE型的冗餘欄位，以便擴充使用，這些冗餘欄位完全可以作為版本列用），在應用程式中我們每次操作對版本列做維護即可。在更新時我們把上次版本作為條件進行更新。在對一行進行更新的時候 限制條件=主鍵+版本號碼，同時對記錄的版本號碼進行更新。 

　　虛擬碼如下：

start transaction;select attributes, old_version from table where primary_key = ?attribute Merge operationsupdate table set version = old_verison + 1 , attributes_column = attributes_value where primary_key = ? and version = old_versioncommit; 

事務提交以後，看最後一步更新操作的記錄更新數是否為1，如果不是，則在業務上提示重試。（表明此時更新操作的並發度較高。）

在使用者並發數比較少且衝突比較嚴重的應用系統中選擇悲觀鎖b方法，其他情況首先樂觀鎖版本列法。

SQL Server中指定鎖：

SELECT * FROM table WITH (HOLDLOCK) ----其他事務可以讀取表，但不能更新刪除SELECT * FROM table WITH (TABLOCKX) -----其他事務不能讀取表,更新和刪除

不同的資料庫鎖的類型有差別，具體需要查詢各自的api doc。

1.2.未確認的相關性（髒讀 DirtyRead）

　　當一個事務讀取另一個事務尚未提交的修改時，產生髒讀。e.g.

        1.Mary的原工資為1000, 財務人員將Mary的工資改為了8000(但未提交事務)         2.Mary讀取自己的工資 ,發現自己的工資變為了8000，歡天喜地！

     3.而財務發現操作有誤，復原了事務,Mary的工資又變為了1000

      像這樣,Mary記取的工資數8000是一個髒資料。

 解決辦法：如果在第一個事務提交前，任何其他事務不可讀取其修改過的值，則可以避免該問題。

1.3.不一致的分析（不可重複讀取 non-repeatable read）

　同一查詢在同一事務中多次進行，由於其他提交事務所做的修改或刪除，每次返回不同的結果集，此時發生非重複讀。e.g.

解決辦法：如果只有在修改事務完全提交之後才可以讀取資料，則可以避免該問題。

  1.4.幻像讀 phantom read　　 　

 　同一查詢在同一事務中多次進行，由於其他提交事務所做的插入操作，每次返回不同的結果集，此時發生幻像讀。當對某行執行插入或刪除操作，而該行屬於某個事務正在讀取的行的範圍時，會發生幻像讀問題。事務第一次讀的行範圍顯示出其中一行已不複存在於第二次讀或後續讀中，因為該行已被其它事務刪除。同樣，由於其它事務的插入操作，事務的第二次或後續讀顯示有一行已不存在於原始讀中。

 　　e.g.目前工資為1000的員工有10人。

解決辦法：如果在操作事務完成資料處理之前，任何其他事務都不可以添加新資料，則可避免該問題

討論加鎖機制，還不要瞭解一下資料庫的隔離機制。

2. 資料庫隔離機制

     談到資料庫隔離機制，就不得不先說事務transaction。資料庫事務有嚴格的定義，它必須同時滿足4個特性：原子性（Atomic）、一致性（Consistency）、隔離性（Isolation）和持久性（Durabiliy），簡稱為ACID。

原子性：保證事務中的所有操作全部執行或全部不執行。例如執行轉賬事務，要麼轉賬成功，要麼失敗。成功，則金額從轉出帳戶轉入到目的帳戶，並且兩個帳戶金額將發生相應的變化；失敗，則兩個賬戶的金額都不變。不會出現轉出帳戶扣了錢，而目的帳戶沒有收到錢的情況。
一致性：保證資料庫始終保持資料的一致性——事務操作之前是一致的，事務操作之後也是一致的，不管事務成功與否。如上面的例子，轉賬之前和之後資料庫都保持資料上的一致性。
隔 離性：多個事務並發執行的話，結果應該與多個事務串列執行效果是一樣的。在並發資料操作時，不同的事務擁有各自的資料空間，其操作不會對對方產生幹擾。隔離允許事務行為獨立或隔離於其他並發啟動並執行事務。通過控制隔離，每個事務在其行動時間裡都像是修改資料庫的惟一事務。一個事務與其他事務隔離的程度稱為隔離等級。資料庫規定了多種交易隔離等級，不同隔離等級對應不同的幹擾程度，隔離等級越高，資料一致性就越好，但並發性越弱。
持久性：持久性表示事物操作完成之後，對資料庫的影響是持久的，即使資料庫因故障而受到破壞，資料庫也應該能夠恢複。通常的實現方式是採用日誌。

 ANSI/ISO SQL92標準定義了一些資料庫操作的隔離等級。每種隔離等級指定當前事務執行時所不允許的互動作用類型，即事務間是否相互隔離，或它們是否可以讀取或更新被另一事務所使用的資訊。較高隔離等級包括由較低層級所施加的限制。

定義的4種隔離等級：

• Read Uncommited
  

  可以讀取未提交記錄。此隔離等級，不會使用，忽略。
  

• Read Committed (RC)
  

  快照讀忽略，本文不考慮。
  

  針對當前讀，RC隔離等級保證對讀取到的記錄加鎖 (記錄鎖)，存在幻讀現象。
  

• Repeatable Read (RR)
  

  快照讀忽略，本文不考慮。
  

  針對當前讀，RR隔離等級保證對讀取到的記錄加鎖 (記錄鎖)，同時保證對讀取的範圍加鎖，新的滿足查詢條件的記錄不能夠插入 (間隙鎖)。
  

• Serializable
  

  從MVCC並發控制退化為基於鎖的並發控制。不區別快照讀與當前讀，所有的讀操作均為當前讀，讀加讀鎖 (S鎖)，寫加寫鎖 (X鎖)。
  

  Serializable隔離等級下，讀寫衝突，因此並發度急劇下降，因此不建議使用。

不同的隔離等級對應的將會導致的資料庫並發的問題總結如下：

隔離等級	髒讀	不可重複讀取	幻讀
讀未提交RU	Yes	Yes	Yes
讀已提交RC	No	Yes	Yes
可重複讀RR	No	No	Yes
序列化	No	No	No

因此，對於不同的隔離等級，需要在事務中主動加鎖，以避免這些並發的問題。

3. 資料庫的鎖機制

     各種大型資料庫所採用的鎖的基本理論是一致的，但在具體實現上各有差別。

     SQL Server更強調由系統來管理鎖。在使用者有SQL請求時，系統分析請求，自動在滿足鎖定條件和系統效能之間為資料庫加上適當的鎖，同時系統在運行期間常常自動進行最佳化處理，實行動態加鎖。

    SQLite採用粗放型的鎖。當一個串連要寫資料庫，所有其它的串連被鎖住，直到寫串連結束了它的事務。SQLite有一個加鎖表，來協助不同的寫資料庫都能夠在最後一刻再加鎖，以保證最大的並發性。

     MySQL資料庫由於其自身架構的特點，存在多種資料存放區引擎，每種儲存引擎所針對的應用情境特點都不太一樣，為了滿足各自特定應用情境的需求，每種儲存引擎的鎖定機制都是為各自所面對的特定情境而最佳化設計，所以各儲存引擎的鎖定機制也有較大區別。
總的來說，MySQL各儲存引擎使用了三種類型（層級）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。

     對於一般的使用者而言，通過系統的自動鎖定管理機制基本可以滿足使用要求。 但是涉及到寫操作，還是一定要理解隔離機制和並發可能帶來的問題，在事務中或者SQL中加入鎖機制。對於資料庫的死結，一般資料庫系統都會有一套機制去解鎖，一般不會造成資料庫的癱瘓，但解鎖的過程會造成資料庫效能的急速下降，反映到程式上就會造成程式的反應效能的下降，並且會造成程式有的操作失敗。   

     在實際開發中，要充分考慮所有可能的並發可能，既不能加作用的鎖，又要保證資料處理的正確性。因此，深刻理解鎖有非常重要的現實意義。

3.1 快照讀VS當前讀

       多版本的並發控制協議——MVCC (Multi-Version Concurrency Control) 最大的好處，相信也是耳熟能詳：讀不加鎖，讀寫不衝突。在讀多寫少的OLTP應用中，讀寫不衝突是非常重要的，極大的增加了系統的並發效能，這也是為什麼現階段幾乎所有的RDBMS都支援了MVCC。

       與MVCC相對的，是基於鎖的並發控制，Lock-Based Concurrency Control。

       在MVCC並發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是曆史版本)，不用加鎖。當前讀，讀取的是記錄的最新版本，並且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再並發修改這條記錄。

     在一個支援MVCC並發控制的系統中，哪些讀操作是快照讀？哪些操作又是當前讀呢？以MySQL InnoDB為例：

• 快照讀：簡單的select操作，屬於快照讀，不加鎖。(當然，也有例外，下面會分析)
  
  • select * from table where ?;
    
• 當前讀：特殊的讀操作，插入/更新/刪除操作，屬於當前讀，需要加鎖。
  
  • select * from table where ? lock in share mode;
    
  • select * from table where ? for update;
    
  • insert into table values (…);
    
  • update table set ? where ?;
    
  • delete from table where ?;
    

    所有以上的語句，都屬於當前讀，讀取記錄的最新版本。並且，讀取之後，還需要保證其他並發事務不能修改目前記錄，對讀取  記錄加鎖。其中，除了第一條語句，對讀取記錄加S鎖 (共用鎖定)外，其他的操作，都加的是X鎖 (排它鎖)。註：這個語句的加鎖是資料庫完成的。

3.2 當前讀的加鎖

       為什麼將 插入/更新/刪除 操作，都歸為當前讀？可以看看下面這個 更新 操作，在資料庫中的執行流程：

                  

        ，可以看到，一個Update操作的具體流程。當Update SQL被發給MySQL後，MySQL Server會根據where條件，讀取第一條滿足條件的記錄，然後InnoDB引擎會將第一條記錄返回，並加鎖 (current read)。待MySQL Server收到這條加鎖的記錄之後，會再發起一個Update請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有滿足條件的記錄為止。因此，Update操作內部，就包含了一個當前讀。同理，Delete操作也一樣。Insert操作會稍微有些不同，簡單來說，就是Insert操作可能會觸發Unique Key的衝突檢查，也會進行一個當前讀。

注：根據的互動，針對一條當前讀的SQL語句，InnoDB與MySQL Server的互動，是一條一條進行的，因此，加鎖也是一條一條進行的。先對一條滿足條件的記錄加鎖，返回給MySQL Server，做一些DML操作；然後在讀取下一條加鎖，直至讀取完畢。

       傳統RDBMS加鎖的一個原則，就是2PL (二階段鎖)：Two-Phase Locking。相對而言，2PL比較容易理解，說的是鎖操作分為兩個階段：加鎖階段與解鎖階段，並且保證加鎖階段與解鎖階段不相交。下面，仍舊以MySQL為例，來簡單看看2PL在MySQL中的實現。

 

從可以看出，2PL就是將加鎖/解鎖分為兩個完全不相交的階段。加鎖階段：只加鎖，不放鎖。解鎖階段：只放鎖，不加鎖。

    如果想要學習更多的資料庫內部加鎖機制，那麼請移步何登成大牛的部落格：http://hedengcheng.com/?p=771

 

參考資料：

1. http://blog.csdn.net/mshust2006/article/details/1333361

2. http://www.poluoluo.com/jzxy/201011/98761.html

3. http://bbs.csdn.net/topics/60365964

4. http://blog.csdn.net/d8111/article/details/2595635

5. http://www.cnblogs.com/dongqingswt/archive/2013/03/28/2987367.html

6. http://database.ctocio.com.cn/426/12256926.shtml、

7. http://blog.sina.com.cn/s/blog_5b16e2790100dc75.html

8. http://blog.sina.com.cn/s/blog_616b428f010163bo.html

9. http://hedengcheng.com/?p=771#_Toc374698319