Is there any parameter to prevent long transactions?
I would like to automatically rollback all transaction that takes more than 5 minutes. Is it possibile?
I'm working on a Microsoft SQL server 2008 R2.
Background notice:
I'm asking this because I'm working with many awful application: In case of application exception they show a modal alert, transaction is released ONLY when the alert is closed. I really know this seems a joke, but it isn't! I can not change this apps and I can not fire their developers but I need to solve this problem: long transaction (can be also a simple transaction with low cost) must take less then 5 minute. Otherwise some tables will be locked until modal close.
Best Answer
There is an option called 'query governor cost limit' which takes an integer value. The value you specify is what the max time in seconds that a query can run. Please note that this is based off of the estimated time and value. If a cost exceeds this, the query will NOT be allowed to execute. This is not an exact science, some may run longer and some that are disallowed may run shorter as it's based on an estimated cost.
http://technet.microsoft.com/en-us/library/ms190419(v=sql.105).aspx