SQL Server – Does Log Record Uncommitted Operations?

sql server

I often see statements like sql server log records every transction and opeation.

But I am confused about what happens when a transaction is eventually rolled back.

Say an explicit transaction has 3 statements: statement A, statement B, statement C, and finally a rollback statement D.

Now say when the execution has not reached the rollback statement D, will the modifications resulting from statements A through C get recorded to sql server log?

Understanding 1:

Statements A through D all get recorded. SQL Server records everything, no matter what.

Understanding 2:
Modifications are only stored somewhere in memory, and only recorded to log when SQL Server see a commit statement. If it turns out to be a rollback statement, SQL Server simply ignore the transacrion, no writing to log happens because it serves no purpose. In other words, SQL Server logs when there is a net result before and after the transactions.

Both seems logical, at least to me but they can't both be right. Thanks for any help.

Best Answer

Understanding 1 is correct. SQL Server records every operation that changes data to the transaction log. A rollback is a change to data so it also records that to the transaction log as well. As statement A run it will write data to the transaction log and will also reserve data in the transaction log in case statement A needs to be rolled back. The same is true for B and C. When you rollback the transaction more information will be written to the log.

There are plenty of ways to see this in action so below is a quick demo. Here is the query that I'll use to see what was written to the log:

SELECT 
  COUNT(*) transaction_count
, SUM(database_transaction_log_bytes_used) used_bytes
, SUM(database_transaction_log_bytes_reserved) reserved_bytes
FROM sys.dm_tran_database_transactions
where database_id = 10;

My table:

create table TLOGDEMO (FLUFF VARCHAR(1000));

BEGIN TRANSACTION

Query A uses minimal logging:

INSERT INTO TLOGDEMO WITH (TABLOCK)
SELECT REPLICATE('A', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After A:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║   24006640 ║      175429451 ║
╚═══════════════════╩════════════╩════════════════╝

Query B does not use minimal logging:

INSERT INTO TLOGDEMO
SELECT REPLICATE('B', 1000)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

After B:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7352935708 ║     1613986255 ║
╚═══════════════════╩════════════╩════════════════╝

Query C changes less data:

INSERT INTO TLOGDEMO
SELECT REPLICATE('C', 1000)
FROM master..spt_values c;

After C:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║                 1 ║ 7355821748 ║     1614545331 ║
╚═══════════════════╩════════════╩════════════════╝

Now I will issue a ROLLBACK and query the DMV while the rollback happens. Below is a table of a few snapshots:

╔═══════════════════╦════════════╦════════════════╗
║ transaction_count ║ used_bytes ║ reserved_bytes ║
╠═══════════════════╬════════════╬════════════════╣
║ 1                 ║ 7393305528 ║ 1573797677     ║
║ 1                 ║ 7458767420 ║ 1502635737     ║
║ 1                 ║ 7682482356 ║ 1259440979     ║
║ 1                 ║ 7803881368 ║ 1127471233     ║
║ ...               ║ ...        ║ ...            ║
╚═══════════════════╩════════════╩════════════════╝

During the ROLLBACK, the used bytes increases and the reserved number of bytes decreases. That's because SQL Server is using the space it set aside before to undo the transaction. To undo the transaction it must change data so it writes more data to the log.

Related Question