SQL Server transactions are often described as having the ACID properties or “passing the ACID test,” where ACID is an acronym for atomic, consistent, isolated, and durable. Transactional adherence to the ACID tenets is commonplace in modern DBMSs and is a prerequisite for ensuring the safety and reliability of data.

A transaction is atomic if it’s an all-or-nothing proposition. When the transaction succeeds, all of its changes are stored permanently; when it fails, they’re completely reversed. So, for example, if a transaction includes ten DELETE commands and the last one fails, rolling back the transaction will reverse the previous nine. Likewise, if a single command attempts ten row deletions and one of them fails, the entire operation fails.

A transaction is consistent if it ensures that its underlying data never appears in an interim or illogical state—that is, if it never appears to be inconsistent. So, the data affected by an UPDATE command that changes ten rows will never appear to the outside world in an intermediate state—all rows will appear in either their initial state or their final state. This prevents one user from inadvertently interfering with another user’s work in progress. Consistency is usually implied by the other ACID properties.

A transaction is isolated if it is not affected by, nor affects, other concurrent transactions on the same data. The extent to which a transaction is isolated from other transactions is controlled by its transaction isolation level (specified via the SET TRANSACTION ISOLATION LEVEL command). These TILs range from no isolation at all—during which transactions can read uncommitted data and cannot exclusively lock resources—to serializable isolation—which locks the entire data set and prevents users from modifying it in any way until the transaction completes. (See the following section, “Transaction Isolation Levels,” for more information.) The trade-off with each isolation level is one of concurrency (concurrent access and modification of a data set by multiple users) vs. consistency. The more airtight the isolation, the higher the degree of data consistency. The higher the consistency, the lower the concurrency. This is because SQL Server locks resources to ensure data consistency. More locks means fewer simultaneous data modifications and reduced accessibility overall.

Isolation prevents a transaction from retrieving illogical or incomplete snapshots of data currently under modification by another transaction. For example, if a transaction is inserting a number of rows into a table, isolation prevents other transactions from seeing those rows until the transaction is committed. SQL Server’s TILs allow you to balance your data accessibility needs with your data integrity requirements.

A transaction is considered durable if it can complete despite a system failure or, in the case of uncommitted transactions, if it can be completely reversed following a system failure. SQL Server’s write-ahead logging and the database recovery process ensure that transactions committed but not yet stored in the database are written to the database following a system failure (rolled forward) and that transactions in progress are reversed (rolled back).

Regardless of whether an operation is logged or nonlogged, terminating it before it’s been committed results in the operation being rolled back completely. This is possible with nonlogged operations because page allocations are recorded in the transaction log.

Triggers behave as though they were nested one level deep. If a transaction that contains a trigger is rolled back, so is the trigger. If the trigger is rolled back, so is any transaction that encompasses it.

By default, each Transact-SQL command is its own transaction. These are known as automatic (or autocommit) transactions. They are begun and committed by the server automatically. A DML command that’s executed outside a transaction (and while implicit transactions are disabled) is an example of an automatic transaction. You can think of an automatic transaction as a Transact-SQL statement that’s ensconced between a BEGIN TRAN and a COMMIT TRAN. If the statement succeeds, it’s committed. If not, it’s rolled back.

Implicit transactions are ANSI SQL-92–compliant automatic transactions. They’re initiated automatically when any of numerous DDL or DML commands is executed. They continue until explicitly committed by the user. To toggle implicit transaction support, use the SET IMPLICIT_TRANSACTIONS command. By default, OLEDB and ODBC connections enable the ANSI_DEFAULTS switch, which, in turn, enables implicit transactions. However, they then immediately disable implicit transactions because of the grief mismanaged transactions can cause applications. Enabling implicit transactions is like rigging your car doors to lock automatically every time you shut them. It costs more time than it saves, and, sooner or later, you’re going to leave your keys in the ignition.

