淺談SQL Server中的事務(wù)日志(二)----事務(wù)日志在修改數(shù)據(jù)時(shí)的角色

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

    淺談SQL Server中的事務(wù)日志(一)----事務(wù)日志的物理和邏輯構(gòu)架

 

簡(jiǎn)介

    每一個(gè)SQL Server的數(shù)據(jù)庫(kù)都會(huì)按照其修改數(shù)據(jù)(insert,update,delete)的順序?qū)?duì)應(yīng)的日志記錄到日志文件.SQL Server使用了Write-Ahead logging技術(shù)來(lái)保證了事務(wù)日志的原子性和持久性.而這項(xiàng)技術(shù)不僅僅保證了ACID中的原子性(A)和持久性(D),還大大減少了IO操作，把對(duì)數(shù)據(jù)的修改提交到磁盤的工作交給lazy-writer和checkpoint.本文主要講述了SQL Server修改數(shù)據(jù)時(shí)的過(guò)程以及相關(guān)的技術(shù)。

 

預(yù)寫式日志（Write-Ahead Logging (WAL)）

    SQL Server使用了WAL來(lái)確保了事務(wù)的原子性和持久性.實(shí)際上，不光是SQL Server,基本上主流的關(guān)系數(shù)據(jù)庫(kù)包括oracle,mysql,db2都使用了WAL技術(shù).

    WAL的核心思想是:在數(shù)據(jù)寫入到數(shù)據(jù)庫(kù)之前，先寫入到日志.

    因?yàn)閷?duì)于數(shù)據(jù)的每筆修改都記錄在日志中，所以將對(duì)于數(shù)據(jù)的修改實(shí)時(shí)寫入到磁盤并沒有太大意義，即使當(dāng)SQL Server發(fā)生意外崩潰時(shí)，在恢復(fù)(recovery)過(guò)程中那些不該寫入已經(jīng)寫入到磁盤的數(shù)據(jù)會(huì)被回滾(RollBack),而那些應(yīng)該寫入磁盤卻沒有寫入的數(shù)據(jù)會(huì)被重做(Redo)。從而保證了持久性(Durability)

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

    硬盤是通過(guò)旋轉(zhuǎn)來(lái)讀取數(shù)據(jù),通過(guò)WAL技術(shù)，每次提交的修改數(shù)據(jù)的事務(wù)并不會(huì)馬上反映到數(shù)據(jù)庫(kù)中，而是先記錄到日志.在隨后的CheckPoint和lazy Writer中一并提交,如果沒有WAL技術(shù)則需要每次提交數(shù)據(jù)時(shí)寫入數(shù)據(jù)庫(kù):

   

       而使用WAL合并寫入，會(huì)大大減少磁盤IO：

     

     也許你會(huì)有疑問(wèn)，那每次對(duì)于修改的數(shù)據(jù)還是會(huì)寫入日志文件.同樣消耗磁盤IO。上篇文章講過(guò)，每一筆寫入日志的記錄都是按照先后順序，給定順序編號(hào)的LSN進(jìn)行寫入的，日志只會(huì)寫入到日志文件的邏輯末端。而不像數(shù)據(jù)那樣，可能會(huì)寫到磁盤的各個(gè)地方.所以，寫入日志的開銷會(huì)比寫入數(shù)據(jù)的開銷小很多。

   

SQL Server修改數(shù)據(jù)的步驟

     SQL Server對(duì)于數(shù)據(jù)的修改,會(huì)分為以下幾個(gè)步驟順序執(zhí)行:

     1.在SQL Server的緩沖區(qū)的日志中寫入”Begin Tran”記錄

     2.在SQL Server的緩沖區(qū)的日志頁(yè)寫入要修改的信息

     3.在SQL Server的緩沖區(qū)將要修改的數(shù)據(jù)寫入數(shù)據(jù)頁(yè)

     4.在SQL Server的緩沖區(qū)的日志中寫入”Commit”記錄

     5.將緩沖區(qū)的日志寫入日志文件

     6.發(fā)送確認(rèn)信息(ACK)到客戶端(SMSS,ODBC等）

  

     可以看到,事務(wù)日志并不是一步步寫入磁盤.而是首先寫入緩沖區(qū)后，一次性寫入日志到磁盤.這樣既能在日志寫入磁盤這塊減少IO，還能保證日志LSN的順序.

     上面的步驟可以看出，即使事務(wù)已經(jīng)到了Commit階段，也僅僅只是把緩沖區(qū)的日志頁(yè)寫入日志，并沒有把數(shù)據(jù)寫入數(shù)據(jù)庫(kù).那將要修改的數(shù)據(jù)頁(yè)寫入數(shù)據(jù)庫(kù)是在何時(shí)發(fā)生的呢?

 

Lazy Writer和CheckPoint

    上面提到，SQL Server修改數(shù)據(jù)的步驟中并沒有包含將數(shù)據(jù)實(shí)際寫入到磁盤的過(guò)程.實(shí)際上，將緩沖區(qū)內(nèi)的頁(yè)寫入到磁盤是通過(guò)兩個(gè)過(guò)程中的一個(gè)實(shí)現(xiàn)：

     這兩個(gè)過(guò)程分別為:

     1.CheckPoint

     2.Lazy Writer

    任何在緩沖區(qū)被修改的頁(yè)都會(huì)被標(biāo)記為“臟”頁(yè)。將這個(gè)臟頁(yè)寫入到數(shù)據(jù)磁盤就是CheckPoint或者Lazy Writer的工作.

    當(dāng)事務(wù)遇到Commit時(shí)，僅僅是將緩沖區(qū)的所有日志頁(yè)寫入磁盤中的日志文件:

   

 

     而直到Lazy Writer或CheckPoint時(shí)，才真正將緩沖區(qū)的數(shù)據(jù)頁(yè)寫入磁盤文件:

   

   

    前面說(shuō)過(guò)，日志文件中的LSN號(hào)是可以比較的，如果LSN2>LSN1,則說(shuō)明LSN2的發(fā)生時(shí)間晚于LSN1的發(fā)生時(shí)間。CheckPoint或Lazy Writer通過(guò)將日志文件末尾的LSN號(hào)和緩沖區(qū)中數(shù)據(jù)文件的LSN進(jìn)行對(duì)比，只有緩沖區(qū)內(nèi)LSN號(hào)小于日志文件末尾的LSN號(hào)的數(shù)據(jù)才會(huì)被寫入到磁盤中的數(shù)據(jù)庫(kù)。因此確保了WAL（在數(shù)據(jù)寫入到數(shù)據(jù)庫(kù)之前，先寫入日志)。

 

