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:
Order Table Output after update:
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:
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:
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
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:
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:
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.