34th Friday Fun Session (Part 2) – 22nd Sep 2017
When one (MS SQL Server) Stored Procedure (SP), say, A calls another SP, say, B inside it; we call the SPs are nested. It is not always the case that A calls B. B can be called directly as well. Now if they are required to execute within transaction, in both the cases – when executed individually or nested, we face some interesting situations. Here we discuss how to manage transactions for those cases, when scope is limited within a database.
Requirements
We usually face two kinds of requirements.
- Full Commit: either all in both parent (A) and child (B) have to be executed or all should fail.
- Partial Commit: if one or more calls to child fail, let them fail. But persist/commit the changes done by parent and the successful calls to child.
Nesting does not need to be limited within two levels (parent and child) as stated here. For example, B can further call another SP C, or A can call any of B or C and so on. The requirements can vary in some other ways as well. However, if we solve the above two cases, the same mechanism/logic can be used/extended to handle all other situations.
Note that, we are also considering the situation that the child, B can be directly called, and in that situation, we are making sure that it is executing within its own transaction.
Full commit
The way we have written the SPs A and B as shown below, A is calling B 6 times. However B fails on the last call. A is inserting data into a table called OuterTable and B is doing the same for InnerTable.
In all or nothing case, we want to make sure, when we execute A, if any call to B fails, no data should be inserted in any of the tables; all should fail. It is obvious that if all the calls pass, then all data changed by them should persist.
It is also required that if B is executed directly, everything happening inside it must happen within a transaction.
Code for SP A:
CREATE Procedure [A]
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Success AS INT
DECLARE @Counter AS INT
SET @Success = 0
SET @Counter = 0
INSERT INTO [OuterTable] ([Name]) VALUES(@Counter)
WHILE < 6
BEGIN
EXEC @Success = [B] @Counter;
IF @Success = 0
THROW 50001, 'B Failed! Rollback all!', 1
SET @Counter = @Counter + 1;
END
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SET @Success = 0
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
RETURN @Success
END
Code for SP B:
CREATE Procedure [B] (@CallCounter INT)
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Success AS INT
SET @Success = 0
IF @CallCounter = 5
THROW 50001, 'Last call cannot be executed! Rollback all!', 1
INSERT INTO [InnerTable] ([Name]) VALUES(@Counter)
SET @Success = 1
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SET @Success = 0
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
RETURN @Success
END
When A gets called it starts a transaction. It then calls B, that succeeds 5 times, and subsequently fails on the last one.
@@TRANCOUNT is important
When B starts, it begins a transaction, unconditionally. Each time A calls B, @@TRANCOUNT gets incremented by 1 and reaches 2. Each time B succeeds, it executes a commit, thus reducing @@TRANCOUNT Back to 1, corresponding to the transaction started by A.
However, the last call to B fails. It then throws an exception, that gets the execution flow jump into the catch block, where it checks @@TRANCOUNT, that is now 2. It then rollbacks the transaction. A rollback discards all transactions in the nested call and resets @@TRANCOUNT. Before commit or rollback, it is a safe practice to make sure that it is still under a transaction, by checking @@TRANCOUNT value that should be greater than 0.
At this situation, B returns 0 to indicate failure (usual practice is to use 0 for indicating success though). A gets to know that B has failed. A throws exception, and ends up in catch block, where it checks @@TRANCOUNT, that is already 0. No further rollback is called and at the end 0 is returned from A to mean that it has failed.
Everything that A and B have done so far are all rolled back. After all, no matter how many transactions are started, a single rollback can discard all. At the end, no data changes happen to either of the tables, where they inserted data.
If B executes directly, depending on the parameter, data would persist or it would fail. However, all inside it would happen within a transaction.
Partial Commit
In this case, we want to persist the changes made by 5 successful calls to B, along with what A has done. The only thing that should get discarded is the failed 6th call to B.
Code for SP A:
CREATE Procedure [A]
AS
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Success AS INT
DECLARE @Counter AS INT
SET @Success = 0
SET @Counter = 0
INSERT INTO [OuterTable] ([Name]) VALUES(@Counter)
WHILE < 6
BEGIN
EXEC @Success = [B] @Counter;
IF @Success = 0
PRINT 'I know B Failed! But I am just ignoring that!', 1
IF @Success = 2
THROW 50001, 'Something terribly wrong! Rollback all!', 1
SET @Counter = @Counter + 1;
END
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SET @Success = 0
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
RETURN @Success
END
Code for SP B:
CREATE Procedure [B] (@CallCounter INT)
AS
BEGIN
DECLARE @ProcedureSave NVARCHAR(MAX)
DECLARE @TranCounter INT;
SET @ProcedureSave = 'B-' + CONVERT(CHAR(23), GETDATE(), 121)
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION @ProcedureSave;
ELSE
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Success AS INT
SET @Success = 0
IF @CallCounter = 5
THROW 50001, 'Last call cannot be executed! Rollback all!', 1
INSERT INTO [InnerTable] ([Name]) VALUES(@Counter)
SET @Success = 1
IF @TranCounter = 0 AND @@TRANCOUNT > 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SET @Success = 0
IF @TranCounter = 0
ROLLBACK TRANSACTION;
ELSE
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION @ProcedureSave;
ELSE
SET @Success = 2
END CATCH;
RETURN @Success
END
Savepoint
B starts a transaction, only if it is already not inside one. Meaning, if it is directly called then it starts a transaction of its own. However, if it is called by A, it does not do so. In that case, it creates a savepoint. A savepoint name is created in a way so that for each instance of the SP’s execution, it remains unique. A savepoint is a point where it can come back by executing a partial rollback. In such a situation, all changes between the savepoint and the place where partial rollback is called get discarded.
That is precisely what happens, when B fails and an exception is thrown. In catch block, it checks whether it started the transaction on its own. If that is the case, it simply calls a (full) rollback. However, if it was called by A, that also means it created a savepoint; it would simply execute a partial rollback using the savepoint. A partial rollback only discards the changes done by B itself.
XACT_STATE() is a safe choice
However, prior to executing the partial rollback it should check whether it is safe to do so. If XACT_STATE() returns -1, that means something went wrong, something that should happen rarely. In this situation, a partial rollback is no longer possible – only a full rollback can be done. Special failure code 2 is returned by B to indicate such a colossal failure.
A remains mostly the same. The only thing that gets changed is when it knows B has failed. Earlier, it would have thrown an exception. But this time, if the returned failure code is 0, it simply ignores that B has failed. If the returned failure code is 2, it knows that B fails to execute partial rollback – something really bad – A executes a (full) rollback.
In the above case, we see that the outer table gets one new row, and the inner table gets 5 new rows; notwithstanding the fact that 6th call to B fails.
When B is called directly – not from A – it manages its own transaction; savepoint is never used.
Sharing Experiences 