Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. –  Paul White Sep 24 '13 at 11:17
    
ROLLBACK TRAN rolls back a transaction. That's it! Please clarify what you are asking –  Mitch Wheat Sep 24 '13 at 11:19
    
I've edited it. –  newbie123_ Sep 24 '13 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? –  ypercube Sep 24 '13 at 12:24
    
Yeah, the question is - is this possible? –  newbie123_ Sep 24 '13 at 12:38

2 Answers 2

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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