Auto rollback Explicit transactions after X amount of time

Question

Is it possible to have SQL Server auto rollback your transactions after a certain amount of time to avoid the blocking/locking that is caused? I have been guilty of this in the past and I hear it from many others that it is a common mistake.

For example: I always use a block such as this When doing deletes/updates.

BEGIN TRAN
--SQL Code Here
ROLLBACK TRAN
COMMIT TRAN

This allows me to see how many rows are affected or perform a select on the data and then I can comment out the ROLLBACK which will automatically COMMIT the TRANSACTION.

However, there is a possibility of the of not having the COMMIT highlighted/selected which causes the transaction to hang. What I am looking for is a setting such as:

BEGIN TRAN 15 Seconds
--SQL Code Here
ROLLBACK TRAN
COMMIT TRAN

This will only keep the transaction open a max of 15 seconds.

If this is not possible, does anyone have a better workflow for this?

Answer 1

You could create a job that checks for open transactions and kills them if they've been open for a period of time and are causing blocking. But you'd have to be careful with this as you could have a large, necessary transaction that takes a while that you don't want to get killed. Imagine rolling back an index rebuild that's been running for an hour on Standard Edition.

You can use sp_WhoIsActive to find open transactions that are causing blocking, though it'll take a bit of work.

But I would recommend that you don't do this and instead are diligent with your transactions in Management Studio. Any window that I ever started a transaction in gets closed when I am done. And if there's still an open transaction in it, SSMS will warn me about it so that I can commit/rollback.

Answer 2

I don't like leaving things to chance, so something like this means you always commit or rollback.

DECLARE @testing char(1) = 'Y'

BEGIN TRAN

--Do stuff

IF @@TRANCOUNT > 0
 IF @testing = 'Y'
 BEGIN
  -- Perform some tests/queries to check the update is correct
  -- ...then...
  ROLLBACK 
  RAISERROR(N'THE TRANSACTION WAS AUTOMATICALLY ROLLED BACK',0,0)
 END
 ELSE
 BEGIN
  COMMIT
  RAISERROR(N'THE TRANSACTION WAS AUTOMATICALLY COMMITTED',0,0)
 END

Answer 3

If it's just for use in your SSMS session windows, you could do something like this:

BEGIN TRAN
--SQL Code Here

WAITFOR DELAY '00:00:30.000'

ROLLBACK TRAN
COMMIT TRAN

It's maybe not a great solution. You still have to remember to highlight it, and it may roll back a modification you want to keep.

Answer 4

I did find that I could change my workflow a bit that seems to hold some merit and might appeal to some people better. Instead of commenting out a section of the code, you can use this base instead.

 BEGIN TRAN 
--SQL CODE

IF @@TRANCOUNT >0 
BEGIN
ROLLBACK TRAN
PRINT 'TRANSACTION WAS AUTOMATICALLY ROLLED BACK'
END

The workflow would be that instead of commenting out the ROLLBACK TRAN, you would instead add the COMMIT TRAN right above the IF statement. Though it still doesn't fix the problem if you do not hight the code.