What is a Transaction?

A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.

Transaction processing follows these steps:
1. Begin a transaction.
2. Process database commands.
3. Check for errors. 
   If errors occurred, 
       rollback the transaction, 
   else, 
       commit the transaction

A successful transaction must pass the "ACID" test, that is, it must be
A - Atomic
C - Consistent
I - Isolated
D - Durable

Atomic - All statements in the transaction either completed successfully or they were all rolled back. The task that the set of operations represents is either accomplished or not, but in any case not left half-done. For example, in the spUpdateInventory_and_Sell stored procedure, both the UPDATE statements, should succeed. If one UPDATE statement succeeds and the other UPDATE statement fails, the database should undo the change made by the first UPDATE statement, by rolling it back. In short, the transaction should be ATOMIC.

Consistent - All data touched by the transaction is left in a logically consistent state. For example, if stock available numbers are decremented from tblProductTable, then, there has to be a related entry in tblProductSales table. The inventory can't just disappear.

Isolated - The transaction must affect data without interfering with other concurrent transactions, or being interfered with by them. This prevents transactions from making changes to data based on uncommitted information, for example changes to a record that are subsequently rolled back. Most databases use locking to maintain transaction isolation.

Durable - Once a change is made, it is permanent. If a system error or power failure occurs before a set of commands is complete, those commands are undone and the data is restored to its original state once the system begins running again



Transaction states

The states of the transaction can be summarized as follows:

  • The running transaction is referred to as the Active transaction
  • The transaction that completes its execution successfully without any error is referred to as a Committed transaction
  • The transaction that does not complete it is execution successfully is referred to as an Aborted transaction
  • The transaction that is not fully committed yet is referred to as a Partially Committed transaction
  • If the transaction does not complete its execution, it is referred to as a Failed transaction, that is Aborted without being committed
  • If the Partially Committed transaction completes its execution successfully, it will be Committed, otherwise it will be Failed then Aborted


T-SQL Statements Allowed in a Transaction

 
You can use all T-SQL statements in a transaction, except for the following statements: ALTER DATABASE, RECONFIGURE, BACKUP, RESTORE, CREATE DATABASE, UPDATE STATISTICS, and DROP DATABASE.