Transaction Management in SQL

A transaction is a unit of work performed against the database. It is a set of work (T-SQL statements) that are executed together such as a single unit in a specific logical order as a single unit.

Transaction Management in SQL

If statements are executed successfully then the transaction is complete and then it is committed that saves the data in the database permanently. If any single statement fails then the entire transaction will fail and the complete transaction will be cancelled or rolled back. When a transaction starts, it locks all the table data that is used in the transaction. Hence during the transaction life cycle no one can modify this table data used by the transaction such that the the integrity of the data for the transaction is maintained.

A transaction is used when more than one table or view related to each other at a time are affected. The main goal of a transaction is for either all operations will be done or nothing will be done. We can compare a transaction with a digital circuit that works on 0 and 1. Here:

  • 1 indicates completeness of all tasks (T-SQL statements)
  • 0 indicates no single tasks performed (T-SQL statements)

Example

A transaction is mainly used in banking or the transaction sector.

Let us see an example of a bank that has two customers, Cust_A and Cust_B. In case Cust_A wants to transfer some money to Cust_B, then there are the following 3 possibilities:

  1. Debiting from the Cust_A account is performed successfully and crediting in the Cust_B account is performed successfully.

  2. Neither debiting from the Cust_A account is performed nor crediting in the Cust_B account is performed.

  3. Debiting from the Cust_A account is performed successfully, but crediting in the Cust_B account is not performed.

The first condition indicates a successful transaction and the second condition is not so critical. We are not required to do any retransmission, but the third condition will create a problem if, due to a technical problem, the first operation is successful but the second one fails. The result here would be that the Cust_A account will be debited, but the Cust_B account will not be credited. This means that we will lose the information.

For overcoming all these problems we can use transaction management. A transaction ensures that either a debit or a credit will be be done or nothing will be done.

Now we will explain what “Transaction Management “ is and how it works.

A transaction mainly consists of 4 properties that are also known as ACID rules.

Atomicity: Atomic means that all the work in the transaction is treated as a single unit. Either it is performed completely or none of it is and at the point of failure the previous operations are rolled back to their former state.

Consistency: Transactions ensure that the database properly changes states upon a successfully committed transaction. In other words, if a transaction completes successfully then the database should be in a new state that will reflect changes else the transaction remains in the same state as at an initial point.

Isolation: It ensures that transactions operate independently and are transparent to each other. In other words, if more than one transections are running then they do not effect each other.

Durability: It ensures that the effect of committed transactions will save in the database permanently and should persist no matter what happens (like in a power failure).

Types of Transactions

In SQL, transactions are of the following two types:

  1. Implicit Transections
  2. Explicit Transections

Implicit Transactions

Implicit transactions in the SQL language are performed by a DML query (insert, update and delete) and DDL query (alter, drop, truncate and create) statements. All these queries are handled by Implicit Transactions.

Implicit Transections

When any DDL or DML query is performed then the system stores the information of all the operations in the log file. If any error occurs then the SQL Server will rollback the complete statement.

Example

Example

In the preceding table we have the following 5 columns:

Emp_Id datatype is Int, Emo_Name nvarchar(50) , Emp_Age int, Emp_Salary int, Emp_City int.

Now we will try to insert some values:

  1. insert into Employee_Detail values(11,'Namo1',32,25000,'Delhi')  
  2. insert into Employee_Detail values(12,'Namo2',32,25000,'Delhi')  
  3. insert into Employee_Detail values(13,'Namo3','32,25000','Delhi') /* Here Error Will Occur */  
  4.   
  5.   
  6. insert into Employee_Detail values(14,'Namo4',32,25000,'Delhi')  
  7. insert into Employee_Detail values(15,'Namo5',32,25000,'Delhi')  
Output

Msg 213, Level 16, State 1, Line 3

Column name or number of supplied values does not match the table definition.

Now check the data of the table:
  1. select * from Employee_Detail  
Output

Output

As we can see, in the preceding query the first two insertion queries worked correctly, but in the third statement an error occured. So a Transaction Rollback will be performed and the table will be restored to its initial state when the transaction was initiated.

It shows each DML and DDL query with the Transaction Control Mechanism.

Explicit Transactions

An explicit transaction is defined and controlled by the user on a DML query (insert, update or delete). A transaction is not applied on a SELECT command because is doesn't affect the data. A transaction is not used in creating tables or dropping them because these operations are automatically committed in the database.

Transaction Control

The following commands are used in the transaction control mechanism.

