The ACID properties of a transaction are enforced in SQL Server through the Write-Ahead Logging (WAL) protocol. The WAL protocol ensures the Durability of a transaction by ensuring that all modifications are first written to the transaction log before they are written to the data file. Once a transaction has been hardened to the transaction log, it can be re-created (or rolled back) in the event of a server crash.
Every data-modification operation goes through the same sequence, in which it writes first to the transaction log and then to the data file
Database Beginning State
Before the transaction begins, the database is in a consistent state. All indexes are complete and point to the correct row. The data meets all the enforced rules for data integrity.
Some data pages are likely already cached in the buffer pool. Additional data pages or index pages are copied into memory as needed. Following are the steps of a transaction.
So the first step in a transaction is: The database is in a consistent state.
The transaction is initiated by a submitted batch, as shown in Figure 47.10.
The second step in a transaction is: The code issues a begin transaction command. Even when the DML command is a standalone command without ...