How to use transaction in sql server for data consistency
In many situations, there is a need to update multiple tables of a database, especially in a normalized database. For data consistency, either all updates should be successful or none of the command should execute. A good example of this would be a banking system where the transfer would increase the balance of one account while other would decrease balance of other account. Moreover, you will also need a log for both accounts to audit all transfers. If the first statement executed successfully but some error was encountered with the second one, and if it fails, then to maintain data consistency, the first executed statement should be reverted back. In such situations, we have to be sure that all the statements were executed correctly before the changes are committed to the database. In SQL server, this is done through transactions. Ideally, transaction should start before the first DML statement (insert, update, delete) and it should end when all the statements are executed. At any point, rollback would be issued only when any of the commands within the transactions fails.
Using Transactions in Transact-SQL
Transactions start with the BEGIN TRANSACTION command, which can be shortened to BEGIN TRAN. After a successful execution of group of SQL statements, commit the group of SQL statements using COMMIT TRANSACTION statement else rollback the group commands using ROLLBACK TRANSACTION statement. This means either all the statements within the transactions are executed as a whole or not even a single statement is executed. When the transaction begins, few resources are locked and not released unless the transaction is completed using the COMMIT TRANSACTION or ROLLBACK TRANSACTION command. Transaction in progress for long period of time may prevent other users from accessing the resources locked by the transaction. Though the BEGIN TRANSACTION statement starts a local transaction, it is not recorded in the transaction log unless the application performs DML operation like UPDATE, INSERT or DELETE statement.
Let us consider a very simple example
Begin
set transaction isolation level read committed
BEGIN TRANSACTION
BEGIN TRY
update UserInfo set Location='Delhi' where UserName='sarinmall'
set @userid=1/0
COMMIT TRANSACTION
select 'User Updated Successfully.' as 'Message'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_MESSAGE() as 'Message'
END CATCH
end
In above chunk of code, I have set the transaction isolation level to read only committed data. I have declared a transaction where I update the user location to ‘Delhi’. Immediately after the update statement, I have declared a statement that will result in an error. So, the transaction fails and the error message is displayed to the user after the rollback operation. Check the output below on executing above piece of code.
As you see above, since the transaction was rollback, update operation was reverted back and there was no change in the user location. To demonstrate the successful execution of the transaction, let me replace the erroneous statement with another update statement.
Check the below screen for the updated code and its result on executing the code.
As you see above, since my transaction was successful, both my update statements have successfully updated the user location.
Conclusion
In this article, we saw how to use transaction in SQL server to maintain data consistency. In fact, transaction was introduced in SQL server to comply with the ACID properties of database system. Transaction is very handy in banking operations and monetary based applications. In my next article, we will see how to use nested transactions and manage error handling in transaction.
Note: Images used on this website
are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon
Foundation or were found in google search under "Free to use and share". If any
of the images presented here violates copyright issues or infringes anyone copyright
or are not under "Fair use", then please bring it to our notice. Read
Disclaimer for more.
Share this to your friends. One of your friend is
waiting for your share.
Related Articles
How to use transaction in sql server for data consistency
Delete duplicate records from table in SQL
Post Comment