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

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

0. 事務(wù)開(kāi)始
(1) 所有DML語(yǔ)句必然是基于事務(wù)的，如果沒(méi)有顯式開(kāi)啟事務(wù)，即手動(dòng)寫(xiě)下BEGIN TRAN，SQL Server則把每條語(yǔ)句作為一個(gè)事務(wù)，并自動(dòng)提交事務(wù)。

也就是說(shuō)SQL SERVER 默認(rèn)不開(kāi)啟隱式事務(wù)，這點(diǎn)與ORACLE正好相反，ORACLE默認(rèn)開(kāi)啟了隱式事務(wù)，每條DML語(yǔ)句或者語(yǔ)句塊，都要手動(dòng)commit才會(huì)提交。

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

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


      
      
        INSERT
      
      
        INTO
      
       TEST_TRAN 
      
        VALUES
      
      (
      
        1
      
      
        )




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

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

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

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

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

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

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

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

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

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

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

數(shù)據(jù)讀寫(xiě)體系結(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)存對(duì)象可分為兩大類(lèi)，圖中僅標(biāo)出Buffer Pool中的數(shù)據(jù)及日志緩存；

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

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

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

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

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

?

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