Transactions in SQL Server

This article explains SQL transactions, how to use transaction in SQL? Summary of the article:

• What is SQL Transactions?
• Properties of SQL Transactions
• Transaction Control
• Example of SQL Transactions

What is SQL Transactions?
A transaction groups a set of task into a single execution unit. Every transaction begins with a specific task and ends when all the tasks in the group successfully complete. If the execution of any of the tasks is fails, the transaction is fails. Therefore, we can say a transaction has only two results: success or failure. Any incomplete steps result in the failure of the total transaction.

Properties of SQL Transactions
Every Transaction has the following four standard properties and in short form generally called ACID:

1. Atomicity: ensures that all operations within the work unit are completed successfully.  Otherwise, the transaction is will be failed and previous operations are rolled back to their previous state.
2. Consistency: ensures that the database properly changes its state based on a successfully committed transaction.
3. Isolation: enables transactions to work independently.
4. Durability: ensures that the effect of a committed transaction persist in case of any failure.

Transaction Control
The following commands are used to control transactions:

• Begin Transaction
• Rollback Transaction
• Commit Transaction

Example of SQL Transactions
A sample SQL transactions is given bellow:

BEGIN TRAN
BEGIN TRY 
 --SQL statement
 --SQL statement
 COMMIT TRAN
END TRY 

BEGIN CATCH 
 ROLLBACK TRAN --For Error 
 SELECT 
  ERROR_NUMBER()AS ErrorNumber, 
  ERROR_SEVERITY()AS ErrorSeverity, 
  ERROR_STATE()AS  ErrorState, 
  ERROR_PROCEDURE()AS ErrorProcedure, 
  ERROR_LINE() AS  ErrorLine, 
  ERROR_MESSAGE() AS ErrorMessage; 
END CATCH

That’s all about SQL transaction.