0

I have a following stored procedure on my system.

BEGIN TRY
  BEGIN TRAN
  SET LOCK_TIMEOUT 10000
  UPDATE tbl
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
END CATCH

UPDATE tbl went into timeout and ROLLBACK TRAN also failed because of timeout.

Question is: is this possible?

5
  • ROLLBACK TRAN rolls back a transaction. That's it! Please clarify what you are asking Sep 24, 2013 at 11:19
  • I've edited it.
    – newbie123_
    Sep 24, 2013 at 12:00
  • So, your question is that you have a transaction that did neither commit nor rollback? And how or why did this happen - or if it is possible at all? Sep 24, 2013 at 12:24
  • Yeah, the question is - is this possible?
    – newbie123_
    Sep 24, 2013 at 12:38
  • Just curious: why are you not just doing BEGIN TRAN; SET LOCK_TIMEOUT 10000; UPDATE tbl; COMMIT TRAN ? Is it important to hide it if the transaction fails?
    – Twinkles
    Sep 24, 2013 at 12:53

2 Answers 2

3

No, this is not possible.

SQL Server will always finish the ROLLBACK, no matter what. Even if you cut the power to the system in the middle of the ROLLBACK, the ROLLBACK will continue after the power is returned. ROLLBACK also ignores any connection interruptions and LOCK_TIMEOUT settings. In fact, a connection interruption will start a ROLLBACK if there is an open transaction at the time of interruption.

0

Well, i have just run this successfully. Also i would not expect rollback to honor the lock_timout.

BEGIN TRY
  BEGIN TRAN
  SET LOCK_TIMEOUT 100
  select * from sys.sysobjects a, sys.sysobjects b with (holdlock)
PRINT @@TRANCOUNT
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
END CATCH
PRINT @@TRANCOUNT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.