Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:)

share|improve this question
    
This blog post I wrote may help clear things up: voluntarydba.com/post/2012/10/16/… –  Jon Seigel Jan 4 '13 at 18:20
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
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)? –  NeverStopLearning Jan 4 '13 at 13:10
1  
@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 –  Martin Smith Jan 4 '13 at 13:14
    
Thank you again for answers and for the link! I will check it out. –  NeverStopLearning Jan 4 '13 at 13:33
    
@Jon - That edit isn't correct. It is a special optimisation for tempdb because it gets recreated after every restart so it doesn't need the D in ACID. –  Martin Smith Jan 4 '13 at 18:30
1  
@JonSeigel - Try this on a DB with simple recovery and in tempdb to see what I mean CREATE TABLE Test(X CHAR(8000) DEFAULT REPLICATE('A',8000));INSERT INTO Test DEFAULT VALUES;CHECKPOINT;UPDATE Test SET X = REPLICATE('B',8000);SELECT CAST([RowLog Contents 0] AS CHAR(8000)), CAST([RowLog Contents 1] AS CHAR(8000));FROM sys.fn_dblog(NULL,NULL) WHERE Operation = 'LOP_MODIFY_ROW'. The tempdb just has the "before" version the simple recovery DB has before and after so it can roll forward committed transactions that never got written to disc in crash recovery. –  Martin Smith Jan 4 '13 at 18:46
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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