一. 數(shù)據(jù)讀寫流程簡要
SQL Server作為一個關(guān)系型數(shù)據(jù)庫，自然也維持了事務(wù)的ACID特性，數(shù)據(jù)庫的讀寫沖突由事務(wù)隔離級別控制。無論有沒有顯示開啟事務(wù)，事務(wù)都是存在的。流程圖如下：

數(shù)據(jù)讀寫流程圖

0. 事務(wù)開始
(1) 所有DML語句必然是基于事務(wù)的，如果沒有顯式開啟事務(wù)，即手動寫下BEGIN TRAN，SQL Server則把每條語句作為一個事務(wù)，并自動提交事務(wù)。

也就是說SQL SERVER 默認(rèn)不開啟隱式事務(wù)，這點與ORACLE正好相反，ORACLE默認(rèn)開啟了隱式事務(wù)，每條DML語句或者語句塊，都要手動commit才會提交。

SQL Server里如要改變這個默認(rèn)行為，可以在會話里做如下設(shè)置，如果沒有打開隱式事務(wù)，SQL Server會自動提交當(dāng)前的DML語句，而打開后，需要手動COMMIT才會提交。

      
        --
      
      
        開啟隱式事務(wù)
      
      
        SET
      
       IMPLICIT_TRANSACTIONS 
      
        ON
      
      
        --
      
      
        插入一條記錄
      
      
        CREATE
      
      
        TABLE
      
       TEST_TRAN(ID 
      
        INT
      
      
        )


      
      
        INSERT
      
      
        INTO
      
       TEST_TRAN 
      
        VALUES
      
      (
      
        1
      
      
        )




      
      
        --
      
      
        查看開啟的事務(wù)
      
      
        DBCC
      
       OPENTRAN()
    

(2) 如果手動開啟了一個事務(wù)(BEGIN TRAN)，則和開啟隱式事務(wù)(SET IMPLICIT_TRANSACTIONS ON)一樣，需要手動提交事務(wù)(COMMIT)；

1. 發(fā)起DML
(1) DML通常指的是：INSERT、DELETE、UPDATE；
(2) DDL語句最終是被轉(zhuǎn)化為對系統(tǒng)表的DML，在SQL SERVER中DDL語句也可以被回滾，比如：CREATE/ALTER/DROP/TRUNCATE，在ORACLE里是不可以的，另外SQL Server中的DCL語句：DENY，REVOKE，也可以被回滾；

2. 數(shù)據(jù)是否在內(nèi)存
(1) 在內(nèi)存中使用HASH算法查找數(shù)據(jù)，如果找到數(shù)據(jù)那么記為邏輯讀；
(2) 如果數(shù)據(jù)頁不在內(nèi)存中，則需要從磁盤上的數(shù)據(jù)文件中，讀取相應(yīng)的數(shù)據(jù)頁到內(nèi)存中，即物理讀，物理讀也會被記數(shù)為邏輯讀，也就是說無論內(nèi)存中有沒有數(shù)據(jù)，邏輯讀是一定有的。

3. 修改數(shù)據(jù)
(1) 在SQL SERVER內(nèi)存的數(shù)據(jù)緩沖區(qū)中將數(shù)據(jù)頁修改，此時數(shù)據(jù)頁稱為臟頁(DIRTY PAGE)；
(2) 在SQL SERVER內(nèi)存的日志緩沖區(qū)中記錄REDO LOG，姑且稱為臟日志；

4. 事務(wù)結(jié)束
(1) 提交(COMMIT)，此時將當(dāng)前事務(wù)的臟日志刷新到數(shù)據(jù)庫的日志文件中，并打上事務(wù)結(jié)束標(biāo)記(COMMIT)，臟頁有可能暫未被刷新到數(shù)據(jù)文件；
事務(wù)日志結(jié)構(gòu)如下(可通過log explorer等類似工具查看)：
BEGIN TRAN
DML
COMMIT TRAN

(2) 回滾(ROLLBACK)，此時讀REDO LOG得到反向DML操作，反向修改臟頁，正向的DML+反向DML都會被記錄在數(shù)據(jù)庫的日志文件中，并打上事務(wù)結(jié)束標(biāo)記(ROLLBACK)，同樣，臟頁有可能暫未被刷新到數(shù)據(jù)文件；
事務(wù)日志結(jié)構(gòu)如下：
BEGIN TRAN
DML
反向DML
ROLLBACK TRAN

不難發(fā)現(xiàn)，SQL SERVER的日志容易成為一個瓶頸(BOTTLENECK)，因為在寫的同時引入了讀，即引入了競爭，而ORACLE用UNDO SEGMENT很好地避免了這個問題，REDO LOG永遠(yuǎn)只是在被串行寫。

