淺談SQL Server中的交易記錄(二)----交易記錄在修改資料時的角色

標籤：style   blog   http   io   ar   color   os   使用   sp   

    本篇文章是系列文章中的第二篇，以防你還沒有看過第一篇.上一篇的文章地址如下:

    淺談SQL Server中的交易記錄(一)----交易記錄的物理和邏輯構架

 

簡介

    每一個SQL Server的資料庫都會按照其修改資料(insert,update,delete)的順序將對應的日誌記錄到記錄檔.SQL Server使用了Write-Ahead logging技術來保證了交易記錄的原子性和持久性.而這項技術不僅僅保證了ACID中的原子性(A)和持久性(D),還大大減少了IO操作，把對資料的修改提交到磁碟的工作交給lazy-writer和checkpoint.本文主要講述了SQL Server修改資料時的過程以及相關的技術。

 

預寫式日誌（Write-Ahead Logging (WAL)）

    SQL Server使用了WAL來確保了事務的原子性和持久性.實際上，不光是SQL Server,基本上主流的關聯式資料庫包括oracle,mysql,db2都使用了WAL技術.

    WAL的核心思想是:在資料寫入到資料庫之前，先寫入到日誌.

    因為對於資料的每筆修改都記錄在日誌中，所以將對於資料的修改即時寫入到磁碟並沒有太大意義，即使當SQL Server發生意外崩潰時，在恢複(recovery)過程中那些不該寫入已經寫入到磁碟的資料會被復原(RollBack),而那些應該寫入磁碟卻沒有寫入的資料會被重做(Redo)。從而保證了持久性(Durability)

    但WAL不僅僅是保證了原子性和持久性。還會提高效能.

    硬碟是通過旋轉來讀取資料,通過WAL技術，每次提交的修改資料的事務並不會馬上反映到資料庫中，而是先記錄到日誌.在隨後的CheckPoint和lazy Writer中一併提交,如果沒有WAL技術則需要每次提交資料時寫入資料庫:

    

       而使用WAL合并寫入，會大大減少磁碟IO：

      

     也許你會有疑問，那每次對於修改的資料還是會寫入記錄檔.同樣消耗磁碟IO。上篇文章講過，每一筆寫入日誌的記錄都是按照先後順序，給定順序編號的LSN進行寫入的，日誌只會寫入到記錄檔的邏輯末端。而不像資料那樣，可能會寫到磁碟的各個地方.所以，寫入日誌的開銷會比寫入資料的開銷小很多。

   

SQL Server修改資料的步驟

     SQL Server對於資料的修改,會分為以下幾個步驟順序執行:

     1.在SQL Server的緩衝區的日誌中寫入”Begin Tran”記錄

     2.在SQL Server的緩衝區的日誌頁寫入要修改的資訊

     3.在SQL Server的緩衝區將要修改的資料寫入資料頁

     4.在SQL Server的緩衝區的日誌中寫入”Commit”記錄

     5.將緩衝區的日誌寫入記錄檔

     6.發送確認資訊(ACK)到用戶端(SMSS,ODBC等）

  

     可以看到,交易記錄並不是一步步寫入磁碟.而是首先寫入緩衝區後，一次性寫入日誌到磁碟.這樣既能在日誌寫入磁碟這塊減少IO，還能保證日誌LSN的順序.

     上面的步驟可以看出，即使事務已經到了Commit階段，也僅僅只是把緩衝區的日誌頁寫入日誌，並沒有把資料寫入資料庫.那將要修改的資料頁寫入資料庫是在何時發生的呢?

 

Lazy Writer和CheckPoint

    上面提到，SQL Server修改資料的步驟中並沒有包含將資料實際寫入到磁碟的過程.實際上，將緩衝區內的頁寫入到磁碟是通過兩個過程中的一個實現：

     這兩個過程分別為:

     1.CheckPoint

     2.Lazy Writer

    任何在緩衝區被修改的頁都會被標記為“髒”頁。將這個髒頁寫入到資料磁碟就是CheckPoint或者Lazy Writer的工作.

    當事務遇到Commit時，僅僅是將緩衝區的所有日誌頁寫入磁碟中的記錄檔:

    

 

     而直到Lazy Writer或CheckPoint時，才真正將緩衝區的資料頁寫入磁碟檔案:

    

   

    前面說過，記錄檔中的LSN號是可以比較的，如果LSN2>LSN1,則說明LSN2的發生時間晚於LSN1的發生時間。CheckPoint或Lazy Writer通過將記錄檔末尾的LSN號和緩衝區中資料檔案的LSN進行對比，只有緩衝區內LSN號小於記錄檔末尾的LSN號的資料才會被寫入到磁碟中的資料庫。因此確保了WAL（在資料寫入到資料庫之前，先寫入日誌)。

 

Lazy Writer和CheckPoint的區別

    Lazy Writer和CheckPoint往往容易混淆。因為Lazy Writer和CheckPoint都是將緩衝區內的“髒”頁寫入到磁碟檔案當中。但這也僅僅是他們唯一的相同點了。

    Lazy Writer存在的目的是對緩衝區進行管理。當緩衝區達到某一臨界值時，Lazy Writer會將緩衝區內的髒頁存入磁碟檔案中，而將未修改的頁釋放並回收資源。

    而CheckPoint存在的意義是減少伺服器的恢復(Recovery Time).CheckPoint就像他的名字指示的那樣，是一個存檔點.CheckPoint會定期發生.來將緩衝區內的“髒”頁寫入磁碟。但不像Lazy Writer,Checkpoint對SQL Server的記憶體管理毫沒有興趣。所以CheckPoint也就意味著在這個點之前的所有修改都已經儲存到了磁碟.這裡要注意的是：CheckPoint會將所有緩衝區的髒頁寫入磁碟，不管髒頁中的資料是否已經Commit。這意味著有可能已經寫入磁碟的“髒頁”會在之後復原（RollBack).不過不用擔心，如果資料復原，SQL Server會將緩衝區內的頁再次修改，並寫入磁碟。

    通過CheckPoint的運作機制可以看出，CheckPoint的間歇(Recovery Interval)長短有可能會對效能產生影響。這個CheckPoint的間歇是一個伺服器層級的參數。可以通過sp_config進行配置，也可以在SSMS中進行配置:

    

    恢複間歇的預設參數是0，意味著由SQL Server來管理這個回複間隔。而自己設定復原間隔也是需要根據具體情況來進行界定。更短的恢複間歇意味這更短的恢復和更多的磁碟IO，而更長的恢複間歇則帶來更少的磁碟IO佔用和更長的恢復.

    除了自動CheckPoint之外，CheckPoint還會發生在Alter DataBase以及關閉SQL Server伺服器時。sysadmin和db_backupoperator組的成員以及db_owner也可以使用CheckPoint指令來手動儲存CheckPoint:

    

    通過指定CheckPoint後的參數，SQL Server會按照這個時間來完成CheckPoint過程，如果時間指定的短，則SQL Server會使用更多的資源優先完成CheckPoint過程。

    通常情況下，將“髒”頁寫入磁碟的工作，Lazy Writer要做的比CheckPoint會多出許多。

 

淺談SQL Server中的交易記錄(二)----交易記錄在修改資料時的角色