ROLLBACK:
Once a transaction has been rolled back, all the changes made by the transaction will be discarded and the database will be returned back to its original state before the transaction started.
Example :
BEGIN TRANSACTION
UPDATE Employee
SET Location = 'Chicago'
WHERE Name = 'Jacob Logan'
IF @ROWCOUNT > 1
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
In the above example, the transaction containing the update statement is rolled back and the changes made by the UPDATE statement is discarded
if the number of rows affected by the UPDATE statement is more than 1, else the transaction is committed.
ERROR HANDLING:
Within an explicit transaction, when an error is encountered, the typical behavior is to halt the transaction and rollback the changes made by the transaction.
Error handling is commonly done be either:
- Using @@ERROR global variable
- Using TRY CATCH block
Handling errors with @@ERROR
@@ERROR returns an error number if the previous SQL statement generates an error. If the previous SQL statement executes without an error, then 0 is returned.
Example :
BEGIN TRANSACTION
DECLARE @ErrorNumber AS INT = 0
UPDATE Employee
SET Location = 'Chicago'
WHERE EmployeeID = 4
SET @ErrorNumber = @@ERROR
UPDATE Employee
SET EmployeeID = 1
WHERE EmployeeID = 4
SET @ErrorNumber = @@ERROR
IF @ErrorNumber > 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
In the above transaction, there are two update statements and the value of @ErrorNumber variable is set to the value returned by @@ERROR after each update statement.
If either of the update statement fails, then value of @ErrorNumber variable will be set to the error number.
In the end, a check is made to verify whether the value of @ErrorNumber is greater than 0, which implies that there was an error in at least one of the update statements.
If @ErrorNumber is greater than 0, then the entire transaction is rolled back, else all the changes made by the update statements are committed.
Since @@ERROR returns a new value after each SQL statement, the above transaction uses @@ERROR after each update statement to check whether there has been any error.
If the transaction has many SQL statements, this approach of error handling is bad mainly because it bloats the code unnecessarily.
Handling errors with TRY CATCH block
TRY CATCH block is a better alternative than using @@ERROR variable. Using TRY CATCH, we can enclose multiple SQL statements within a single TRY block.
When SQL Server encounters a TRY CATCH statement, it executes all the statements included in the TRY block.
If all the SQL statements in the TRY block executes successfully, SQL Server simply moves on. On the other hand,
if any of the SQL statement in the TRY block generates an error, SQL Server executes the CATCH statement to gracefully handle the error.
Example :
BEGIN TRY
BEGIN TRANSACTION
UPDATE Employee
SET Location = 'Chicago'
WHERE EmployeeID = 4
UPDATE Employee
SET EmployeeID = 1
WHERE EmployeeID = 4
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
SELECT ERROR_NUMBER()
AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
In the above example, the transaction is started inside a TRY block and contains two UPDATE statements.
If both the UPDATE statements execute without an error, then the transaction gets committed.
But if either of the UPDATE statement fails, then the control is immediately passed to the CATCH block where the entire transaction is rolled back.
NESTED TRANSACTIONS:
SQL SERVER allows nesting of transactions by allowing to start a new transaction within an existing transaction.
To keep track of nested transactions, SQL SERVER provides a global variable @@TRANCOUNT which returns the number open transactions in the current session.
When BEGIN TRANSACTION statement is executed, value of @@TRANCOUNT is incremented by 1. Similarly when a COMMIT TRANSACTION statement is executed, the value of @@TRANCOUNT is decremented by 1.
In nested transactions, commit and rollback works differently. When a COMMIT TRANSACTION statement is executed for an inner transaction, it does not actually commit the transaction.
Only thing it does is that it decrements the value of @@TRANCOUNT by 1. The only time COMMIT TRANSACTION actually saves changes to the disk is when the value of @@TRANCOUNT is 0,
indicating that the current transaction is the outermost transaction and is not an inner transaction.
On the other hand, when a ROLLBACK TRANSACTION statement is executed within any transaction level,
it rolls back all the transactions and resets the value of @@TRANCOUNT to 0 regardless of the current transaction nesting level.
Example :
BEGIN TRANSACTION
UPDATE Employee
SET Location = 'Chicago'
WHERE EmployeeID = 4
BEGIN TRANSACTION
UPDATE Employee
SET EmployeeID = 1
WHERE EmployeeID = 4
COMMIT TRANSACTION
ROLLBACK TRANSACTION
In the above example, there are two transactions. When the first BEGIN TRANSACTION statement is executed, the value of @@TRANCOUNT is incremented by 1.
When the second BEGIN TRANSACTION statement is executed, the value of @@TRANCOUNT is again incremented by 1. So at this point the value of @@TRANCOUNT is set to 2.
When the COMMIT TRANSACTION statement is executed for the inner transaction, the value of @@TRANCOUNT is decremented by 1.
Since at this point, the current value of @@TRANCOUNT is not 0, the changes made by the UPDATE statement inside the inner transaction is not saved to the disk.
Finally for the outermost transaction, since ROLLBACK TRANSACTION statement is executed,
all the changes made by both the transactions will be rolled back and the value of @@TRANCOUNT at this point will be set to 0.
PARTIAL ROLLBACK:
To avoid rolling back the entire transaction, we can define save points within a transaction.
When a save point is declared inside a transaction, we can reference the save point in the rollback statement which tells the database engine to rollback only those
changes which were made after declaring the specific save point. Save points are typically useful in a transaction where if an error occurs,
we want the transaction to recover from the error by rolling back to a save point and then continue executing the remaining statements.
We can declare multiple save points within a transaction. When multiple save points are declared, when we roll back to a particular save point,
any save point declared after that save point will be automatically removed.
Example :
BEGIN TRANSACTION
UPDATE Employee
SET Location = 'Chicago'
WHERE EmployeeID = 4
SAVE TRANSACTION A
UPDATE Employee
SET EmployeeID = 1
WHERE EmployeeID = 4
ROLLBACK TRANSACTION A
COMMIT TRANSACTION
In the above example, save point named A is defined inside the transaction.
When the ROLLBACK TRANSACTION statement referencing the save point is executed, the database engine rolls back only those changes which were made after the definition of the save point.
After rolling back the changes made by the second update statement, the COMMIT TRANSACTION statement will commit the changes made by the first UPDATE statement to the disk.