Transactions Locks «Prev  Next»

Lesson 2What is a SQL-Server transaction?
ObjectiveDefine Transactions and how they are used in SQL Server

Define Transactions and how they are used in SQL Server

Purpose of transactions

The concept of transactions is very important in the SQL Server environment. A transaction[1] is a unit of work in which all Transact-SQL statements contained within that unit are treated as a whole.
Any Transact-SQL statements in a transaction that fails will cause the entire transaction to fail. As a result, transactions protect against a statement modifying data after a previous statement has failed.

Three Concepts Relating to Transactions

It is important to understand the following three concepts relating to transactions:
  1. Begin Transaction: Begins the definition of a unit of work.
  2. Commit Transaction:Ends the definition of a unit of work and applies all Transact-SQL statements that are executed between the Begin Transaction statement and the Commit Transaction statement.
  3. Rollback Transaction:Ends the definition of a unit of work and cancels all Transact-SQL statements that are executed between the Begin Transaction statement and the Rollback Transaction statement.

ACID test for Transactions

For a single unit of work to be a transaction, it must pass the ACID test.
ACID stands for Atomicity, Consistency, Isolation, and Durability.
  1. Atomicity - Either every statement is committed or none of them are committed.
  2. Consistency - Data integrity must be enforced to ensure the Transact-SQL statements wrapped within the transaction have all rules applied to them.
  3. Isolation - Concurrent transactions either read the data at the beginning or the end of the transaction, but nowhere in between. This is controlled by isolation level, as discussed in a later lesson in this module.
  4. Durability - When a transaction is completed, the effects on the data are made permanent in the database.
The Transact-SQL statements that are contained between the Begin Transaction and Commit Transaction statements are ‘wrapped in a transaction.’ These are considered to be the complete unit of work.

ACID is an acronym for Atomicity, Consistency Isolation, Durability. ACID is a concept that database admins look for when evaluating database and application architectures A robust database should possess these four attributes. Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in an incomplete state.
Isolation keeps transactions separated from each other until they are finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. The diagram below summarizes the four components of an ACID transaction.

ACID Components

When you design any system or database, make sure you select the database which contains these 4 components as that will help you better develop applications for your business.
Historical Note Andreas Reuter and Theo Hoerder created the acronym ACID in the year 1983. SQL Server, Oracle, MySQL, PostgreSQL are some of the databases which follows ACID properties by default.

[1]Transaction: A set of SQL commands that are grouped together and either all succeed or all fail as a unit.