Beginners Guide to SQL Server Transactions

Transactions are a method in most databases that ensures that a series of instructions are either committed or rolled back. Sql Server is no different, transactions are supported. Transactions are useful when you must ensure that all the instructions pass or fail. Below is a straightforward example of a rollback transaction.

Begin Tran
    Update Students set Grade = 'A'
Rollback Tran

When you view the records after, all the students will have their original grades, and no one will have been elevated to the top grade.

Transactions always start with a Begin to indicate where the transaction is to start or, as the command says, begin. The end of the transaction is always terminated with either a Rollback Tran or a Commit Tran. Rollback will cause the changes to be ignored, whereas Commit will cause the changes to become permanent.

When using transactions, all other records become read-only so that they are not updated whilst the transaction is in place. If you don't commit or roll back a transaction, the database and any program editing records will hang until the transaction has been dealt with. You can use the @@Trancount variable to see if a transaction is currently active.

You can combine this with error handling so the code can roll back if an error occurs. Below is an example of how error handling can work with SQL Transactions.

Begin Transaction

Begin Try
    Update table set Score = 10 where Id = 5
    if (Select sum(score) from table) > 9
    Begin
        ; throw 99000, 'High Score', 1;
    End
    Commit Tran
End Try
Begin Try
    Print 'Error has occurred'
    Rollback Transaction
End Try

Remember to put the Commit Tran at the end of the Try block; otherwise, it will hang the server. Do not put the Commit Tran after End Try because it will error if the transaction has been rolled back.

It is possible to have nested or embedded transactions, but this is just an introduction. The next article will be about nested transactions.

Tags - SQL   Transaction

Last Modified : June 2023