5

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?

0
4

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.

1
  • 1
    Thank you Tara, This makes sense and your right, I definitely wouldn't want to kill a job that I need such as a report, index, or any type of long running job. I did notice that there was a column for the program name, this can list your job name as "SQLAgent - TSQL JobStep (Job 0x8469FD7F1E8C2C4CB15BCA3E7CFAEB9D : Step 1)" for example.. but it is still best not to attempt this and just be more diligent. Just trying to avoid the fire :) Oct 22 '16 at 0:30
3

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
2

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.

0
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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