Problems occurring when I use Transactions in Stored Procedure

Question

I'm having an SQL related problem which is just pissing me off now :p. Here is my setup:

I have 2 Stored Procedures:

Parent Stored Procedure is called GenerateAnnualPenalty.

GenerateAnnualPenalty has a SELECT CURSOR in it, which iterates over a series of objects called Properties, and for each Property, it determines if a Penalty needs to be applied. This is stored in a boolean variable named @ApplyPenalty, and its either 0 or 1. Also, there are no SQL Transactions being used in GenerateAnnualPenalty.

Secondly, For each Property that GenerateAnnualPenalty iterates over, it calls a child Stored Procedure named GenerateAnnualPenaltyForProperty. @ApplyPenalty is passed as an input to GenerateAnnualPenaltyForProperty. GenerateAnnualPenaltyForProperty does use SQL Transactions (commit/rollback).

I make entries into a table named DebugLog, to mark if a specific point has been reached in code or not.

Here is the skeleton for the child GenerateAnnualPenaltyForProperty:

ALTER PROCEDURE [RTS].[GenerateAnnualPenaltyForProperty]
@PROPERTY_ID numeric(18,0),
@ApplyPenalty int
AS

insert into DebugLog (DebugMessage1, DebugMessage2, DebugMessage3, DebugMessage4)
values
('Checkpoint 1 for Property:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

DECLARE @TRANSACTION_NAME varchar(50)
SET @TRANSACTION_NAME = 'GenerateAnnualPenaltyForProperty'

BEGIN TRANSACTION @TRANSACTION_NAME

BEGIN TRY


    insert into DebugLog (DebugMessage1, DebugMessage2, DebugMessage3, DebugMessage4)
    values
    ('Checkpoint 2 for Property:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

    IF @ApplyPenalty = 1
    BEGIN
        -- All main logic here !!!
    END

    COMMIT TRANSACTION @TRANSACTION_NAME
    RETURN 0
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION @TRANSACTION_NAME
    RETURN -1
END CATCH

The Problem (Intro):

Suppose GenerateAnnualPenalty iterates over 4 Properties, in this specific order (which exploits the problem):

First property to iterate over: @PROPERTY_ID = 1

Second property to iterate over: @PROPERTY_ID = 2

Third property to iterate over: @PROPERTY_ID = 3

Fourth property to iterate over: @PROPERTY_ID = 4

Suppose that for Properties 1, 3 and 4, ApplyPenalty = 1, while for Property 2, ApplyPenalty = 0

The Problem (Main Crux):

When GenerateAnnualPenaltyForProperty is called for Property 1, everything is fine: I see both Checkpoint 1 and Checkpoint 2 entries in the DebugLog table.

When GenerateAnnualPenaltyForProperty is called for Property 2, everything is fine again: I see both Checkpoint 1 and Checkpoint 2 entries in the DebugLog table.

When GenerateAnnualPenaltyForProperty is called for Property 3, the incorrect scenario occurs: I see only the Checkpoint 1 entry in the DebugLog table, whereas the 'Checkpoint 2` entry should also have been visible !

When GenerateAnnualPenaltyForProperty is called for Property 4, its again correct: I again see both Checkpoint 1 and Checkpoint 2 entries in the DebugLog table.

So the problem only occurs for a Property for which ApplyPenalty = 1, provided that in the previous iteration, ApplyPenalty = 0. In such a case, the property with ApplyPenalty = 1 gets treated as if it was ApplyPenalty = 0

Disabling SQL Transaction code fixes the problem, but why ?:

If I disable all code in GenerateAnnualPenaltyForProperty which relates to SQL Transactions, everything works fine ! The problematic case described above is corrected. Here is a skeleton for GenerateAnnualPenaltyForProperty, in which SQL Transaction code has been taken out, for which it works:

ALTER PROCEDURE [RTS].[GenerateAnnualPenaltyForProperty]
@PROPERTY_ID numeric(18,0),
@ApplyPenalty int
AS

insert into DebugLog (DebugMessage1, DebugMessage2, DebugMessage3, DebugMessage4)
values
('Checkpoint 1 for Property:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)


BEGIN TRY
    insert into DebugLog (DebugMessage1, DebugMessage2, DebugMessage3, DebugMessage4)
    values
    ('Checkpoint 2 for Property:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

    IF @ApplyPenalty = 1
    BEGIN
        -- All main logic here !!!
    END

    RETURN 0
END TRY
BEGIN CATCH
    RETURN -1
END CATCH

The Question (???):

Why does this behavior occur ? Why is it that when I use SQL Transaction commit/rollback into GenerateAnnualPenaltyForProperty, the Stored Procedure doesn't work for the problematic case ?

Actual Code:

If anyone wishes to see the full code for the child Stored Procedure, then its available here: https://gist.github.com/anonymous/5214236

I added a sql-server tag to expand your audience. If that's not correct, replace it with the db type you are using. — Dan Bracuk, Mar 21 '13 at 13:05
Perhaps an error occurs in your "main logic" during property 3 which causes checkpoint 2 to be rolled back. Checkpoint 1 falls outside your transaction scope, thus is always inserted. — JodyT, Mar 21 '13 at 13:06
I would try as hard as I possibly could to get rid of the cursor based solution. I have a cursor sample, and its set-based replacement example here : http://granadacoder.wordpress.com/2008/07/24/cursors-setbased-and-scalar-udf/ — granadaCoder, Mar 21 '13 at 13:13
@JodyT, there's no problem with the logic itself, because if I disable the SQL Transaction related code, I see both entries for Property 3 as well. And also, no error/exception occurs. — Ahmad, Mar 21 '13 at 13:14
I personally feel that the SQL Transaction data remains in the session somehow, so that when `GenerateAnnualPenaltyForProperty` is called for the second time, it somehow remembers the result from the iteration before ? So maybe I need to explicitly 'destroy' the Transaction somehow, before I exit `GenerateAnnualPenaltyForProperty` ... Does this make sense ? — Ahmad, Mar 21 '13 at 13:17
What are the return values for Property 3 in each case? If it is -1 then @jodyT is probably correct - you don't see Checkpoint 2 in the first case because the insert has been rolled back. — Young Bob, Mar 21 '13 at 13:22
I would suspect that this is just a typo, but it appears that the `@` is missing from the front of the `PROPERTY_ID` param at the start of your script. — Paul, Mar 21 '13 at 13:34
Returned value is `0` for all cases. That means no error occurs anywhere. Guys, I'm 100% positive that the code is correct, as I've tested it inside out ! :). I've corrected the typo reported by @Westie. Its just a type here, and not in the original code. — Ahmad, Mar 21 '13 at 14:02
I haven't looked at your code but if you are iterating over records you are doing something wrong, you should never iterate over records, you should work with data sets. — HLGEM, Mar 21 '13 at 17:27

score 0 · Answer 1 · answered Mar 21 '13 at 13:24

0

Could you try and change the code to:

BEGIN TRY

    -- Your code

END TRY

BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

If an error does occur during a transaction, and a rollback is done, you will see the error that caused it.

answered Mar 21 '13 at 13:24

JodyT

4,324
2
19
31

This did not solve it. I tried the code. As expected, the outcome was correct, and I didn't see any error. To further confirm, I ran a second version of the above code with an additional `DebugLog` insertion inside the catch statement. Returned value was `0`, with no error, and with no error entry in `DebugLog`. – Ahmad Mar 21 '13 at 13:59

score 0 · Answer 2 · answered Mar 21 '13 at 13:48

0

A possible explanation is you have nested transactions - for example if the main logic calls a stored procedure which also does a BEGIN TRAN...COMMIT/ROLLBACK TRAN. If the inner transaction does a ROLLBACK it will rollback to the outermost transaction so in the case of Penalty 3 the code appears to fail. When you remove the outermost transaction it only rollsback the inner transaction making it appear to have worked.

answered Mar 21 '13 at 13:48

Young Bob

733
3
9

The outer Stored Procedure is not doing anything related to SQL Transactions. No Begin/commit/rollback transaction in the parent Stored Procedure. This is only being done in the child SP, as that's the one doing the actual processing at individual Property level. – Ahmad Mar 21 '13 at 14:05
I was meaning that transactions may be happening within the line marked "-- All main logic here !!!" for example within other SP's that may be being called. – Young Bob Mar 21 '13 at 14:20
Is it possible to see "-- All main logic" because from everything else you've said I can see no reason for it. – Young Bob Mar 21 '13 at 14:26
I have triple checked that no transactions are being used anywhere within the main logic. The full code for the child SP is available here: https://gist.github.com/anonymous/5214236 .. Although it might seem a bit scary :) – Ahmad Mar 21 '13 at 16:06
Thanks, I can't see a problem with the code. So it's behaving as if BEGIN TRY line isn't being reached when it's preceded by BEGIN TRANSACTION. Could you put a PRINT statement immediately before the TRY and immediately after the TRY? (A print statement shouldn't get rolled back, in case that's what's happening) – Young Bob Mar 21 '13 at 18:14
Bob, I did what you're asking. So its like this: Print 1, `BEGIN TRANSACTION`, Print 2, `BEGIN TRY`, Print 3 ... So if I do this, for the problematic case, I only see the very first print, i.e. Print 1 .. I don't see Prints 2 or 3 .. That means that its the `BEGIN TRANSACTION` statement that causes the code to get out of this Stored Procedure, not `BEGIN TRY` – Ahmad Mar 22 '13 at 10:14
Yes and presumably the error is being caught in the code calling this sp. You could move the BEGIN TRANSACTION inside the BEGIN TRY. The error should then be caught by this sp's catch block where you could print ERROR_MESSAGE(), @@TRANCOUNT and XACT_STATE() for more info. – Young Bob Mar 22 '13 at 12:16