O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required