Lazy Writer和CheckPoint的區(qū)別

    Lazy Writer和CheckPoint往往容易混淆。因?yàn)長(zhǎng)azy Writer和CheckPoint都是將緩沖區(qū)內(nèi)的“臟”頁(yè)寫入到磁盤文件當(dāng)中。但這也僅僅是他們唯一的相同點(diǎn)了。

    Lazy Writer存在的目的是對(duì)緩沖區(qū)進(jìn)行管理。當(dāng)緩沖區(qū)達(dá)到某一臨界值時(shí)，Lazy Writer會(huì)將緩沖區(qū)內(nèi)的臟頁(yè)存入磁盤文件中，而將未修改的頁(yè)釋放并回收資源。

    而CheckPoint存在的意義是減少服務(wù)器的恢復(fù)時(shí)間(Recovery Time).CheckPoint就像他的名字指示的那樣，是一個(gè)存檔點(diǎn).CheckPoint會(huì)定期發(fā)生.來(lái)將緩沖區(qū)內(nèi)的“臟”頁(yè)寫入磁盤。但不像Lazy Writer,Checkpoint對(duì)SQL Server的內(nèi)存管理毫無(wú)興趣。所以CheckPoint也就意味著在這個(gè)點(diǎn)之前的所有修改都已經(jīng)保存到了磁盤.這里要注意的是：CheckPoint會(huì)將所有緩沖區(qū)的臟頁(yè)寫入磁盤，不管臟頁(yè)中的數(shù)據(jù)是否已經(jīng)Commit。這意味著有可能已經(jīng)寫入磁盤的“臟頁(yè)”會(huì)在之后回滾（RollBack).不過(guò)不用擔(dān)心，如果數(shù)據(jù)回滾，SQL Server會(huì)將緩沖區(qū)內(nèi)的頁(yè)再次修改，并寫入磁盤。

    通過(guò)CheckPoint的運(yùn)作機(jī)制可以看出，CheckPoint的間歇(Recovery Interval)長(zhǎng)短有可能會(huì)對(duì)性能產(chǎn)生影響。這個(gè)CheckPoint的間歇是一個(gè)服務(wù)器級(jí)別的參數(shù)?？梢酝ㄟ^(guò)sp_config進(jìn)行配置，也可以在SSMS中進(jìn)行配置:

   

    恢復(fù)間歇的默認(rèn)參數(shù)是0，意味著由SQL Server來(lái)管理這個(gè)回復(fù)間隔。而自己設(shè)置恢復(fù)間隔也是需要根據(jù)具體情況來(lái)進(jìn)行界定。更短的恢復(fù)間歇意味這更短的恢復(fù)時(shí)間和更多的磁盤IO，而更長(zhǎng)的恢復(fù)間歇?jiǎng)t帶來(lái)更少的磁盤IO占用和更長(zhǎng)的恢復(fù)時(shí)間.

    除了自動(dòng)CheckPoint之外，CheckPoint還會(huì)發(fā)生在Alter DataBase以及關(guān)閉SQL Server服務(wù)器時(shí)。sysadmin和db_backupoperator組的成員以及db_owner也可以使用CheckPoint指令來(lái)手動(dòng)保存CheckPoint:

   

    通過(guò)指定CheckPoint后的參數(shù)，SQL Server會(huì)按照這個(gè)時(shí)間來(lái)完成CheckPoint過(guò)程，如果時(shí)間指定的短，則SQL Server會(huì)使用更多的資源優(yōu)先完成CheckPoint過(guò)程。

    通常情況下，將“臟”頁(yè)寫入磁盤的工作，Lazy Writer要做的比CheckPoint會(huì)多出許多。

 

總結(jié)

    本文簡(jiǎn)單介紹了WAL的概念和修改數(shù)據(jù)庫(kù)對(duì)象時(shí)，日志所扮演的角色。還分別介紹了CheckPoint和Lazy Writer,對(duì)于這些概念的理解是理解SQL Server DBA工作的基礎(chǔ)。下篇文章將會(huì)講述在簡(jiǎn)單恢復(fù)模式下日志的機(jī)制。