Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.9.1. Using Transactions to Protect Your Data

A transaction is a group of database statements that are combined into a single unit of work. Transactions are used to ensure multiple statements either succeed or fail as a whole.

The three primary commands used with transactions are

• BEGIN TRANSACTION: The BEGIN TRANSACTION marks the beginning of a transaction. No data modifications issued from this point are committed to the database until a COMMIT TRANSACTION command is issued.

• COMMIT TRANSACTION: The COMMIT TRANSACTION implies that all commands after the BEGIN TRANSACTION command have succeeded and the data needs to be written to the database. Up to this point, the data changes have been logged in to the transaction log but not actually written to the database.

• ROLLBACK TRANSACTION: The ROLLBACK TRANSACTION command causes all data modifications after the BEGIN TRANSACTION command to be undone. They're not written to the database.

Most databases today (including SQL Server 2008) use a transaction log to track changes to the database. Figure 9-1 shows how the transaction log fits into the process. Any data modifications (such as INSERT, UPDATE, and DELETE) are written to the transaction log first, and then written to the database. Normally, data modifications are written to the database almost immediately. When a transaction is used, the modifications aren't written to the database until a COMMIT TRANSACTION is received.

Figure III.9-1. Modifications are recorded in the transaction ...