5. 刷新數(shù)據(jù)頁
(1) SQL Server數(shù)據(jù)庫遵循預(yù)寫日志(WAL:Write-Ahead Logging)原則，因為關(guān)系型數(shù)據(jù)庫是基于事務(wù)的，而日志正是事務(wù)ACID屬性的保證，也是數(shù)據(jù)恢復(fù)的保證；
(2) 檢查點(CHECKPOINT)，檢查點周期性地將臟頁刷新到數(shù)據(jù)文件中，最終在日志文件打上檢查點標(biāo)記(CHECKPOINT)，至此上面事務(wù)中修改的數(shù)據(jù)被正式寫到磁盤上的數(shù)據(jù)文件中。

二. 數(shù)據(jù)讀寫流程深入
試想：
(1) 日志是不是一定要在COMMIT后才寫到日志文件？如果有個很長很大的事務(wù)，那么提交日志時，日志從緩沖區(qū)被寫入磁盤，豈不是要等很久？
(2) 數(shù)據(jù)是不是一定要在日志提交后，發(fā)生了CHECKPOINT，才寫到數(shù)據(jù)文件？如果日志一直沒提交，那么數(shù)據(jù)緩沖區(qū)豈不是很擁擠？

考慮到這2點，SQL Server還會通過Log Writer/Lazy Writer不定時的刷新日志/數(shù)據(jù)到磁盤，至于日志和數(shù)據(jù)的一致性，在啟動或者數(shù)據(jù)庫還原時，SQL Server會去做檢查，也即是我們常說的前滾(REDO)和回滾(UNDO)。

數(shù)據(jù)讀寫體系結(jié)構(gòu)圖

0. SQL SERVER MEMORY
(1) SQL SERVER占用服務(wù)器內(nèi)存的一部分，非SQL SERVER占用的內(nèi)存，供操作系統(tǒng)及服務(wù)器上其他應(yīng)用程序使用；
(2) SQL SERVER內(nèi)存對象可分為兩大類，圖中僅標(biāo)出Buffer Pool中的數(shù)據(jù)及日志緩存；

1. 事務(wù)結(jié)束
(1) 事務(wù)結(jié)束的前提是日志緩存成功寫入到日志文件中，也就是說客戶端收到COMMIT/ROLLBACK語句運(yùn)行成功的消息時，日志已被成功寫入日志文件(數(shù)據(jù)還不定是否被寫入數(shù)據(jù)文件)；
(2) 不過，日志緩存并不是一定要等到事務(wù)結(jié)束時才刷新到日志文件的；

2. LOG WRITER
(1) 當(dāng)遇到長事務(wù)時，不必等到發(fā)出事務(wù)結(jié)束命令，LOG WRITER也會周期性地將臟日志刷新到日志文件，以保證用戶發(fā)出COMMIT時快速響應(yīng)以結(jié)束事務(wù)；
(2) 微軟并沒有公布SQL SERVER 除去COMMIT外，LOG WRITER將臟日志刷新到日志文件的周期，這里可以參考ORACLE的：每3秒，或者日志緩沖區(qū)1/3滿；或者已經(jīng)包含1M的臟日志；

3. LAZY WRITER
(1) LAZY WRITER周期性掃描緩存(默認(rèn)1s)，維護(hù)自由頁面(free page)列表，根據(jù)LRU算法將已刷新到磁盤的頁釋放；
(2) 如果是臟頁，則Lazy Writer將臟頁刷新到磁盤(這時事務(wù)可能還未提交)，以最終將內(nèi)存頁釋放并加入自由頁面列表；

4. CHECKPOINT
(1) CHECKPOINT同LAZY WRITER一樣也會刷新臟頁到數(shù)據(jù)文件中(只刷新已提交的事務(wù)數(shù)據(jù))，但不會維護(hù)內(nèi)存自由頁面列表；
(2) 可以設(shè)置SP_CONFIGURE ‘RECOVERY INTERVAL’選項來改變CHECKPOINT發(fā)生的頻率，默認(rèn)為1分鐘一次。

小結(jié) ：可以發(fā)現(xiàn)，數(shù)據(jù)和日志被寫入數(shù)據(jù)/日志文件，并不是同步的。有可能寫入/提交了日志，數(shù)據(jù)沒有寫入磁盤；有可能寫入了數(shù)據(jù)，事務(wù)未被提交；
(1) 針對有完整事務(wù)日志，數(shù)據(jù)未被寫入磁盤的情況，啟動/還原數(shù)據(jù)庫時，SQL SERVER做前滾(REDO)；
(2) 針對有數(shù)據(jù)寫入數(shù)據(jù)文件，日志未完整提交的事務(wù)，啟動/還原數(shù)據(jù)庫時，SQL SERVER做回滾(UNDO)。

?

01. SQL Server 如何讀寫數(shù)據(jù)