Auto Rollback Explicit Transactions After a Set Time in SQL Server

sql server

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?

Best Answer

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.

Related Solutions

Sql-server – Distributed – across multiple databases or servers – Transactions

Yes this can be done but you need a third party acting as a transaction coordinator. The standard protocol for this is called Two Phase Commit (2PC). This is usually done with a transaction manager acting as the coordinator.

This can also be generalized further to more than two databases. In fact it doesn't even have to be databases as the approach is generally applied to "transactional resources". Besides databases the other common transactional resource is a message queue. The usual example is to perform some work in a database (ex: update an account balance) and also send a message out on a message queue (ex: an outbound order).

For this to work your datasources need to support 2PC, usually via the XA protocol. How to do it depends on the specific type of datasource and programming language (ex: for Java you'd use an XADataSource).

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Best Answer

Related Solutions

Sql-server – Distributed – across multiple databases or servers – Transactions

Sql-server – connection pooling, transactions, nested transaction and rollback

Related Question