User-defined transactions are the chief means of managing transactions in SQL Server applications. A user-defined transaction is user-defined in that you control when it begins and when it ends. The BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN commands are used to control user-defined transactions. Here’s an example:

Transactions that span multiple servers are known as distributed transactions. These transactions are administered by a central manager application that coordinates the activities of the involved servers. SQL Server can participate in distributed transactions coordinated by manager applications that support the X/Open XA specification for Distributed Transaction Processing, such as the Microsoft Distributed Transaction Coordinator (DTC). You can initiate a distributed transaction in Transact-SQL using the BEGIN DISTRIBUTED TRANSACTION command.

The BULK INSERT, TRUNCATE TABLE, SELECT...INTO, and WRITETEXT/ UPDATETEXT commands minimize transaction logging by causing only page operations to be logged (BULK INSERT can, depending on the circumstances, create regular detail log records). Contrary to a popular misconception, these operations are logged—it’s just that they don’t generate detail transaction log information. That’s why the Books Online refers to them as nonlogged operations—they’re nonlogged in that they don’t generate row-level log records.

Nonlogged operations tend to be much faster than fully logged operations. And since they generate page allocation log records, they can be rolled back (but not forward) just like other operations. The price you pay for using them is transaction log recovery. Once you’ve executed a nonlogged command in a database, you can no longer back up the database’s transaction log—you must perform a full or differential database backup instead.

One obvious way of avoiding logging as well as resource blocks and deadlocks in a database is by making the database read-only. Naturally, if the database can’t be changed, there’s no need for transaction logging or resource blocks. Making the database single-user even alleviates the need for read locks, avoiding the possibility of an application blocking itself.

Though reducing a database’s accessibility in order to minimize transaction management issues might sound a little like not driving your car in order to keep it from breaking down, you sometimes see this in real applications. For example, it’s fairly common for DSS (Decision Support System) applications to make use of read-only databases. These databases can be updated off-hours (e.g., overnight or on weekends), then returned to read-only status for use during normal working hours. Obviously, transaction management issues are greatly simplified when a database is modifiable only by one user at a time, is changed only en masse, or can’t be changed at all.

Read-only databases can also be very functional as members of partitioned data banks. Sometimes an application can be spread across multiple databases—one containing static data that doesn’t change much (and can therefore be set to read-only) and one containing more dynamic data that must submit to at least nominal transaction management.

Specifying READ UNCOMMITTED is essentially the same as using the NOLOCK hint with every table referenced in a transaction. It is the least restrictive of SQL Server’s four TILs. It permits dirty reads (reads of uncommitted changes by other transactions) and nonrepeatable reads (data that changes between reads during a transaction). To see how READ UNCOMMITTED permits dirty and nonrepeatable reads, run the following queries simultaneously:

While the first query is running (you have five seconds), fire off the second one, and you’ll see that it’s able to access the uncommitted data modifications of the first query. It then waits for the first transaction to finish and attempts to read the same data again. Since the modifications were rolled back, the data has vanished, leaving the second query with a nonrepeatable read.

READ COMMITTED is SQL Server’s default TIL, so if you don’t specify otherwise, you’ll get READ COMMITTED. READ COMMITTED avoids dirty reads by initiating share locks on accessed data but permits changes to underlying data during the transaction, possibly resulting in nonrepeatable reads and/or phantom data. To see how this works, run the following queries simultaneously:

As in the previous example, start the first query, then quickly run the second one simultaneously (you have five seconds).

In this example, the value of the qty column in the first row of the sales table changes between reads during the first query—a classic nonrepeatable read.

REPEATABLE READ initiates locks to prevent other users from changing the data a transaction accesses but doesn’t prevent new rows from being inserted, possibly resulting in phantom rows appearing between reads during the transaction. Here’s an example (as with the other examples, start the first query; then run the second one simultaneously—you have five seconds to start the second query):

As you can see, a new row appears between the first and second reads of the sales table, even though REPEATABLE READ has been specified. Though REPEATABLE READ prevents changes to data it has already accessed, it doesn’t prevent the addition of new data, thus introducing the possibility of phantom rows.

