SQL Transaction Overview

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 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.

Transaction commands

The transaction commands are only used in SQL DML language 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.

The transaction commands are given below:

• COMMIT: This command is used to save the changes invoked by transaction.
  
  
• ROLLBACK: This command is used to undo the changes made by transaction.
  
  
• SAVEPOINT: With the help of this command you can roll the transaction back to a certain point without rolling back the entire transaction.
  
  
• 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' ] ]
   ]
[ ; ]

Example 1: 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

Example 2: 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

Example 3: 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
GO

Transaction with ADO.NET

The following example describes how SQL transaction is used with ADO.NET.

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

Hope you must got come information about SqlTansaction with example in SQL and ADO.NET.