savepoints within transactions
A savepoint is a named location from which a transaction can restart if part of it is conditionally canceled. That means you can rollback a transaction to a specific savepoint if a statement does not complete successfully, as shown in the previous example.
When you assign a savepoint name, you should use 32 characters or less. SQL Server allows you to assign a longer name, but the statement uses only the first 32 characters. Bear in mind that the savepoint name is case-sensitive even if SQL Server is not configured for case sensitivity. Another option is to use a variable in the SAVE TRANSACTION statement, but the data type must be char, varchar, nchar, or nvarchar. If you use the same savepoint name multiple times in the same transaction, the ROLLBACK TRANSACTION statement rolls back to the most recent savepoint.
Normally, a ROLLBACK TRANSACTION resets the value of @@TRANCOUNT to 0. However, when a transaction rolls back to a savepoint, @@TRANCOUNT is not reset. The SAVE TRANSACTION statement also has no effect on @@TRANCOUNT.
Example: You need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order. You need to ensure that the stored procedure:
Runs within a single transaction.
Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
Commits changes to all four tables when updates to all four tables are successful.
Solution: You create a stored procedure that includes the following Transact-SQL segment:
This will handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. (It is worth noting that the code above is wrong: 1. It is END TRY rather than END TRAN; 2. It is ROLLBACK TR 1 instead of ROLLBACK TRAN. )
It is also noteworthy that we add the @CustomerComplete variable in the BEGIN TRY block, and test it in the BEGIN CATCH block.
Note: XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. XACT_STATE =1: the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
References:https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql
It is fine to write:
IF XACT_STATE() = 1
ROLLBACK TRANSACTION
But normally, it seems to write as follows,
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
You use XACT_STATE() to determine the current status of a transaction if one is in effect. There are three possible values: 1-There is an active transaction that can be committed; 0-There is no active transaction; -1-There is an active transaction that cannot be committed, also referred to as an uncommitable transaction, or a doomed transaction.