CODEWITHSUNDEEP
sql-server
dp0891
dp0891

Reputation: 45

Confusion regarding transaction ROLLBACK on server restart

Following is an except from SQL Server 461 training kit -

[...] if the database server shuts down unexpectedly before the fact of successful commit could be written to the log, when SQL Server starts up the database, the transaction will be rolled back and any database changes undone.

Microsoft page (https://msdn.microsoft.com/en-us/library/jj835093(v=sql.120).aspx#WAL) reads,

The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. [...] Log records are written to disk when the transactions are committed.

Any transactions that have not been committed would not have its log records flushed to disk, and therefore would not have the dirty page with data modifications flushed to disk.

I can only see rolling forward changes that have not been flushed to disk yet (from a committed transaction), but this rollback scenario seems impossible since there would never be a change on disk (from a non-committed transaction) to begin with.

Which part am I misunderstanding?

Upvotes: 1

Views: 678

Answers (1)

Martin Smith
Martin Smith

Reputation: 453152

Any transactions that have not been COMMITed, would not have its log records flushed to disk

This is not correct. At commit all the log records relating to the transaction must be flushed to disc but this can certainly happen earlier. It doesn't have to wait for all the transactions to commit before persisting that part of the log.

there would never be a change on disk (from an uncommited transaction) to begin with.

This is not correct.

Changes from uncommitted transactions can be written out to disc as soon as they are permanently recorded in the transaction log.

This happens every checkpoint.

It is of course not permissible for these changes to be written to the data files on disc until the transaction log with these changes has been flushed to disc as then there would be no way of recovery.

Upvotes: 3

Related Questions