3

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)

1

1 Answer 1

9

No your theory is wrong.

Dirty pages can be written to disc even if the transaction is not yet committed. However it is ensured that they cannot be written before the last transaction log entry that modified the page has been written to disc.

The transaction log records do contain sufficient information both for redo and undo (except for in tempdb where only undo is necessary). If you decide to rollback the transaction then nothing is deleted from the log. Instead compensation log records are written to the log indicating this.

2
  • Okay, that makes sense and fits with the practical test I mention in my EDIT. Thanks. One thing though: I do not fully understand implication of "it is ensured that they (dirty pages) cannot be written before the corresponding transaction log entry is written to disc". This simply means that it is to ensure that we know about them in the log and then either make them valid (on commit) or delete them (on rollback)? Jan 4, 2013 at 13:10
  • 2
    @NeverStopLearning - This is the WAL protocol. The dirty pages are not allowed to be written to disc before the log buffer is flushed up to at least that point because if you were to crash in that state there would be nothing to indicate that the changes need to be undone. BTW you might find this useful Inside the SQL Server Transaction Log Jan 4, 2013 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.