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?
Best Answer
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 theROLLBACK
, theROLLBACK
will continue after the power is returned.ROLLBACK
also ignores any connection interruptions andLOCK_TIMEOUT
settings. In fact, a connection interruption will start aROLLBACK
if there is an open transaction at the time of interruption.