How to work with Transactions and Errors in SQL Server. What is the difference between Auto-Commit and Explicit Transactions and how do transactions affect Error Handling?

In a previous post we discussed how to work with batches where we created a table named Accounting.BankAccounts and inserted two records. When the two records were inserted, we received the following message.

This happened because while the two statements were in the same batch, there were two separate auto commit transactions. However, what if we wanted to make sure that both statements either had to be successful or neither statement would commit to the database? An example of this would be if Jack had hired Diane to work on his website. Diane sends an invoice of $200. We would need to transfer $200 from Jack’s account into Diane’s account.

We would run the following Update statements to transfer $200 from AcctID 1 into AcctID 2.

UPDATE Accounting.BankAccounts
SET Balance -= 200
WHERE AcctID = 1

UPDATE Accounting.BankAccounts
SET Balance += 200
WHERE AcctID = 2

This code would run successfully, and we would still receive the message that each statement was successful.

However, what if we made an error in one of the statements? For example, in the first update statement we accidentally typed 2/0 instead of $200 which would give a divide by zero run-time error. (Error message displayed below)

UPDATE Accounting.BankAccounts
SET Balance -= 2/0
WHERE AcctID = 1

UPDATE Accounting.BankAccounts
SET Balance += 200
WHERE AcctID = 2

Notice that the first update statement failed, but the second update statement still ran successfully. This would lead to inconsistent data in our tables where $200 had been added to the Diane account without removing it from the Jack account.

So, to ensure that either both statements are successful or neither statement completes, we will create an Explicit Transaction by using the Begin Transaction and Commit Transaction statements.

	BEGIN TRANSACTION
		UPDATE Accounting.BankAccounts
		SET Balance -= 2/0
		WHERE AcctID = 1

		UPDATE Accounting.BankAccounts
		SET Balance += 200
		WHERE AcctID = 2
	COMMIT TRANSACTION

However, when we run this code, we still get the same message.

This is because we would need to add error handling to capture the error and rollback the entire transaction. We introduce error handling by using the TRY/CATCH statements. We use the Begin Try and End Try statements around the code we would like to try and run.

If there are not any errors, the code will run successfully. If there are errors, our code will be sent immediately to the Begin Catch and End Catch block of code to be handled appropriately. Notice we have also indented the code to make it easier to see where the error handling and transaction statements end and begin. (For those familiar with .NET language, SQL does not include a FINALLY block with its error handling.)

BEGIN TRY
	BEGIN TRANSACTION
		UPDATE Accounting.BankAccounts
		SET Balance -= 2/0
		WHERE AcctID = 1

		UPDATE Accounting.BankAccounts
		SET Balance += 200
		WHERE AcctID = 2
	COMMIT TRANSACTION
	PRINT 'Transaction Successful'
END TRY
BEGIN CATCH
	IF ERROR_NUMBER() = 8134
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'Divide by Zero Error. Error Number:' + 
		CAST(ERROR_Number() as char(4))
	END
END CATCH

When we TRY to run this code, an error is caught and thrown into the catch section to be handled. In the CATCH section we used the Rollback Transaction statement to undo the entire transaction. The following message shows that not only did the first UPDATE statement not run, neither did the second statement in the transaction.

By using transactions, we ensure that our data stays consistent. Another way of turning on transactions is by using the SET IMPLICT_TRANSACTION ON statement. This would implicitly turn on transactions as soon as the first statement runs without having to use the Begin Transaction statement. However, it will not finish until an explicit Commit Transaction or Rollback Transaction is reached. For better control and readability, it is still best practice to use Explicit Transactions with the Begin Transaction statement.

Finally, if we did not want to include the error handling but still use the functionality of the transactions. We could use the SET XACT_ABORT ON statement. This statement would terminate the transaction once the error was reached and cancel the rest of the code. However, we would not be able to add any other error handling functionality without using the TRY/CATCH error handling blocks.