1. XACT_ABORT & XACT_STATE
In this lesson, you will learn how to control errors in transactions with the use of XACT_ABORT and XACT_STATE.
2. XACT_ABORT
XACT_ABORT specifies whether the current transaction will be automatically rolled back when an error occurs.
XACT_ABORT can be set to ON or OFF. OFF is the default setting. If an error occurs under this setting, the transaction can automatically be rolled back or not, depending on the error. If the transaction isn't rolled back, it remains open.
Setting XACT_ABORT to ON ensures that if there is an error, the transaction is rolled back and the execution is aborted.
Let's see some examples.
3. XACT_ABORT - examples
In this example, we set XACT_ABORT to OFF. We insert the data of two customers within a transaction. Finally, we commit the transaction.
If we execute this code, we get an error when inserting the data of the second customer. That is because in the customers table, there is a constraint that checks that every email must be unique, and Dylan's email is already registered. But, if we check the customers table, we see that the first insert statement was committed. That's because we didn't rollback anything in case of an error.
4. XACT_ABORT - examples
Let's change the previous example setting XACT_ABORT to ON. Now, the second insert statement also generates an error. The difference is that with this setting, the transaction is rolled back, so the first insert statement doesn't take place.
5. XACT_ABORT WITH RAISERROR
Let's study the XACT_ABORT behavior with RAISERROR. In this example, we set XACT_ABORT to ON. After that, we open a transaction and insert the data of two customers with new emails. Between these insertions, we raise an error using RAISERROR. Finally, we commit the transaction. If we run this code, we can see that RAISERROR produces an error. However, the execution continues, and the transaction remains open, so the data of the second customer is also inserted.
Checking the content of the customers table, we see that both insert statements took place.
6. XACT_ABORT with THROW
Let's modify the previous example using THROW instead of RAISERROR. This time we also get an error. However, none of the customer's data is inserted because the transaction is automatically rolled back.
If you remember from the previous chapter, Microsoft suggests THROW over RAISERROR, and this is one important reason.
7. XACT_STATE
Next, let's study the XACT_STATE function. XACT_STATE doesn't take any parameters and returns 0 when there is no open transaction, 1 when the transaction is open and committable, and -1 when the transaction is open but uncommittable, also known as doomed transaction. When a transaction is open but uncommittable, you cannot commit the transaction or rollback to a savepoint. The only option you have is to rollback the full transaction. You cannot make any changes, but you can read data.
Let's see some examples.
8. XACT_STATE - open and committable
This code sets XACT_ABORT to OFF. Then, a transaction starts within the TRY block and inserts the data of two customers. If everything goes fine, the transaction is committed. Otherwise, the CATCH block takes control and rollbacks if XACT_STATE is -1, or commits if XACT_STATE is 1. Finally, we select the error message.
This setting of XACT_ABORT makes that when the CATCH block takes the control, the transaction state remains open and committable; therefore, XACT_STATE is 1, so the transaction is committed.
9. XACT_STATE - open and committable
As the only statement to be committed is the insertion of the first customer's data, it is inserted.
10. XACT_STATE - open and uncommittable (doomed)
Let's change the previous example setting XACT_ABORT to ON. This will cause that when the CATCH block takes the control, the transaction remains open but uncommittable, so XACT_STATE is -1. The only option we have is to rollback.
11. XACT_STATE - open and uncommittable (doomed)
If we execute this code, we can see that the first customer's data isn't inserted because the transaction was rolled back.
12. Let's practice!
Let's play with XACT_ABORT and XACT_STATE!