Microsoft SQL Server 2012 Bible

Programming Transactions

A transaction is a sequence of tasks that together constitute a logical unit of work. All the tasks must complete or fail as a single unit, and the transaction must adhere to the ACID properties just outlined. For example, for an inventory movement transaction that reduces the inventory count from one bin and increases the inventory count in another bin, both updates must be committed together. If this did not occur, the reliability and usefulness of database management systems would be negligible.

In SQL Server, every DML operation (Select, Insert, Update, Delete, and Merge) is a transaction, whether or not it has been executed within a begin transaction. For example, a single insert command that inserts 25 rows is a logical unit of work. All the 25 rows must be inserted together or none of them are inserted. An update to a single row operates within a transaction so that the row in the clustered index (or heap) and the row's data in every nonclustered index are updated. Any partially competed transaction would violate transactional integrity.

Logical Transactions

If the logical unit of work involves multiple operations, some code is needed to define the bounds of the transaction: two statements — one at the beginning of the transaction and the other at its completion, at which time the transaction is committed to stable media. If the code detects an error, the entire transaction can be rolled back, or undone. The following three commands appear simple, ...