Overview

• A transaction is a single unit of work.
  • If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.
  • If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
• SQL Server operates in the following transaction modes:
  • Autocommit transactions: Each individual statement is a transaction.
  • Explicit transactions: Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
• Implicit transactions: A new transaction is implicitly started when the prior transaction completes.
  • Each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
• Batch-scoped transactions: Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction.
  • A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.
• A transaction is the propagation of one or more changes to the database.

Properties of Transactions

• Transactions have the following four standard properties, usually referred to by the acronym ACID:

1. Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
2. Consistency: ensures that the database properly changes states upon a successfully committed transaction.
3. Isolation: enables transactions to operate independently of and transparent to each other.
4. Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

• Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only.
• They can not be used while creating tables or dropping them because these operations are automatically committed in the database.
• COMMIT: to save the changes.
• ROLLBACK: to rollback the changes.
• SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
• SET TRANSACTION: Places a name on a transaction.

SQL Server Transaction Logs