SQL Server Transaction

Multiple SQL queries in a group in SQL Server may need to execute and out of the SQL Queries in the group, some of the queries may fail. This could create data update issues and data consistency issues as a partial update could make a series of transactions incomplete. So, there is an SQL Statement called TRANSACTION with COMMIT and ROLLBACK, which can be used to handle incomplete or partial execution of queries in a Transaction. In this article, we will discuss the different Transaction commands with examples.

This article explores SQL Server Transactions, including syntax, error handling, and best practices. The model illustrates the key role played in data integrity, which is applied to financial transactions and inventory management. Whether you’re a developer or an administrator, understanding these principles is essential to building flexible database applications.

TRANSACTION in SQL Server

A TRANSACTION in SQL Server is a single unit of work that contains one or more SQL Statements to be executed at once. The TRANSACTION statement ensures that all statements within a single transaction either succeed or fail together to maintain data consistency. If there are multiple SQL queries for Insert, Update, or Delete without a TRANSACTION statement, if one of the queries fails then the other queries get executed since each SQL statement is an independent Transaction. This could lead to errors for a targeted transaction with incomplete data updates.

TRANSACTION is based on ACID, which stands for Atomicity, Consistency, Isolation and Durability. These four characteristics define the reliability and consistency of a database transaction with one or more SQL queries as a single unit. TRANSACTION provides a way to ensure consistency and integrity of the database when multiple SQL statements are grouped and executed in an atomic operation.

Basic Syntax:

BEGIN TRANSACTION;

— SQL statements here

— If everything is successful, commit the transaction

COMMIT;

— If there is an issue, rollback the transaction

ROLLBACK;

A transaction begins with BEGIN TRANSACTION and ends with either COMMIT or ROLLBACK. When all statements inside the TRANSACTION block execute successfully, then the COMMIT statements execute to make the changes permanent in the database. But if there was a failure in one or more queries in the block, then all SQL Statements are rolled back using the ROLLBACK statement.

Example of SQL Server Transaction

Example: Using Begin Transaction and Commit Transaction

BEGIN TRANSACTION
      Insert into Customers  (CustName,City,State,Country) Values ('Jaya Kumar','Bangalore','KA','India')
      Update Orders Set Product='iPhone 15' where Id=3
COMMIT TRANSACTION

In the above example, both SQL statements will be executes successfully.

Output in customers table:

Customers table data

Order Table Output after update:

Order table data

IF…ELSE Statements inside the TRANSACTION block

The COMMIT and ROLLBACK can be used inside conditional statements like IF…ELSE to commit or rollback the changes from all the SQL queries based on a specific condition.

Example: Using Begin, Commit, and Rollback Transaction with IF…ELSE Statements

BEGIN TRANSACTION

Example for using Begin, Commit, and Rollback Transaction with IF…ELSE Statements:

BEGIN TRANSACTION
Insert into Customers  (CustName,City,State,Country) Values ('Vijay Kumaran','Bangalore','KA','India')
Update Orders Set Product='iPhone 14' where Id=31
If @@ROWCOUNT = 0
      ROLLBACK TRANSACTION
else
     COMMIT TRANSACTION

In the above example since there is no Id no 31 in orders table and the transaction will be rolled back. So no data inserted or updated as seen below:

Output:

IF..ELSE example no updates done

SAVE TRANSACTION

The save transaction statement can be used with a name identifier to rollback a transaction from a particular point based on some specific logic.

Example: Conditional Transaction Rollback using SAVE TRANSACTION in SQL Server

BEGIN TRANSACTION
Insert into Customers  (CustName,City,State,Country) Values ('Vijay Kumaran','Bangalore','KA','India')
 SAVE TRAN tran_save 
Insert into Customers  (CustName,City,State,Country) Values ('Kumaresan Prabhu','Chennai','TN','India')
Update Orders Set Product='iPhone 14' where Id=34
If @@ROWCOUNT = 0
     ROLLBACK TRANSACTION  tran_save
COMMIT TRANSACTION

In the above example the SAVETRANSaction command is used to rollback any insert or update after the SAVETRAN block if the update to Orders table is not successful as given in the example above. So when we check the table, the first Insert is successful, but second insert is rolleback and so no data inserted from this Insert statement.
Output:

SAVETRANS example output

Named Transaction in SQL Server

Each BEGIN TRANSACTION can be given a name and it can be referenced in COMMIT and ROLLBACK Statements. This will be useful when there are multiple TRANSACTION statements or nested transaction statements in a single Transaction code block.

Example: Nested Transactions with Named Transactions in SQL Server

BEGIN TRANSACTION Trans_One
      Insert into Customers  (CustName,City,State,Country) Values ('Raja Kumaran','Chennai','TN','India')
      Begin TRANSACTION Trans_Two
            Insert into Orders Values (@@IDENTITY,'SANYO TV',1)
      COMMIT TRANSACTION Trans_Two
COMMIT TRANSACTION Trans_One

Named Transaction example

Nested Transactions

TRANSACTION statements can be nested to separate multiple units of code and to only COMMIT or ROLLBACK specific TRANSACTION

Example: Nested Transactions with Selective Commit/Rollback in SQL Server

BEGIN TRANSACTION Trans_One

Insert into Customers  (CustName,City,State,Country) Values ('Ramraj','Chennai','TN','India')
Insert into Orders Values (@@IDENTITY,'Soundbar BOAT 900',1)

        BEGIN TRANSACTION Trans_Two
             Update Orders Set Product='iPhone 14 pro max' where Id=3
        COMMIT TRANSACTION Trans_Two

COMMIT TRANSACTION Trans_One

Below is the output after the above Insert and update statements

Output:

Nested Transactions

Error Handling – TRY…CATCH

Error Handling block can be used to catch errors and commit or rollback the transaction accordingly as below:

Example: Error Handling with Transaction in SQL Server

BEGIN TRY

BEGIN TRANSACTION Trans_One
Insert into Customers  (CustName,City,State,Country) Values ('Loganathan','Chennai','TN','India')
Insert into Orders Values (@@IDENTITY,'SANYO TV',1)
COMMIT TRANSACTION Trans_One

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION Trans_One

END CATCH

Below is the data updated after the above Insert statements
Output:

Try…Catch example

Common Use Cases for TRANSACTION

1. Transferring funds between Bank Accounts

2. Inventory management of updating Orders online involving multiple tables

3. Batch Processing to make Batch Updates or Insert of data

4. Critical System Operations like updating configurations, settings or transactions to maintain consistency of the system state

5. Data Cleaning and Maintenance tasks, and prevent partial update by roll back all changes.

Conclusion

The TRANSACTION statements are a great way to avoid any partial updates to the database tables which are related and needs to implement ACID characteristics of all data. It is important to use transaction statements with care and as needed to maintain data consistency and integration. By implementing proper transaction management strategies, developers and administrators can enhance the performance and reliability of their SQL Server databases.