Transaction Management in SQL Server

Transaction Management in SQL Server

In this article, I am going to discuss the basics of Transaction Management in SQL Server with some examples. Please read our previous article before proceeding to this article where we discussed the basics of Exception Handling in SQL Server with some examples. As part of this article, we are going to discuss the following pointers.

  1. What is Transaction Management in SQL Server?
  2. What is a Transaction in SQL Server?
  3. Understanding the Commands to Manage Transaction in SQL Server.
  4. Example to Understand Transaction Management in SQL Server
  5. What is the need of COMMIT and ROLLBACK Command in SQL Server?
  6. When do we need to Use Transactions in SQL Server?
What is Transaction Management in SQL Server?

The process of combining a set of related SQL operations into a single unit and executing those operations by applying to do everything or do nothing principle is called transaction management in SQL Server. For example, transfer money task is the combination of two operations

  1. Withdraw money from the source account
  2. Deposit money into the destination account.

We need to execute the above two statements or operations by applying to do everything or do nothing principle performing the transaction management. So, every transaction has two boundaries

  1. Beginning
  2. Ending

And controlling the boundaries of a transaction is nothing but transaction management.

What is a Transaction in SQL Server?

A transaction is a unit of work or set of SQL statement such as INSERT, UPDATE and DELETE which should be executed as one unit. A transaction ensures that either all of the SQL Commands executed successfully or none of them. If one of the commands in the transaction fails, all of the commands fail and any data that was modified in the database is rolled back. In this way, transaction maintains the integrity of data in a database.

Please have a look at the following diagram which shows how the Transaction Processing works in SQL Server.

Transaction Management in SQL Server

Understanding the Commands to Manage Transaction in SQL Server:

In order to manage the transaction in SQL Server, we are provided with transaction control language commands such as “BEGIN TRANSACTION”, “COMMIT TRANSACTION”, “ROLLBACK TRANSACTION”, and “SAVE TRANSACTION”. Let us understand each command and its need in detail.

BEGIN TRANSACTION: It indicates that the transaction is started.

COMMIT TRANSACTION: It indicates that the transaction completed successfully and all the data manipulation operation performed since the start of the transaction committed to the database and frees the resources held by the transaction.

ROLLBACK TRANSACTION: It will bring back an implicit or explicit transaction to the beginning of the transaction or erase all data modification made from resources held by the transaction.

SAVE TRANSACTION: This is used for dividing or breaking a transaction into multiple units so that we will have a chance of roll backing a transaction up to a location.

Example to Understand Transaction Management in SQL Server.

Let’s create the following Product table with some test records

-- Create Product Table
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY, 
  Name VARCHAR(40), 
  Price INT,
  Quantity INT
 )
 GO

 -- Populate Product Table with some test data
 INSERT INTO Product VALUES(101, 'Product-1', 100, 10)
 INSERT INTO Product VALUES(102, 'Product-2', 200, 15)
 INSERT INTO Product VALUES(103, 'Product-3', 300, 20)
 INSERT INTO Product VALUES(104, 'Product-4', 400, 25)

SELECT * FROM Product will give us the below output

Transaction Management in SQL Server

Example of COMMIT transaction with DML statements
BEGIN TRANSACTION
  INSERT INTO Product VALUES(105,'Product-5',500, 30)
  UPDATE Product SET Price =350 WHERE ProductID = 103
  DELETE FROM Product WHERE ProductID = 103
COMMIT TRANSACTION

Once you commit the transaction we cannot Rollback the above three statements. So, SELECT * FROM Product statement will give us the following output

Transaction Management in SQL Server

What is the need of ROLLBACK Command in SQL Server?

The ROLLBACK command in SQL Server is used to undo the transactions that have not already been saved to the database and gets back to the initial state where the transaction started. So, if you want to restore the data into its previous state then you need to write the ROLLBACK command at any time after the DML queries have been written but remember once the COMMIT has been written then you cannot ROLLBACK the data.

That means we can only ROLLBACK the DML query that has been written after the last COMMIT statement. The Syntax is as follows:

BEGIN TRANSACTION
ROLLBACK TRANSACTION

Example of ROLLBACK TRANSACTION with DML statements

BEGIN TRANSACTION
INSERT INTO Product VALUES(106,'Product-6',600, 30)
UPDATE Product SET Price =550 WHERE ProductID = 105
DELETE FROM Product WHERE ProductID = 104

When the above statements are not committed in the transaction then the user has a chance to cancel all the operations (DML operations) on the table by using ROLLBACK TRANSACTION command like below.

ROLLBACK TRANSACTION

The concept of ROLLBACK and COMMIT is designed for data consistency because many users manipulate the data of the same table using the same database so the user must get the updated data. That’s why COMMIT and ROLLBACK are used.

When do we need to Use Transactions in SQL Server?

We need to use transactions when we want to execute several operations as a unit. The following are some frequent scenarios where the use of transactions is recommended:

  • In batch processing where multiple rows must be inserted, updated, or deleted as of a single unit
  • Whenever a change to one table requires that other tables be kept consistent
  • When modifying data in two or more databases concurrently
  • In distributed transactions where data is manipulated in databases on various servers

When we use transactions, we put locks on data that is pending for the permanent change to the database. No other operations can take place on locked data until the acquired lock is released. We could lock anything from a single row up to the entire database. This is called concurrency, which means how the database handles multiple updates at one time.

Note: it’s important to keep transactions pending for the shortest period of time. A lock stops others from accessing the locked database resource. Too many locks, or locks on frequently accessed resources, can seriously degrade performance.

In the next article, we will continue our discussion with Exception handling Using Transaction Management in SQL Server with examples. Here, in this article, I try to explain the Transaction Management in SQL Server step by step with some examples. 

Leave a Reply

Your email address will not be published. Required fields are marked *