ACID Properties

3 4

A transaction must meet four requirements to qualify as a valid transaction. These requirements are known as the ACID properties. "ACID" is an acronym for "atomicity, consistency, isolation, and durability." SQL Server provides mechanisms to help ensure that a transaction meets each of these requirements.

Atomicity

SQL Server ensures that all data modifications in a transaction are completed as a group if the transaction is successful or that none of the modifications occur if the transaction is not successful—in other words, SQL Server ensures the atomicity of your transactions. The transaction must be performed as an atomic unit—thus the term "atomicity." For a transaction to be successful, every step (or statement) in the transaction must succeed. If one of the steps fails, the entire transaction fails, and any modifications made since the transaction started will be undone. SQL Server provides a transaction management mechanism that automatically performs the task of determining whether a transaction has succeeded or failed and undoes any data modifications, as necessary, in the case of a failure.

Consistency

SQL Server also ensures the consistency of your transactions. Consistency means that all data remains in a consistent state—that the integrity of the data is preserved—after a transaction finishes, regardless of whether the transaction failed or was completed successfully. Before a transaction begins, the database must be in a consistent state, which means that the integrity of the data is upheld and that internal structures, such as B-tree indexes and doubly linked lists, are correct. And after a transaction occurs, the database must be in a consistent state—a new state if the transaction succeeded or, if the transaction failed, the same consistent state it was in before the transaction started.

Consistency is also a transaction management feature provided by SQL Server. If your data is consistent and your transactions maintain logical consistency and data integrity, SQL Server will ensure the consistency of the data after a transaction. When you are using data replication in a distributed environment, various levels of consistency can be achieved that range from eventual transactional convergence (or latent consistency) to immediate transactional consistency. The level of consistency will depend on the type of replication you use. For more information about replication, see Chapters 26, 27, and 28.

Isolation

Isolation means that the effects of each transaction are the same as if the transaction were the only one in the system; in other words, modifications made by a transaction are isolated from modifications made by any other concurrent transaction. In this way, a transaction will not be affected by a value that has been changed by another transaction until the change is committed. If a transaction fails, its modifications will have no effect because the changes will be rolled back. SQL Server enables you to adjust the isolation level of your transactions. A transaction's isolation behavior depends on the isolation level you specify.

NOTE

---

When a transaction is committed, all of its modifications are made a permanent part of the database. When a transaction is rolled back, the changes are rescinded, and the database functions as if the transaction had never occurred.

Levels of Isolation

SQL Server supports four levels of isolation. A level of isolation is a setting that determines the level at which a transaction is allowed to accept inconsistent data—that is, the degree to which one transaction is isolated from another. A higher isolation level increases data accuracy, but it can reduce the number of concurrent transactions. On the other hand, a lower isolation level will allow more concurrency but will result in reduced data accuracy. The isolation level you specify for a SQL Server session determines the locking behavior for all SELECT statements performed during that session (unless you set the isolation level to another level). Locking behavior is described in the section "Transaction Locking" later in this chapter. The four levels of isolation, from lowest to highest, are listed here:

• Read uncommitted Lowest level of isolation. At this level, transactions are isolated just enough to ensure that physically corrupted data is not read.
• Read committed Default level for SQL Server. At this level, reads are allowed only on committed data. (Committed data is data that has been made a permanent part of the database.)
• Repeatable read Level at which repeated reads of the same row or rows within a transaction will achieve the same results. (Until a transaction is completed, no other transactions can modify the data.)
• Serializable Highest level of isolation; transactions are completely isolated from each other. At this level, the results achieved by running concurrent transactions on a database are the same as if the transactions had been run serially (one at a time in some order).

Concurrent Transaction Behavior

To better understand each isolation level, we must first look at three types of behaviors that can occur when you are running concurrent transactions. These behaviors are as follows:

• Dirty read A read that retrieves uncommitted data. A dirty read occurs when one transaction modifies data and a second transaction reads the modified data before the first transaction has committed the changes. If the first transaction rolls back the changes, the second transaction will have retrieved data that is not in the database.
• Nonrepeatable read Inconsistent results obtained by repeated reads. A nonrepeatable read occurs when a single row of data is read more than once within a transaction, and between the reads, a separate transaction makes an update to that row. Because the first transaction's repeated reads will retrieve different data, the results were not repeatable within that transaction.
• Phantom read A read that occurs when a transaction attempts to retrieve a row that does not exist when the transaction begins but that is inserted by a second transaction before the first transaction finishes. If the first transaction again looks for the row, it will find that the row has suddenly appeared. This new row is called a phantom row.

Table 19-1 lists the types of behaviors each isolation level allows. As you can see, read uncommitted is the least restrictive isolation level, and serializable is the most restrictive. As mentioned, the default SQL Server isolation level is read committed. As the level of isolation increases, SQL Server will hold more restrictive locks, for longer periods of time. And since the isolation level affects the locking behavior for SELECT statements, isolation affects the locking mode used on data that is being read. Locking modes are described in the section "Transaction Locking" later in this chapter.

Table 19-1. Isolation level behaviors

	Behavior Allowed
Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Serializable	No	No	No

Setting the Isolation Level

You can set the isolation level to be used for an entire SQL Server user session by using Transact-SQL (T-SQL) statements or through functions in your application. You can also specify a locking hint in a query to override the set isolation level for that transaction. Locking hints are given in the section "Locking Hints" later in this chapter. To set the isolation level by using T-SQL or in a DB-LIB application, use the SET TRANSACTION ISOLATION LEVEL statement, and specify one of the four isolation levels. The syntax is as follows:

 SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE GO 

MORE INFO

---

For other types of applications, such as those using ODBC, ADO, or OLE-DB, look up "ACID properties" in the Books Online index and select "Adjusting Transaction Isolation Levels" in the Topics Found dialog box.

Once you have set the isolation level for a session, subsequent transactions in that SQL Server session will perform locking that ensures this isolation level. To find out which isolation level SQL Server is currently using as the default, use the DBCC USEROPTIONS command. The syntax for this command is simply the command name:

 DBCC USEROPTIONS 

This command returns only the options that a user has set or that are active. If you do not set the isolation level (leaving it at the SQL Server default level), you will not see the isolation level when you run the DBCC USEROPTIONS command. If you do specify a level other than the default, however, you will see the isolation level. For example, if you execute the following statements, the isolation level will be shown in the output from DBCC USEROPTIONS:

 USE pubs  GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO DBCC USEROPTIONS GO 

The results returned from the DBCC USEROPTIONS command will look something like this:

 Set Option Value   ------------------------------------------- textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation_level serializable (13 row(s) affected) 

You can use locking hints at the table level to override the default isolation level, but you should use this technique only when absolutely necessary and if you fully understand how it will affect your transactions. For more details about the available locking hints, see the section "Locking Hints" later in this chapter.

Durability

The last ACID property is durability. Durability means that once a transaction is committed, the effects of the transaction remain permanently in the database, even in the event of a system failure. The SQL Server transaction log and your database backups provide durability. If SQL Server, the operating system, or a component of the server fails, the database will automatically recover when SQL Server is restarted. SQL Server uses the transaction log to replay the committed transactions that were affected by the system crash and to roll back any uncommitted transactions.

If a data drive fails and data is lost or corrupted, you can recover the database by using database backups and transaction log backups. If you plan your backups well, you should always be able to recover your system from a failure. Unfortunately, if your backup drives fail and you lose the backup that is needed to recover the system, you might not be able to recover your database. See Chapters 32 and 33 for details about backing up and restoring your database and transaction logs. Now that you understand the properties of a transaction, let's look at how to begin and end a transaction.