每次我講解SQL Server之前,我都會先簡單談下當我們執行查詢時,在SQL Server內部發生了什麼。執行一個SELECT語句非常簡單,但是執行DML語句更加複雜,因為SQL Server要修改記憶體中的相關頁,併在事務日誌里記錄整個事務。 介紹完這些特定步驟後,我總會問同樣的問題:當我們有個未
每次我講解SQL Server之前,我都會先簡單談下當我們執行查詢時,在SQL Server內部發生了什麼。執行一個SELECT語句非常簡單,但是執行DML語句更加複雜,因為SQL Server要修改記憶體中的相關頁,併在事務日誌里記錄整個事務。
介紹完這些特定步驟後,我總會問同樣的問題:當我們有個未提交的事務,這個時候剛好有檢查點(Checkpoint)發生,SQL Server會崩潰麽?在我們數據文件里有我們未提交的數據麽?先思考下,然後再寫下你的答案。
創建測試場景
現在我想和你一起重建這個特定場景,最後你會看到你是否回答對了。這個場景的第一步,我創建了一個新的資料庫,一個新的表,並插入一些記錄。
1 -- Create a new database 2 CREATE DATABASE Checkpointing 3 GO 4 5 -- Use it 6 USE Checkpointing 7 GO 8 9 -- Create a new table 10 CREATE TABLE Foo 11 ( 12 Col1 CHAR(100) NOT NULL, 13 Col2 CHAR(100) NOT NULL, 14 Col3 CHAR(100) NOT NULL 15 ) 16 GO 17 18 -- Insert a record 19 INSERT INTO Foo VALUES 20 ( 21 REPLICATE('A', 100), 22 REPLICATE('B', 100), 23 REPLICATE('C', 100) 24 ) 25 GO 26 27 -- Retrieve the record 28 SELECT * FROM Foo 29 GO
在我們插入數據後,我想知道SQL Server存儲特定記錄的頁號。我們可以使用DBCC IND命來來返回特定表的所有頁。在我的伺服器上SQL Server使用的Page id是79。
1 -- Retrieve the first data page for the specified table (columns PageFID and PagePID) 2 DBCC IND(Checkpointing, Foo, -1) 3 GO
現在當我們用DBCC PAGE命令輸出頁內容時(使用這個命令前,要先啟用3604跟蹤標記),我們可以看到插入的A,B,C的16進位值。
1 -- Enable DBCC trace flag 3604 2 DBCC TRACEON(3604) 3 GO 4 5 -- Dump the first data page of the table Customers retrieved by DBCC IND previously 6 DBCC PAGE (Checkpointing, 1,79, 3) 7 GO
現在當我們進行檢查點(Checkpoint)過程,並最終殺掉SQL Server會發生什麼?未提交的數據會物理寫入數據文件麽?我們來試驗下...
崩潰並恢復SQL Server
現在我們開始一個新的事務,並更新插入記錄的第一列。
1 -- Begin a new transaction without committing it... 2 BEGIN TRANSACTION 3 4 UPDATE Foo 5 SET Col1 = REPLICATE('X', 100)
從代碼里你可以看到,我們並沒有提交這個事務!它還是待定的,未提交的事務。現在我們打開另一個會話,我們人為進行一次檢查點(Checkpoint)過程,並最終關閉SQL Server。
1 -- Execute it in a different session 2 CHECKPOINT 3 GO 4 5 SHUTDOWN WITH NOWAIT 6 GO
現在你認為未提交的事務已經寫入數據文件了麽?不確定?我們來找出答案!我們在16進位的編輯器(例如XVI32)里打開數據文件。跳到頁號79的開始。在數據文件里,頁號是物理偏移量,即頁開始的地方——乘上8192位元組,因為在SQL Server里頁的大小是8kb。因此頁79的開始整數偏移量是647168(79*8192).當我們查看hex值時,我們看到了我們未提交的數據。
檢查點(Checkpoint)過程不會區分提交和未提交的事務。它只會到緩存管理器(Buffer Manager)索取所有臟頁,不管它們事務的狀態。
現在我們有不一致,損壞的資料庫了麽?沒有,並不真的是。因為現在當我們啟動SQL Server,每個資料庫都經過故障恢復階段,所有沒提交的事務都會回滾。當SQL Server啟動的時候,我們可以在SQL Server日誌里看到這個行為:
小結
檢查點(Checkpoint)不會在意你的事務狀態。來自緩存池(Buffer Pool)的每個臟頁會寫入數據頁。如果SQL Server崩潰了也沒關係,因為故障恢復能恢復你的資料庫到完全一致的狀態。我希望這篇日誌能讓你更好的理解檢查點(Checkpoint)過程,還有它如何與未提交的事務打交道。
作為家庭作業,你能否留言告訴我,還有哪些情形,SQL Server需要運行故障恢復為你的資料庫還原到一致狀態。在SQL Server里你知道多少個不同的場景呢?