Transactions in SQL Server
In my last blog, I talked about errors in SQL Server and the methods used to handle them. In this blog, I will talk more about how SQL transactions works. This blog is a continuation of information from DataCamp’s “Transactions and Error Handling in SQL Server” course. The data used for the concepts in this blog is a bank account transaction dataset, containing information from customers, accounts, and transactions.
Transactions
The definition of a transaction is the execution of one or more operations, such that either all or none of the operations are executed. An example of transaction using our dataset is an account transfer of $100 from one account to another. For this transaction to occur, we must first subtract $100 from one account and then add $100 to the other account. Both operations must occur for a successful transaction but if any of the operations fail, the transaction also fails.
The way a transaction occurs, with its all or nothing functionality, reflects a key property of data integrity within SQL known as Atomicity. Atomicity is reliant on the fact that transactions are either successful or can be rolled back if they failed. This ensures data integrity even if a transaction were to fail mid-way through execution.
To declare a transaction, we use BEGIN TRAN or BEGIN TRANSACTION as the starting point of the statement.
BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]COMMIT is used to designate the end of a successful transaction. When COMMIT gets executed, the transaction can no longer be reversed.
COMMIT [ { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]
[ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
[ ; ]ROLLBACK TRAN or ROLLBACK TRANSACTION is used to revert a transaction back to the beginning or a previous savepoint. I will talk more about save points later in this blog.
ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]
[ ; ]Going back to the previous example of transferring money between two accounts, we can perform this using our transaction statements within a TRY…CATCH block. We will declare our transaction within the TRY block using BEGIN TRAN. Our transaction will consist of changing the current_balance of the accounts appropriately to reflect a $100 transfer. Afterwards, we will COMMIT the transaction to confirm the changes. If, at any point, the transaction fails, we move to the CATCH block where we will ROLLBACK the transaction along with any operations or changes that might have taken place.
BEGIN TRY
BEGIN TRAN;
UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
INSERT INTO transactions VALUES (1, -100, GETDATE()); UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
INSERT INTO transactions VALUES (5, 100, GETDATE());
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
@@TRANCOUNT returns the number of BEGIN TRAN statements that are currently active within your instance of SQL. 0 means that there are no open transactions and any number greater than 0 represents the number of open transactions. Every BEGIN TRAN statement that is executed will increment @@TRANCOUNT by 1 and every executed COMMIT TRAN statement will decrement it by 1. ROLLBACK TRAN will decrease the value of @@TRANCOUNT to 0, unless there is a savepoint.
Savepoints
Savepoints are checkpoints we can set within a transaction. They allow us to rollback to simply the savepoint rather than the whole transaction. We set a savepoint using the SAVE TRAN or SAVE TRANSACTION statement. We can state a savepoint_name for ease of use.
SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } [ ; ]In the following example, we use savepoints within our transaction. We declare a savepoint after the beginning of our transaction as savepoint1 using SAVE TRAN savepoint1. We then insert into our customers table a new customer and then state ROLLBACK TRAN savepoint1. This will rollback any operations and changes that occur between the declaration and the rollback of savepoint1. At the end of this transaction, only Jeremy Johnsson will be present in the customers table.
BEGIN TRAN;
-- Mark savepoint1
SAVE TRAN savepoint1;
INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com', '555909090');-- Rollback savepoint1
ROLLBACK TRAN savepoint1;-- Mark savepoint2
SAVE TRAN savepoint2; INSERT INTO customers VALUES ('Jeremy', 'Johnsson', 'jeremyjohnsson@mail.com', '555929292');-- Commit the transaction
COMMIT TRAN;
XACT_ABORT and XACT_STATE()
When dealing with errors with our transactions, we have two means of controlling them. XACT_ABORT is a setting used to specify whether SQL Server will roll back a transaction when an error occurs. It can be set to either ON or OFF and by default, it is set to off. When it is set to ON, SQL Server will automatically rollback the transaction and abort the execution of the transaction if there is an error. If it is OFF, the transaction can be rollbacked or not, depending on the type of error.
XACT_STATE() is a function that returns a value depending on whether there are open transactions or not. It will return 0 if there are no open transactions, 1 for open and committable transactions, or -1 for open and uncommittable transactions. An uncommittable or doomed transaction is one that cannot be committed or rolled back to a savepoint. We can use XACT_STATE(), for example, to rollback a transaction if it returns as -1 or to commit it if it returns as 1.
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRAN;
INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com', '555909090');
INSERT INTO customers VALUES ('Dylan', 'Smith', 'dylansmith@mail.com', '555888999');
COMMIT TRAN;
END TRY
BEGIN CATCH
-- Check if there is an open transaction
IF XACT_STATE() <> 0
-- Rollback the transaction
ROLLBACK TRAN;
-- Select the message of the error
SELECT ERROR_MESSAGE() AS Error_message;
END CATCHIt was interesting to learn more about SQL Server transactions and how they work. Data integrity is a crucial step in maintaining data warehouses and the data science process. In my next blog, I will look to cover how concurrency is maintained in SQL Server. Thank you for reading!
