Transaction Explained in SQL Server

Introduction 

In this blog, I will explain the SQL Transaction. A transaction is a logical unit of work. Each transaction begins with a specific task and ends with all tasks in a group successfully complete. If any tasks fail, it means that the transaction fails. All steps must be committed (transaction Success) or rolled back (transaction failure). A transaction begins to initiate the execution of the SQL statement. A transaction must be committed or rolled back. It is separate operations succeed is transaction succeed and committed to the database. If any separate operation fails means transaction failure and must be undone rolled back. The following are the properties of a transaction.
 
1. Atomicity
2. Consistency
3. Isolation
4. Durability

Transaction Process

BEGIN TRANSACTION - Starts the transaction
ROLLBACK - If an error occurred, reverts the existing transaction changes
COMMIT - No error occurred, then it saves all transaction states
SAVEPOINT - Rollback particular named transaction

Example

  1. BEGIN TRANSACTION T1  
  2. UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1  
  3. COMMIT;  
  4.   
  5. BEGIN TRANSACTION T1  
  6. UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1  
  7. ROLLBACK;  
  8.   
  9. SAVEPOINT T1  
  10. UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1  
  11. ROLLBACK T1;