What Is a Transaction?

A transaction is one or more SQL statements that must be completed as a whole, or in other words, as a single Logical Unit of Work (LUW). Transactions provide a way of collecting and associating multiple actions into a single all-or-nothing multiple operation action. All operations within the transaction must be fully completed or not performed at all.

Consider a bank transaction in which you move $1,000 from your checking to your savings account. This transaction is, in fact, two operations: a decrement of your checking account and an increment of your savings account. Consider the impact on your finances if the bank's server went down after completing the first stage and never got to the second! By collecting the two operations together, as a transaction, they either both succeed or both fail as a single, complete unit of work.

A transaction is a logical unit of work that has four special characteristics, known as the ACID properties:

  • Atomicity?Associated modifications are an all-or-nothing proposition; either all are done or none are done.

  • Consistency?After a transaction finishes, all data is in the state it should be, all internal structures are correct, and everything accurately reflects the transaction that has occurred.

  • Isolation?One transaction cannot interfere with the processes of another transaction.

  • Durability?After the transaction has finished, all changes made are permanent.

The responsibility for enforcing the ACID properties of a transaction is split between T-SQL developers and SQL Server. The developer is responsible for ensuring that the modifications are correctly collected together and that the data is going to be left in a consistent state that corresponds with the actions being taken. SQL Server ensures that the transaction is isolated and durable, undertakes the atomicity requested, and ensures the consistency of the final data structures. The transaction log of each database provides the durability for the transaction. As you will see in this chapter, you have some control over how SQL Server handles some of these properties. For example, you can modify a transaction's isolation by enlisting bound connections.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features