How the checkpoint (Checkpoint) process handles uncommitted transactions

Every time I talk about SQL Server, I'll start by talking about what happens inside SQL Server when we execute the query. Executing a SELECT statement is straightforward, but executing DML statements is more complex because SQL Server modifies the related pages in memory and records the entire transaction in the transaction log.

After describing these specific steps, I always ask the same question: Does SQL Server crash when we have an uncommitted transaction that happens to have a checkpoint (Checkpoint)? Are there any data in our data file that we haven't submitted? Think about it before you write your answer.

Creating a test scenario

Now I want to rebuild this particular scene with you, and finally you will see if you have answered right. The first step in this scenario, I created a new database, a new table, and inserted some records.

1 --Create a new database2 CREATE DATABASEcheckpointing3 GO4 5 --Use it6  Usecheckpointing7 GO8 9 --Create a new tableTen CREATE TABLEFoo One ( ACol1CHAR( -) not NULL, -Col2CHAR( -) not NULL, -Col3CHAR( -) not NULL the ) - GO -  - --Insert a record + INSERT  intoFooVALUES - ( +     REPLICATE('A', -), A     REPLICATE('B', -), at     REPLICATE('C', -) - ) - GO -  - --Retrieve the Record - SELECT *  fromFoo in GO

After we insert the data, I want to know the page number where SQL Server stores the specific record. We can use DBCC IND to return all pages of a particular table. The page ID used by SQL Server on my server is 79.

1 -- Retrieve The first data page for the specified table (columns Pagefid and Pagepid) 2 DBCC - 1 )3GO

Now when we use the DBCC PAGE command to output page content (before using this command, first enable the 3604 trace flag), we can see the 16 binary values of the inserted a,b,c.

1 --Enable DBCC trace flag 36042 DBCCTRACEON (3604)3 GO4 5 --Dump The first data page of the table Customers retrieved by DBCC IND previously6 DBCCPAGE (checkpointing,1, -,3)7 GO

What happens now when we take the checkpoint (Checkpoint) process and eventually kill SQL Server? Does uncommitted data physically write to the data file? Let's test it out.

crash and restore SQL Server

Now we start a new transaction and update the first column of the inserted record.

1 -- Begin a new transaction without committing it ... 2 BEGIN TRANSACTION 3 4 UPDATE Foo 5 SET = REPLICATE ('X')

From the code you can see that we did not commit this transaction! It is still pending, uncommitted transactions. Now that we have another session open, we are making a checkpoint (Checkpoint) process and finally shutting down SQL Server.

1 -- Execute it in a different session 2 CHECKPOINT 3 GO 4 5 SHUTDOWN  with NOWAIT 6 GO

Now do you think the uncommitted transaction has been written to the data file? Not sure? Let's find the answer! We open the data file in the 16 binary editor (e.g. XVI32). Jump to the beginning of page number 79. In the data file, the page number is the physical offset, that is, where the page started-multiply by 8192 bytes, because the size of the page in SQL Server is 8KB. Therefore, the starting integer offset for page 79 is 647168 (79*8192). When we look at the hex value, we see the data we didn't commit.

The checkpoint (Checkpoint) procedure does not differentiate between committed and uncommitted transactions. It only requests all dirty pages from the cache manager (buffer Manager), regardless of the state of their transactions.

Now we have inconsistent, corrupt database? No, it's not really. Because now when we start SQL Server, each database goes through the recovery phase, and all uncommitted transactions are rolled back. When SQL Server starts, we can see this behavior in the SQL Server log:

Summary

Checkpoints (Checkpoint) do not care about your state of affairs. Each dirty page from the buffer pool is written to the data page. It doesn't matter if SQL Server crashes, because failback can restore your database to a completely consistent state. I hope this log will give you a better understanding of the checkpoint (Checkpoint) process and how it deals with uncommitted transactions.

As a homework, can you leave a message to tell me what else is happening, SQL Server needs to run failback to restore your database to a consistent state. How many different scenarios do you know in SQL Server?

Reference article: https://www.sqlpassion.at/archive/2016/01/25/how-the-checkpoint-process-deals-with-uncommitted-transactions/

How the checkpoint (Checkpoint) process handles uncommitted transactions