SQL Transaction Overview

Introduction

This article describes about and demonstrates the SQL Transaction, use of Transaction with example any many more. It Describes details about the property and commands of SQL Transaction and demonstrates how to use in SQL and as well as in C# with ado.net.

Getting Started

Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

It is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors or rolled back, then all of the data modifications are erased.

Properties of SQL Transaction

1. Atomicity
   

   A transaction consists of many steps. When all the steps in a transaction gets completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
   

2. Consistency
   

   The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
   

3. Isolation
   

   Every transaction should operate as if it is the only transaction in the system.
   

4. Durability
   

   Once a transaction completed successfully, the updated rows/records must be available for all other transactions on a permanent basis
   

Commands SQL Transaction

The transaction commands are only used in SQL DML languages like INSERT, UPDATE, and DELETE, you cannot use it with DDL or DCL language as these DDL and DCL languages are used to in creating structure and SQL security. There are four types of command described below.

1. COMMIT
   

   This command is used to save the changes invoked by transaction.
   

2. ROLLBACK
   

   This command is used to undo the changes made by transaction.
   

3. SAVEPOINT
   

   With the help of this command you can roll the transaction back to a certain point without rolling back the entire transaction.
   

4. SET TRANSACTION
   

   This command is used to specify characteristics for the transaction. For example, you can specify a transaction to be read only, or read write it. Also helps set the name of transaction.
   

Syntax

 BEGIN { TRAN | TRANSACTION }   
   [ { transaction_name | @tran_name_variable }   
   [ WITH MARK [ 'description' ] ]   
   ]   
 [ ; ]   

SQL Example: Simple Transaction

 DECLARE @TranName VARCHAR(20);   
 SELECT @TranName = 'MyTransaction';   
 BEGIN TRANSACTION @TranName;   
 USE AdventureWorks2012;   
 DELETE FROM AdventureWorks2012.HumanResources.JobCandidate   
   WHERE JobCandidateID = 13;   
 COMMIT TRANSACTION @TranName;   
 GO   

SQL Example: Transaction with rollback.

 BEGIN TRAN    
   UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176'    
   UPDATE authors SET au_fname = 'JohnY' WHERE city = 'Lawrence'    
   IF @@ROWCOUNT = 5    
    COMMIT TRAN    
   ELSE    
    ROLLBACK   
 END  

SQL Example: Transaction in Procedure.

 CREATE PROCEDURE TranTest2   
 AS   
 BEGIN TRAN   
   INSERT INTO[authors]([au_id], [au_lname], [au_fname], [phone], [contract])   
   VALUES('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1)   
   IF@@ ERROR < > 0   
 BEGIN   
   ROLLBACK TRAN   
 END   
 UPDATE authors SET au_fname = 'Johnzzz'   
 WHERE au_id = '172-32-1176'   
 IF@@ ERROR < > 0   
 BEGIN   
   ROLLBACK TRAN   
 END   
 COMMIT TRAN   
 END  
 GO   

ADO. NET Example

 SqlConnection sqlConnection db = new SqlConnection("ConnectionString");    
 SqlTransaction transaction;    
 sqlConnection.Open();    
 transaction = sqlConnection.BeginTransaction();    
 try     
 {    
   new SqlCommand("INSERT Qwery1", sqlConnection, transaction)    
    .ExecuteNonQuery();    
   new SqlCommand("INSERT Qwery2 ", sqlConnection, transaction)    
    .ExecuteNonQuery();    
   new SqlCommand("INSERT Qwery3 ", sqlConnection, transaction)    
    .ExecuteNonQuery();    
   transaction.Commit();    
 }     
 catch (SqlException sqlError)     
 {    
   transaction.Rollback();    
 }    
 sqlConnection.Close();   

In the above example, we first opened connection with SQL Database then created object of SqlTransaction class. Secondly, kept the reference of SqlTransaction with this transaction object by calling SQL Begin Transaction method.

Within the try blockexecuted three SQL commands, if no error occurs the transaction will be committed other than the catch block rolled back the transaction. Finally, database connection is closed.

Summary

In the above of this article we have seen about SQL Transaction, different property of transaction, command of transaction and how to use SQL Transaction in SQL as well as in C# with ado.net. Hope you have got the clearly about the above discussion.

Thanks