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.

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 do everything or do nothing principle is called as 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 these two statements or operations by applying do everything or 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 is modified in the database is rolled back. In this way, transaction maintains the integrity of data in a database.

Transaction processing follows these steps

  1. Begin transaction
  2. Process database commands
  3. Check for errors

       If error occurs

                Roll back the transaction

       Else

              Commit the transaction

To manage transaction in SQL Server, we have provided with transaction control language that provides command like “BEGIN TRANSACTION”, “COMMIT TRANSACTION”, “ROLLBACK TRANSACTION”, and “SAVE TRANSACTION”.

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.

Let us understand transaction management with examples.

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 we commit the transaction we cannot Rollback the above three statements.

SELECT * FROM Product will give us the below output

Transaction Management in SQL Server

What is the need of ROLLBACK command?

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 we want to restore our data into its previous position then we need to write the ROLLBACK at any time after the DML queries have been written but remember once the COMMIT has been written then we cannot ROLLBACK the data.

That means we can only ROLLBACK the DML query that has been written after the last COMMIT statement.

Syntax:

BEGIN TRANSACTION

ROLLBACK TRANSACTION

Example on 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 to Use Transactions?

We should use transactions when several operations must succeed or fail as a unit. The following are some frequent scenarios where use of transactions is recommended:

  • In batch processing where multiple rows must be inserted, updated, or deleted as 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.

SUMMARY

In this article, I try to explain the Transaction Management in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

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