SQL Server Transaction

An Introduction to Transaction

In this post we will learn about transaction in SQL language. Transaction is fundamental concept in any modern database programming. Transaction is basically a unit of work where work implies one or more SQL statements for some operation. It means that all statements of transaction must succeed for success of work. If any statement in transaction throws error then all statements will be rolled back.

A SQL statement represents an action. A single statement or more than one statement can represent a transaction or unit of work. Transaction means commit all actions of a unit of work else rollback all actions.

Examples

• Suppose that email ID of a person in bank is to be updated. To perform this operation, only one table i.e. personal details table of users will be updated.
• Consider another example in which a person A transfers some money to person B. In this case two bank accounts or tables will be affected. The transaction will not be complete unless the account of A is debited and account of B is credited. There are two actions involved here; debiting account A and crediting account B.
• Consider third example in which a sales person sells some product to a customer. In this case multiple tables will be affected e.g. product, customer, salesperson and order tables. Unless all these tables are not properly updated business status will not be okay. When all of these tables will be updated, transaction will be complete and business will reach from one consistent state to another consistent

Thus, how any actions together will represent a transaction is purely based on business requirement. Each action represents a SQL statement.

Definition: A collection of actions fulfilling a business requirement is called transaction.

We look at the following T-SQL example to understand transaction.

Example

First of all, we create a table for this example and insert some records into it.

use AppliedkDb
Go
Create table tblEmployee_Transaction
(
Emp_Id int
,Emp_Name varchar(10)
,Grade varchar(10)
,Salary int check(Salary<7000)
)
go
Insert into tblEmployee_Transaction values (1, 'Paul',  'Grade1', 6000)
Insert into tblEmployee_Transaction values  (2, 'Tom',   'Grade2', 2300)
Insert into tblEmployee_Transaction values  (3, 'Haris', 'Grade1', 5000)
go

Now table is ready to understand about transaction. By default, SQL Server database engine treats each SQL statement as a unit of work i.e. transaction. So, CREATE TABLE statement is a transaction. Inserting a row into table is a transaction. Similarly a UPDATE statement is a transaction.

update tblEmployee_Transaction
set Grade='Grade1',Salary=9000
where Emp_id=2
go
select * from tblEmployee_Transaction

In above example we use a single update statement to update both grade and salary of an employee. Both updating grade and salary together is taken as a transaction or unit of work in this example. In table definition, the salary constraint is that salary must be below 7000. So update statement to update salary to 9000 fails and the statement is roll backed. Neither grade nor salary is updated because of salary constraint violation.

NOTE: There are two possible cases for a transaction. Either it will be committed or roll backed. Commit means all the actions of transaction succeeds. If any action fails then transaction cannot be committed. Rollback means all the actions of transaction are cancelled.

Now we look at the following UPDATE situation.

update tblEmployee_Transaction
set Salary=9000
where Emp_id=2
update tblEmployee_Transaction
set Grade='Grade1'
where Emp_id=2
go
select * from tblEmployee_Transaction

In the above example, there are two update statements. Each statement represents a transaction. The transaction to update grade will succeed but the transaction to update salary will not succeed because of salary constraint violation.

Now the question is how to treat the two statements as a unit of single transaction. We use BEGIN TRANSACTION statement to create an explicit transaction. The statements following BEGIN TRANSACTION statement will be part of the transaction.

Syntax

Look at the following example.

BEGIN TRANSACTION 
Update tblEmployee_Transaction
set Salary=9000
where Emp_id=2
update tblEmployee_Transaction
set Grade='Grade1'
where Emp_id=2
go
select * from tblEmployee_Transaction

We have not used COMMIT TRANSACTION or COMMIT TRANSACTION statement at the end. So, the SQL engine takes the decision on its own. The first statement is roll backed but second statement is committed. So, we find that grade is updated while salary is not.

Note that COMMIT TRANSACTION does not guarantee that the transaction will be committed. A transaction is committed only when no constraint or rule is violated. For example, the following commit will not work because salary constraint is violated. You can rollback a transaction even if all its statements succeed.

BEGIN TRANSACTION 
update tblEmployee_Transaction
set Salary=9000
where Emp_id=2
COMMIT TRANSACTION
Go

Next look at the following example:

BEGIN TRANSACTION 
update tblEmployee_Transaction
set Salary=9000
where Emp_id=2
update tblEmployee_Transaction
set Grade='Grade1'
where Emp_id=2
If @@ERROR>0
	ROLLBACK TRANSACTION
Else
	COMMIT TRANSACTION
Go
Select * from tblEmployee_Transaction