How SQL Server Manages Transactions

SQL Server uses the database's transaction log to record the modifications occurring within the database. Each log record is labeled with a unique log sequence number (LSN), and all log entries that are part of the same transaction are linked together so they can be easily located if the transaction needs to be undone or redone. The primary responsibility of logging is to ensure transaction durability?either ensuring that the completed changes make it to the physical database files, or ensuring that any unfinished transactions are rolled back should there be an error or a server failure.

What is logged? Obviously, the start and end of a transaction are logged, but also the actual data modification, page allocations and deallocations, and changes to indexes. SQL Server keeps track of a number of pieces of information, all with the aim of ensuring the ACID properties of the transaction.

After a transaction has been committed, it cannot be rolled back. The only way to undo a committed transaction is to write another transaction to reverse the changes made. Before a transaction is committed, it can be rolled back.

SQL Server provides transaction management for all users using the following components:

  • Transaction-control statements to define the logical units of work

  • A write-ahead transaction log

  • An automatic recovery process

  • Data-locking mechanisms to ensure consistency and transaction isolation

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features