SERIALIZABLE prevents dirty reads and phantom rows by placing a range lock on the data it accesses. It is the most restrictive of SQL Server’s four TILs. It’s equivalent to using the HOLDLOCK hint with every table a transaction references. Here’s an example (delete the row you added in the previous example before running this code):

In this example, the locks initiated by the SERIALIZABLE isolation level prevent the second query from running until after the first one finishes. While this provides airtight data consistency, it does so at a cost of greatly reduced concurrency.

Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting—0 indicates no nesting, 1 indicates nesting one level deep, and so forth. Batches and stored procedures that are nesting sensitive should query @@TRANCOUNT when first executed and respond accordingly.

Though on the surface it appears otherwise, SQL Server doesn’t support truly nested transactions. A COMMIT issued against any transaction except the outermost one doesn’t commit any changes to disk—it merely decrements the @@TRANCOUNT automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there’s a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn’t be able to reverse those changes since they would already be recorded permanently. Likewise, if ROLLBACK didn’t reverse all changes at all levels, calling it from within stored procedures and triggers would be vastly more complicated since the caller would have to check return values and the transaction nesting level when the routine returned in order to determine whether it needed to roll back pending transactions. Here’s an example that illustrates some of the nuances of nested transactions:

In this example, we see that despite the nested COMMIT TRAN, the outer ROLLBACK still reverses the effects of the DELETE titleauthor command. Here’s another nested transaction example:

In this example, execution never reaches the outer COMMIT TRAN because the ROLLBACK TRAN reverses all transactions currently in progress and sets @@TRANCOUNT to zero.

Note that we can’t ROLLBACK the nested transaction. ROLLBACK can reverse a named transaction only when it’s the outermost transaction. Attempting to roll back our nested transaction yields the message:

The error message notwithstanding, the problem isn’t that no transaction exists with the specified name. It’s that ROLLBACK can reference a transaction by name only when it is also the outermost transaction. Here’s an example that illustrates using ROLLBACK TRAN with transaction names:

Here, we named the outermost transaction “main” and then referenced it by name with ROLLBACK TRAN. Note that a transaction name is never required by ROLLBACK TRAN, regardless of whether the transaction is initiated with a name. For this reason, many developers avoid using transaction names with ROLLBACK altogether, since they serve no real purpose. This is largely a matter of personal choice and works acceptably well either way as long as you understand it. Unless called with a save point (see below), ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT to zero, regardless of the context in which it’s called.

You can control how much work ROLLBACK reverses via the SAVE TRAN command. SAVE TRAN creates a save point to which you can roll back if you wish. Syntactically, you just pass the name of the save point to the ROLLBACK TRAN command. Here’s an example:

As with version 6.5, SQL Server 7.0 allows you to reuse a save point name if you wish, but if you do so, only the last save point is retained. Rolling back using the save point name will roll the transaction back to the save point’s last reference.

Since ROLLBACK TRAN reverses all transactions in progress, it’s important not to inadvertently nest calls to it. Once it’s been called a single time, there’s no need (nor are you allowed) to call it again until a new transaction is initiated. For example, consider this code:

Note the error message that’s generated by the second ROLLBACK TRAN. Since the first ROLLBACK TRAN reverses both transactions, there’s no transaction for the second to reverse. This situation is best handled by querying @@TRANCOUNT first, like this:

Some normally valid Transact-SQL syntax is prohibited while a transaction is active. For example, you can’t use sp_dboption to change database options or call any other stored procedure that modifies the master database from within a transaction. Also, a number of Transact-SQL commands are illegal inside transactions: ALTER DATABASE, DROP DATABASE, RECONFIGURE, BACKUP LOG, DUMP TRANSACTION, RESTORE DATABASE, CREATE DATABASE, LOAD DATABASE, RESTORE LOG, DISK INIT, LOAD TRANSACTION, and UPDATE STATISTICS.