SQL Server Transaction Statements

Different Transaction Statements

Now you look at practical steps to carry out transaction in SQL. There are four statements used to manage transactions which are as follows:

• BEGIN TRANSACTION
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION
• SAVE TRANSACTION 

Two global variables @@ERROR and @@TRANCOUNT are important in the context of transactions.

1. @@ERROR
2. @@TRANCOUNT

A transaction may throw error. The @@ERROR variable stores information about the most recent error number. If there is no error then its value is 0. Its value is reset to 0 if statement executes successfully. So, it is important to save its value in some variable for later use.

The @@TRANCOUNT variable stores the number of transactions in the current connection. It is important if we are using nested transactions. 

BEGIN TRANSACTION statement

A transaction can be given a name. After BEGIN TRANSACTION clause we can write either transaction name or a variable which holds the transaction name. 

BEGIN TRANSACTION empTrans WITH MARK 'Description for logging transaction plus current time'

The WITH MARK clause is used to place some marker in the transaction log. Marker is basically a description plus current time at which the transaction was carried out. This is basically used to use restore command to restore the database to the point of time the marker is describing to recover from a problem.

Look at the following log file path.

If file is in use, you cannot open log file.

Nested transaction

At transaction can be nested. The outer transaction will control the inner transactions. It means that if the outer transaction is roll backed then inner transactions will be rolled back; no matter inner transactions are committed or roll backed. 

• There is cascading effect of rollback of outer transaction upon its inner transactions.
• We must commit or roll back inner transactions before committing or rolling back the outer transaction.
• Committing inner transaction does not change the database. It merely passes this change to outer transaction.

Example of Nested Transaction

Look at the following nested transactions. T1 and T2 are two transactions where T2 is nested within T1. Note that outer transaction is still open when inner transaction is committed or roll backed. To count the total number of open transactions we use system global variable @@TRANCOUNT.

BEGIN TRANSACTION; -- T1
SELECT @@TRANCOUNT AS 'T1 Begins, Transactions Count'
    BEGIN TRANSACTION; -- T2
    SELECT @@TRANCOUNT AS 'T2 Begins, Transactions Count'
    COMMIT; -- T2 is committed, but T1 is still open
    SELECT @@TRANCOUNT AS 'T2 Ends, Transactions Count'
COMMIT; -- T1 is committed, no open transactions remaining
SELECT @@TRANCOUNT AS 'T1 Ends, Transactions Count'

Note that @@TRANCOUNT is a system function in T-SQL that returns the current nesting level of open transactions for the current session. It indicates how many transactions are currently active and provides a way to determine if the code is currently executing within a transaction. 

Example of Nested Transaction

Now we see a practical example of nested transaction. For this we use employees table.

CREATE TABLE dbo.Employees(
	Id int ,
	first_name varchar(10) ,
	last_name varchar(10) 
)
GO
BEGIN TRANSACTION
INSERT INTO Employees VALUES (1, 'Amit', 'Kumar')
INSERT INTO Employees VALUES (3, 'Ritu', 'Sinha') 
	BEGIN TRANSACTION
	INSERT INTO Employees VALUES (5, 'Rohit', 'Sinha')
	COMMIT TRANSACTION
ROLLBACK TRANSACTION
GO
SELECT * FROM Employees

In the above example, although inner transaction is committed, yet it is roll backed because outer transaction is roll backed.

Look at the following and find out the cause of error message “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

BEGIN TRANSACTION
INSERT INTO Employees VALUES (1, 'Amit', 'Kumar')
INSERT INTO Employees VALUES (3, 'Ritu', 'Sinha') 
	BEGIN TRANSACTION
	INSERT INTO Employees VALUES (5, 'Rohit', 'Sinha')
	ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO

Corrected
BEGIN TRY
    BEGIN TRANSACTION;

    -- Outer transaction
    INSERT INTO Employees VALUES (1, 'Amit', 'Kumar');
    INSERT INTO Employees VALUES (3, 'Ritu', 'Sinha');

    -- Begin inner transaction
    BEGIN TRANSACTION;

    -- Inner transaction
    INSERT INTO Employees VALUES (5, 'Rohit', 'Sinha');

    -- Rollback inner transaction if needed (based on some condition)
    IF @@ERROR>0
        ROLLBACK TRANSACTION;
    ELSE
        -- Commit inner transaction
        COMMIT TRANSACTION;

    -- Commit outer transaction
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Handle errors
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Optionally re-throw the error
    THROW;
END CATCH;

Suppose in your code there are a number of transactions then how can you commit one of them? 

COMMIT TRANSACTION statement

COMMIT TRANSACTION statement optionally uses transaction name after the commit transaction clause. Instead of literal name, we can also use variable name that is holding the transaction name after the COMMIT TRANSACTION. This will commit the transaction mentioned after the COMMIT TRANSACTION.

• Naming the transaction name after the commit transaction clause is optional. It is just for readability purpose. 

BEGIN TRANSACTION empTrans WITH MARK 'Description for logging transaction'
update tblEmployee_Transaction
set Salary=9000
where Emp_id=2
update tblEmployee_Transaction
set Grade='Grade1'
where Emp_id=2
if @@TRANCOUNT>0
	rollback 
else
	commit 
go

ROLLBACK TRANSACTION statement

Rollback Transaction means cancelling all the statements of the transaction.

ROLLBACK TRANSACTION statement allows you to roll back the most recent BEGIN TRANSACTION.

Some facts about Rollback Transaction:

• We can optionally write transaction name after rollback transaction.
• ROLLBACK TRANSACTION will always rollback the most recent begin transaction statement.
• ROLLBACK WORK always rollbacks the outermost transaction.

Syntax: ROLLBACK TRANSACTION clause is followed by either transaction name or a variable that holds the name of transaction or a variable that hold the save point or a save point name.

SAVE TRANSACTION statement

A single transaction can be divided into multiple save points so that transaction can be committed up to a save point.  We use save transaction statement followed by the save point name to create a save point inside a transaction. Look at the following pseudo code.

To commit to a particular save point and roll back statements after save point, we use rollback statement followed by save point name.  We can conditionally return to a particular save point by using case expression or if statement.

Save points can be created even within nested transactions. Look at the following example in this regard.

What will happen if two transactions are given same save point?

In this situation the final save point will be considered and earlier save points will be ignored. It means that when we roll back the transaction using this save point then the last save point will be used to rollback and all the statements after that save point will be roll backed.

SAVE TRANSACTION statement allows you partially commit transaction while still being able to rollback the rest of the transaction given after the save point.

When you save a transaction then it must be named. it means that after the SAVE TRANSACTION clause you must have to write its name. By using this name we can roll back the transaction later.

Already we have learnt that are transaction can be nested. To find out the number of nested transaction, you should use system global variable @@TRANCOUNT.