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