Transactions in T-SQL

Transaction is a logical unit which contains set of statements. Transactions are made in terms of following ACID properties in database.

ACID properties is one of major requirement in database to avoid deadlocks. Atomicity, Consistency, Isolation and Durability.

• Atomicity: All T-SQL statements must be executed all together or nothing should be executed inside a transaction.
• Consistency: All transactions must leave the data in a consistent state after execution.
• Isolation: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. ie one transaction should not modify the same data while the other transaction is still running on it.
• Durability: If a transaction is committed it must be committed on permanent basis. Once committed it persists. 

Types of Transactions in T-SQL

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

Actions performed on Explicit Transactions

• BEGIN TRANSACTION: Marks the starting point of an explicit transaction for a connection.
• COMMIT TRANSACTION: Used to end a transaction successfully if no errors where encountered. All DML changes made in the transaction become permanent.
• ROLLBACK TRANSACTION: Used to erase a transaction in which errors are encountered.All DML changes made in the transaction are undone.