Transaction in SQL Server

In this article, I am giving a quick overview about Transactions in SQL Server. After completing this article you will understand:

What is Transaction in SQL Server?

What are different TCL commands?

What are different modes of Transactions?

Please give your valuable suggestions and feedback to improve this article.

What is Transaction in SQL Server?

Transaction is a single unit of work; if a Transaction is successful then all of the data modifications made during the transaction are committed and become a permanent part of the database. If a Transaction encounters any errors and must be cancelled or rolled back, then all of the data modification is erased. A Transaction always follows ACID rules. Click here to know about ACID Properties of Transaction.

What are different TCL commands?

To manage the Transaction we have TCL (Transaction Control language) with three commands in it COMMIT, ROLLBACK and Save Transaction. Click here to know about types of SQL Commands.

COMMIT- Commit Marks the end of a successful implicit or explicit transaction. COMMIT Transaction makes all data modifications performed since the start of the transaction a permanent part of the database.

ROLLBACK- Roll back an Explicit or Implicit transaction to the beginning of the transaction or to the Save point inside the transaction. Transaction can ROLLBACK Truncated table data.

Save Transaction- A user can set a save point or marker within a transaction. The save point defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a save point, it must be proceed to completion with more TSQL statement if needed and a COMMIT Transaction statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the ROLLBACK Transaction statement.

What are different modes of Transactions?

There are three modes of Transaction in SQL Server

1.      Auto Commit Transaction

2.      Implicit Transaction – A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK keyword.

3.      Explicit Transaction – Each Transaction is explicitly stared with the Begin Transaction keyword and explicitly ended with a COMMIT or ROLLBACK keyword.

Auto Commit

By default SQL Server uses Auto commit transaction mode which means after executing each statement it will automatically commit it

Implicit Transactions

By default SQL Server uses Auto commit transaction mode but you can change it to Implicit Transaction mode using below query

SET IMPLICIT_TRANSACTONS ON

When SET IMPLICIT_TRANSACTIONS is set to ON, then it sets the connection into Implicit Transaction Mode, but when it is set to OFF, then it returns the connection to auto commit transaction mode. It is to be noted that When Transaction mode is changed to Implicit Transaction mode, it will remain ON only for the same connection it was set to ON.

Let’s understand the Transaction mode using simple examples

As I have already told there are three types of TRANSACTION MODE - Auto commit, IMPLICIT and EXPLICIT. By default in SQL Server Transaction mode is auto commit. But you can change to IMPLICIT Transaction mode using SET IMPLICIT_TRANSACTIONS command. Once you change it to IMPLICIT mode, Transaction mode will be changed to IMPLICIT mode but only for that particular connection. When you open another connection for that new connection TRANSACTION mode will be auto commit.

Let’s open two connections and in first connection we will SET IMPLICIT_TRANSACTIONS ON and in second connection we haven’t SET it to ON.

In first connection I am creating EMP table and in Second connection I am creating DEP table. From the below Image you can clearly see. I ran both the connection’s command and it ran successfully.

As I have already told in my beginning that if Transaction mode is set to IMPLICIT then we have to end the transaction by COMMIT or ROLLBACK but in my first connection I have not done that.  Now let’s close the first connection.

As you can see from the above Image when I tried to close the connection I got a pop up message, which is saying I have not committed the transaction. If I will click on Yes then it will commit the Transaction and EMP table will be created in the database for permanently. If I click on NO then Transaction will be ROLLED BACK and EMP table will not be created in TEACHMESQLSERVER database. Just for testing purpose I am clicking on NO.

Now let’s try to close the second connection, you can clearly see from the below Image when I try to close second connection it is not asking whether I want to commit or not.  It is asking if I want to save my SQL command then I can save it. I am not saving the SQL command and clicking on NO.

Now again I create a new connection, let’s see how many table our TEACHMESQLSERVER database contains. As you can see from the below result, it say only DEP table is present in our database. This DEP table was created by the second connection. EMP table is not present in our database which was created by first connection. Even after successful execution EMP table is not present in the database it is because when we closed the First connection we clicked on NO. It didn't commit but it rolled back the transaction. 

Explicit Transactions

Explicit Transaction start with BEGIN TRAN or BEGIN TRANSACTION keyword then it contains TSQL statements and ends with COMMIT or ROLLBACK keywords.

Syntax

BEGIN TRANSACTION

SQL Statement

COMMIT | ROLLBACK

Let’s have one-one example for both COMMIT and ROLLBACK.

We are inserting a record in DEP table and committing our transaction

USE TEACHMESQLSERVER

GO

BEGIN TRANSACTION

INSERT INTO DEP VALUES(1)

COMMIT

GO

SELECT * FROM DEP

As you can see this records becomes permanent and stored in DEP table because we COMMITTED our transaction.

In this below query we are inserting another record in DEP table but we are rolling back our transaction.

USE TEACHMESQLSERVER

GO

BEGIN TRANSACTION

INSERT INTO DEP VALUES(2)

ROLLBACK

GO

SELECT * FROM DEP

When we fetch all the records from DEP table, we don’t find second record. It is because we ROLLED BACK the transaction. When we ROLL BACK our transaction it restore the table data to last COMMITTED stage. As you know last COMMIT state was when we inserted record one. So it is showing 1 in the result set.

As you know TRUNCATE command can’t be rolled back until it is used within transaction. If you don’t know about what are the difference between Truncate and Delete command then Click here

If you will run the below command then you will find, Truncate can also be rolled back if it is used in Transaction.

  

USE TEACHMESQLSERVER

GO

BEGIN TRANSACTION

TRUNCATE TABLE DEP

ROLLBACK

GO

SELECT * FROM DEP

We can also save transaction point in our Transaction; those save point can be used for roll back. Below example shows a simple example of SAVE TRANSACTION. We are inserting three records but we have created on transaction point after inserting first record. After two more insert operation we are rolling back to Saved Transaction point named as one and then committing the transaction.

USE TEACHMESQLSERVER

GO

BEGIN TRANSACTION

INSERT INTO DEP VALUES(2)

SAVE TRANSACTION ONE

INSERT INTO DEP VALUES(3)

INSERT INTO DEP VALUES(4)

ROLLBACK TRANSACTION ONE

COMMIT

GO

SELECT * FROM DEP

As you can see form the above result set record 3 and 4 was not inserted in to the table, it is because before committing the transaction we rolled back to the saved transaction point which comes after the first insertion which is 2.

@@TRANCOUNT System Function

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Showing the effects of the BEGIN and COMMIT statements

PRINT @@TRANCOUNT
--  The BEGIN TRAN statement will increment the
--  transaction count by 1.
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The COMMIT statement will decrement the transaction count by 1.
    COMMIT
    PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT

Click here to know how to Manage Transaction in Stored Procedure.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.