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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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
BEGIN TRAN; SET LOCK_TIMEOUT 10000; UPDATE tbl; COMMIT TRAN? Is it important to hide it if the transaction fails?