What is a Transaction

What is a Transaction?

A transaction is an atomic unit of work that must be completed in its entirety. The transaction succeeds if it is committed and it fails if it is aborted. Transactions have four essential attributes: atomicity, consistency, isolation, and durability (known as the ACID attributes).

• Atomicity The work cannot be broken into smaller parts. Although a transaction might contain many SQL statements, they must be run as an all-or-nothing proposition, which means that if a transaction is half complete when an error occurs, the work reverts to its state prior to the start of the transaction.

• Consistency A transaction must operate on a consistent view of the data and must also leave the data in a consistent state. Any work in progress must not be visible to other transactions until the transaction has been committed.

• Isolation A transaction should appear to be running by itself the effects of other ongoing transactions must be invisible to this transaction, and the effects of this transaction must be invisible to other ongoing transactions.

• Durability When a transaction is committed, it must be persisted so it will not be lost in the event of a power failure or other system failure. Only committed transactions are recovered during power-up and crash recovery; uncommitted work is rolled back.

Concurrency Models and Database Locking

The attributes of consistency and isolation are implemented by using the database locking mechanism, which keeps one transaction from affecting another. If one transaction needs access to data that another transaction is working with, the data is locked until the first transaction is committed or rolled back. Transactions that need to access locked data are forced to wait until the lock is released, which means that long-running transactions can affect performance and scalability. The use of locks to prevent access to the data is known as a "pessimistic" concurrency model.

In an "optimistic" concurrency model, locks are not used when the data is read. Instead, when updates are made, the data is checked to see if the data has changed since it was read. If the data has changed, an exception is thrown and the application applies business logic to recover.

Transaction Isolation Levels

Complete isolation can be great, but it comes at a high cost. Complete isolation means that any data that is read or written during a transaction must be locked. Yes, even data that is read is locked because a query for customer orders should yield the same result at the beginning of a transaction and at the end of the transaction.

Depending on your application, you might not need complete isolation. By tweaking the transaction isolation level, you can reduce the amount of locking and increase scalability and performance. The transaction isolation level affects whether you experience the following.

• Dirty Read Being able to read data that has not been committed. This can be a big problem if a transaction that has added data is rolled back.

• Nonrepeatable Read When a transaction reads the same row more than once and a different transaction modifies the row between reads.

• Phantom Read When a transaction reads a rowset more than once and a different transaction inserts or deletes rows between the first transaction's reads.

Table 10-1 lists the transaction isolation levels along with their effects. It also shows the concurrency model that the isolation level supports.

Table 10-1: Isolation Levels in SQL Server 2005

Level	Dirty Read	Nonrepeatable Read	Phantom Read	Concurrency Model
Read Uncommitted	Yes	Yes	Yes	None
Read Committed with Locks	No	Yes	Yes	Pessimistic
Read Committed with Snapshots	No	Yes	Yes	Optimistic
Repeatable Read	No	No	Yes	Pessimistic
Snapshot	No	No	No	Optimistic
Serializable	No	No	No	Pessimistic

Here are details on each concurrency level.

• Read Uncommitted Queries inside one transaction are affected by uncommitted changes in another transaction. No locks are acquired, and no locks are honored when data is read.

• Read Committed With Locks The default setting in SQL Server. Committed updates are visible within another transaction. Long-running queries and aggregations are not required to be point-in-time consistent.

• Read Committed With Snapshots Committed updates are visible within another transaction. No locks are acquired, and row versioning is used to track row modifications. Long-running queries and aggregates are required to be point-in-time consistent. This level comes with the overhead of the version store (discussed further at the end of this list). The version store provides increased throughput with reduced locking contention.

• Repeatable Read Within a transaction, all reads are consistent other transactions cannot affect your query results because they cannot complete until you finish your transaction and release your locks. This level is used primarily when you read data with the intention of modifying the data in the same transaction.

• Snapshot Used when accuracy is required on long-running queries and multi-statement transactions but there is no plan to update the data. No read locks are acquired to prevent modifications by other transactions because the changes will not be seen until the snapshot completes and the data modification transactions commit. Data can be modified within this transaction level at the risk of conflicts with transactions that have updated the same data after the snapshot transaction started.

• Serializable Places a range lock, which is a multirow lock, on the complete rowset that is accessed, preventing other users from updating or inserting rows into the data set until the transaction is complete. This data is accurate and consistent through the life of the transaction. This is the most restrictive of the isolation levels. Because of the large amount of locking in this level, you should use it only when necessary.

The version store retains row version records after the UPDATE or DELETE statement has committed, until all active transactions have committed. The version store essentially retains row version records until all of the following transaction types have committed or ended:

• Transactions that are running under Snapshot isolation

• Transactions that are running under Read Committed with Snapshot isolation

• All other transactions that started before the current transaction committed