Microsoft SQL Server 2012 Bible

Transaction-Log Architecture

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.

Transaction-Log Sequence

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.

Data-Modification Command

The transaction is initiated by a submitted batch, as shown in Figure 47.10.

Figure 47.10 The SQL DML commands are performed in memory as part of a transaction.

The second step in a transaction is: The code issues a begin transaction command. Even when the DML command is a standalone command without ...