Transaction management Part(1)

The core feature of any database including SQL Server is its ability to ensure that transactions are atomic—that is, all or nothing. Transactions must adhere to four properties, called the ACID properties: atomicity, consistency, isolation, and durability. In SQL Server, if work is in progress and a system failure occurs before the transaction is committed, all the work is rolled back to the state that existed before the transaction began.

Write-ahead logging makes possible the ability to always roll back work in progress or roll forward committed work that hasn’t yet been applied to the data pages. Write-ahead logging ensures that the record of each transaction’s changes is captured on disk in the transaction log before a transaction is acknowledged as committed, and that the log records are always written to disk before the data pages where the changes were actually made are written. the transaction management component delineates the boundaries of statements that must be grouped to form an operation. The transaction management component marks save points that you designate within a transaction at which work can be partially rolled back or undone.

The transaction management component also coordinates with the locking code regarding when locks can be released, based on the isolation level in effect. It also coordinates with the versioning code to determine when old versions are no longer needed and can be removed from the version store. The isolation level in which your transaction runs determines how sensitive your application is to changes made by others and consequently how long your transaction must hold locks or maintain versioned data to protect against those changes.

SQL Server 2012 supports two concurrency models for guaranteeing the ACID properties of transactions:

1.Pessimistic concurrency This model guarantees correctness and consistency by locking data so that it can’t be changed. Every version of SQL Server prior to SQL Server 2005 used this currency model exclusively; it’s the default in both SQL Server 2005 and later versions.

2. Optimistic currency SQL Server 2005 introduced optimistic concurrency, which provides consistent data by keeping older versions of rows with committed values in an area of tempdb called the version store. With optimistic concurrency, readers don’t block writers and writers don’t block readers, but writers still block writers. The cost of these non-blocking operations must be considered. To support optimistic concurrency, SQL Server needs to spend more time managing the version store. Administrators also have to pay close attention to the tempdb database and plan for the extra maintenance it requires.

The behavior of the transactions depends on the isolation level and the concurrency model we are working with.

A complete understanding of isolation levels also requires an understanding of locking. My next blog will give an overview of locking and more detailed information on isolation, transactions, and concurrency management.