Transaction Control

 

  • BEGIN: To initiate a transaction.
  • COMMIT: To save changes. After the commit command, the transaction can't rollback.
  • SAVEPOINT: Provides points where the transaction can rollback to.
  • ROLLBACK: To rollback to a previous saved state.

Syntax of Transaction

Begin {Transaction| Tran }[ Transaction_Name |@Trans_Name]

Write Code Here

End


Here

  • Begin: Initiate transaction.
  • Transaction| Tran: We can use any one out of both.
  • Transaction_Name: Used for providing a name for a transaction.
  • @Trans_Name: This is the name of a user-defined variable containing a valid transaction name.
  • End: Indicates the end of the transaction.

Now we will see some examples of Transaction Control Mechanisms.

First, create a table:

  1. Create Table Student  
  2. (  
  3. IId int Not Null primary key,  
  4. Name Nvarchar(MAXNot NUll,  
  5. Age Int Not Null,  
  6. Class int not Null  
  7. )  
Now insert some values into the table:
  1. Insert Into Student  
  2. Select 1,'A',12,10 Union All  
  3. Select 2,'B',16,11 Union All  
  4. Select 3,'C',15,9 Union All  
  5. Select 4,'D',13,12 Union All  
  6. Select 5,'E',14,11 Union All  
  7. Select 6,'F',17,8 Union All  
  8. Select 7,'G',12,7 Union All  
  9. Select 8,'H',17,12  
Now the table will look like the following:

Value Into Table

We will use the preceding table in the following example.

Example 1
  1. Begin Transaction My_Trans  
  2. Delete from Student Where IId=3  
  3.  Begin Rollback Transaction My_Trans  
  4. End   
  5. Select * From Student  
Output

table


In the preceding example we deleted a row from the table and then performed a rollback. Now we can see that there is no change in the table because we performed a rollback that transfers the table in the previous stable (saved) state.

Example 2
  1. Begin Transaction My_Trans  
  2. Delete from Student Where IId=4  
  3. Commit Transaction My_Trans  
  4.   
  5. Begin Transaction My_Trans  
  6. Rollback Transaction My_Trans   
  7.   
  8.  SELECT * FROM Student  
Output

Commit Transaction
In this example we deleted a row from the table and then performed a “commit” operation. The result of this operation saves all the changes that are performed. Now if we do a “Rollback” then the transaction will not be returned to its starting state because we commit (save) all the changes.

Example 3
  1. Begin Transaction My_Trans  
  2.   
  3. Delete from Student Where IId=3  
  4. save Transaction My_Save1  
  5.    
  6. Delete from Student Where IId=4  
  7. save Transaction My_Save2  
  8.    
  9. Delete from Student Where IId=5  
  10. save Transaction My_Save3  
  11.    
  12. Begin Transaction My_Trans  
  13. Rollback Transaction My_Save2   
  14.   
  15.  SELECT * FROM Student  
Output

Student
In a transaction we can create some savepoints that saves the current state of the database. Then we can rollback to any specific savepoint. In the preceding example we performed 3 delete operations and after each delete operation we created a save point and finally we did a rollback at the save point ”My_Save2”. So, the operations performed after “My_save2” will be discarded.

Example 4
  1. Begin Transaction Trans  
  2. Begin Try  
  3. Delete From Student Where Student.IId=3;  
  4. Update Student Set Student.Name='Pankaj' ,Student.Class=6 Where Student.IId=6  
  5. If @@TranCount>0  
  6. begin Commit Transaction Trans  
  7. End  
  8. End Try  
  9. Begin Catch  
  10. if  @@TranCount>0  
  11. Print 'Error Is Occur in Transaction'  
  12. begin Rollback Transaction Trans  
  13. End  
  14. End Catch  
  15.   
  16. Select * From Student  
Output

Begin Catch

In this example we used a new concept of @@TRANCOUNT. @@TRANCOUNT returns the number of active transactions for the current connection. The starting value of @@TRANCOUNT is zero. When a new transaction begins, it increase its value by 1. When the commit statement decreases its value by 1 and the rollback statement decreases the value of @@TRANCOUNT to 0, the savepoint statement doesn't affect the value of @@TRANCOUNT.

Let us see an example:
  1. Print @@Trancount  
  2. Begin Transaction  
  3. Print @@Trancount  
  4. Begin Transaction  
  5. Print @@Trancount  
  6. Commit Transaction  
  7. Print @@Trancount  
  8. Rollback Transaction  
  9. Print @@Trancount  
Output

0
1
2
1
0

The following describes when to use @@TRANCOUNT:
  1. In the case of exception handling and nested transactions.
  2. The current transaction was called by some .NET code with its own transaction.
  3. The current transaction was called from another Stored Procedure that had its own transaction.

The following is another example:

  1. Begin Transaction Trans  
  2. Begin Try  
  3. Delete From Student Where Student.IId=3;  
  4. Update Student Set Student.Name=12121 ,Student.Class='12th' Where Student.IId=6   /* Error Will Occur Here */  
  5. If @@TranCount>0  
  6. begin Commit Transaction Trans  
  7. End  
  8. End Try  
  9. Begin Catch  
  10. if  @@TranCount>0  
  11. Print 'Error Is Occur in Transaction'  
  12. begin Rollback Transaction Trans  
  13. End  
  14. End Catch  
Output

(0 row(s) affected)

(0 row(s) affected)
Error Is Occur in Transaction

Example 5
  1. Begin Transaction Trans  
  2. Begin Try  
  3. Update Student Set Student.Name='Pankaj'  Where Student.Class=11    
  4. If @@ROWCOUNT=2  
  5. Print 'Number of Rows affected is 2, so rollback occurs'  
  6. begin RollBack Transaction Trans  
  7. End  
  8. End Try  
  9. Begin Catch  
  10. if  @@ROWCOUNT=2  
  11. Print 'Error Is Occur in Transaction'  
  12. begin Rollback Transaction Trans  
  13. End  
  14. End Catch  
Output

(2 row(s) affected)
Number of rows affected is 2, so rollback occurs.

@@ROWCOUNT is another important factor used in transactions. It returns the number of rows affected during the transaction. Using the value of @@ROWCOUNT we can use a proper action. In the preceding example the update query updates two rows, so the value of @@ROWCOUNT is 2 and we did a rollback.

Example 6
  1. Create Procedure  My_Proc__  
  2. AS  
  3. Begin Transaction  
  4. Delete From Student Where IId=3  
  5. Delete From Student Where IId='123'  /* Error Occur Here  */  
  6. Delete From Student Where IId=4  
  7.   
  8. Commit Transaction  
  9. Go  
  10. EXEC My_Proc__  
  11. Go  
  12. Select * From Student  
Output

Procedure

This example shows that the transaction is not done correctly with the Stored Procedure. The problem with this Stored Procedure is that the transactions don't care if the statements run correctly or not. They only care is if SQL Server fails in the middle. In the preceding example if an error occurs then the transaction is still going on. So, it is our responsibility to check for an error after each step in the Stored Procedure. Now we will see how to do this.
  1. Create Procedure  My_Proc_  
  2. AS  
  3. Begin Transaction My_Trans  
  4. Insert Into Student Values(9,'1',50,11)  
  5.   
  6. If @@ERROR <>0  
  7. Begin  
  8.  Rollback Transaction My_Trans  
  9. Return 4  
  10. End   
  11. Insert Into Student Values(9,'J',15,11)  /* Error Occur Here  */  
  12. If @@ERROR  <>0  
  13. Begin   
  14. Rollback Transaction My_Trans  
  15. Return 4  
  16. End   
  17. Insert Into Student Values(11,'K',50,11)  
  18. If @@ERROR <>0  
  19. Begin  
  20.  Rollback Transaction My_Trans  
  21. Return 4  
  22. End   
  23. Commit Transaction My_Trans  
  24. Go  
  25. EXEC My_Proc_  
  26. Go  
  27. Select * From Student  
Output

Student Values

In the preceding example the first insert query executes with no error, but in the second insert query an error will occur, violation of “PRIMARY KEY constraint 'PK__Student__C4972BAC3D5E1FD2'. Cannot insert duplicate key in object 'dbo.Student'. The duplicate key value is (9).” So the value of @@ERROR is not equal to 0 if the condition becomes true, so a rollback will be done. The @@ERROR system function returns 0 if the last Transact-SQL statement executed successfully; if the statement generated an error, @@ERROR returns the error number.

Example 7
  1. Declare @Trans_Name nvarchar(50)  
  2. Set @Trans_Name='My_Trans'  
  3. Begin Transaction @Trans_Name  
  4. Delete from Student Where IId=4  
  5. Commit Transaction @Trans_Name  
  6.   
  7. Begin Transaction @Trans_Name  
  8. Rollback Transaction @Trans_Name   
  9.   
  10.  SELECT * FROM Student  
In this article we defined the syntax of the transaction that shows that we can provide the name of a transaction using a user-defined variable.

@Trans_Name: This is the name of a user-defined variable containing a valid transaction name.

Here the user-defined variable @Trans_Name contains the name of the transaction.

Output

contain