SQL Server 2005 transaction commit being ignored

Question

I am seeing some strange behaviour which has occurred more than once for completely unrelated queries.

The COMMIT TRANSACTION statement in a Sql query seems to be being ignored, despite no error occurring, leaving a lock on the table until the process is killed.

Here is one example:

BEGIN TRY

BEGIN TRANSACTION 

    UPDATE Elements.ProductDeparture
    SET DurationDays = 5,       
        FinishDate = DATEADD(day, 4, DepartureDate)
    WHERE DepartureCode LIKE 'PPAF%'
    AND   ProductID = 2359

COMMIT TRANSACTION 
END TRY

BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE()
    ROLLBACK
END CATCH

There is no error coming out of the query (if you run it without the transaction it completes fine). The behaviour is reproduceable (i.e. it happens every time the above query is run, not just intermittently).

This has happened twice now, for completely unrelated queries running in transactions.

Here's the weird bit. We have run it in our dev environment, and our test environment, with no issues. Running it in Production causes the process to not complete, holding a lock on the table until the process is killed. They are all SQL Server 2005 environments.

Are there any SQL Server Database settings that could be causing this weird behaviour? So far roughly 5 developers have looked at it, and never seen anything like it.

Deadlock? And if I recall correctly, 1 process is not equivalent to one SQL statement -- a process can be shared by multiple connections. — ta.speot.is, Dec 20 '11 at 06:38

score 3 · Answer 1 · edited May 23 '17 at 12:31

Some thoughts:

Use SET XACT_ABORT ON to force rollback and lock release in case of any error
This also suppress error 266
Test for an existing transaction first
I suspect this is your problem
Test for transaction state before rollback
ROLLBACK on it's own does nothing: you need TRAN or TRANSACTION.

For more info, see this: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

So, like this

SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SET @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

    UPDATE Elements.ProductDeparture
        SET DurationDays = 5,       
            FinishDate = DATEADD(day, 4, DepartureDate)
        WHERE DepartureCode LIKE 'PPAF%'
        AND   ProductID = 2359

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    PRINT 'Error: ' + ERROR_MESSAGE()
END CATCH

score 1 · Answer 2 · answered Dec 20 '11 at 06:46

Test the @@TRANCOUNT variable - before transaction - if it > 0 - then there is an outer transaction in progress. So - in this case your commit just decrements the value of @@TRANCOUNT and does not actually commits the changes

Only the most outer commit (which decrements @@trancount to 0) do fixes the changes

SQL Server 2005 transaction commit being ignored

2 Answers2