Transactions in Transact SQL

A transaction can be defined as a set of instructions that work as a unit, so if one part fails the whole process fails in its entirety, reversing all the changes made in the database.

To declare a transaction, we start with the following syntax:

BEGIN TRANSACTION

The shortened sentence will also be valid:

BEGIN TRAN

Therefore a transaction validates all the statements it contains as a single set or unit of work. If all the statements work, the changes to the database are committed and made permanent. If any statement fails, all changes must be rolled back.

Therefore, it is very common for transactions to internally contain error handling structures such as TRY...CATH, to commit or revert changes to the database. If the code fails, the error will be caught in the CATCH and we will proceed to revert all the changes to the database. If all the TRY instructions work correctly, the changes will be saved.

For more information regarding error control and handling, visit the error management section.

The control of saving and overlaying the changes, respectively, will be done using the following instructions:

• COMMIT TRANSACTION: commits and makes database changes permanent if the transaction completes successfully.

• ROLLBACK TRANSACTION:: undoes and reverts the changes of a transaction when an error occurs during it, until the start of the transaction or until a return point within it.

• SAVE TRANSACTION: Sets a savepoint within a transaction..

To understand better how a transaction works and the concepts of COMMIT and ROLLBACK, suppose we want to add two new customers within a transaction:

BEGIN TRANSACTION;
INSERT INTO Customers VALUES ('Gerald',
                             'Villegas',
                             '1195 White Avenue',
                             NULL);
INSERT INTO Customers VALUES ('Margaret',
                              NULL,
                              '3191 Wescam Court',
                              NULL);
COMMIT TRANSACTION;

If we execute the code above, it returns the following error message:

However, if we consult our Customers table, we can see that the first record referring to: Gerald Villegas has been correctly inserted:

From this example, we deduce that we are not using transaction correctly, since we are not taking into account the errors to undo the changes with the ROLLBACK statement.

In the following example, we are going to see an error management and the ROLLBACK statement, so that in case the insertion of a record fails, the entire process fails.

BEGIN TRAN;
    BEGIN TRY
        INSERT INTO Customers VALUES ('Robert',
                                     'Austin',
                                     '339 Tuna Street',
                                     NULL);
        INSERT INTO Customers VALUES ('Margaret',
                                      NULL,
                                      '3191 Wescam Court',
                                      NULL);
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        SELECT
         ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
        ROLLBACK TRAN
    END CATCH;

In this case, although the first INSERT INTO of the registry: Robert Austin works correctly, when the second one fails we capture the error in the CATCH and do ROLLBACK of the transaction. Therefore, if we consult the Customers table, we can verify that no record has been inserted.