An ACID Trip: How SQL Server Transactions Work

In a recent consulting engagement a system administrator asked me “what’s the point of the transaction log? ” followed by “do we need it?” and “can’t we delete it?”

So lets trip back to the 70’s…that’s right, disco music, lava lamps, waterbeds and Jim Gray was describing ACID, that’s right Atomicity, Consistency, Isolation and Durability, the four properties of a database transaction. These properties ensure that a transaction completes fully or fails entirely (Atomicity), that any data written to the database complies with all rules and leaves the database in a valid state (Consistency), that concurrently executing transactions cannot interfere with one another (Isolation) and that once a transaction is committed, it will remain so even in the event of a system failure (Durability).

acid

So how does SQL Server ensure that transactions comply with these four properties? SQL Server users a process called Write Ahead Logging (WAL) that ensures that a log record for any committed data modification is hardened to the Transaction Log file prior to the data page being written to disk.

Imagine that an application sends a request to SQL Server to either INSERT, UPDATE or DELETE a row in a table in a database. The SQL Server Relational Engine receives the request and either chooses a cached execution plan, or creates a new plan. The plan is then forwarded to the Storage Engine.

The Storage Engine receives the plan and determines if the data page containing the required row exists in a memory buffer called the Buffer Cache. If it isn’t there, the Storage Engine will request the memory page to be written to a free buffer in the Buffer Cache from the database data file.

Once the data page is available in the Buffer Cache, the Storage Engine invokes a process called the Lock Manager to place an exclusive lock on the row that is to be modified, the data page in memory is modified and the buffer is marked as dirty.

how-transaction-log-works

The Storage Engine then invokes an internal process called the Log Manager to create a log record in another memory buffer called the Log Cache. All information required to create (redo) or delete (undo) the modification made by the transaction is written to the log record. Each log record is given a unique sequential identifier called a Log Sequence Number (LSN), the LSN is then added to the page header of the dirty data page corresponding to the log record.

Once the Storage Engine receives a signal that any transaction with a record in the Log Cache is committed, all log records in the cache are flushed to the transaction log file on disk in LSN order. The Storage Engine then invokes the Lock Manager to release the exclusive lock on the row.

When an internal process called CHECKPOINT is invoked by the Storage Engine, it checks the LSNs in the header of each dirty page to ensure that their corresponding log records have been flushed to the Transaction Log file. All dirty buffers with corresponding log records in the Transaction Log file are then hardened to the data file. A CHECKPOINT is normally invoked automatically in order to meet the upper limit of the preconfigured recovery interval server configuration (by default it usually runs once every minute). The process can also be manually executed by running the CHECKPOINT command.

So how does this WAL process ensure that SQL Server complies with the ACID properties of database transactions?

  • Atomicity – If a transaction fails for any reason, the transaction log records can be used to roll back the data modifications already made by the transaction.
  • Consistency – When a database is started, log records are rolled forward or rolled back to ensure that the database is started in a consistent state.
  • Isolation – The Storage Engine locks the row and doesn’t allow other transactions to modify the row until the transaction is complete.
  • Durability – As the log records of a committed transaction are always written to the transaction log file prior to being hardened to the data file, SQL Server can recover from system failures using a process called Crash Recovery. This process uses the transaction log to undo the data modification made by any uncommitted transactions at the time of failure. The process will also use the transaction log to redo (roll forward) the data modifications made by committed transactions at the time of failure, even if the dirty pages associated with these transactions were not written to the data file at the time of the crash.

As you can see, the transaction log is a fundamental component of  SQL Server, so to answer my client’s questions…. yes, the transaction log is required, and no, you shouldn’t delete it!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s