The database table remains locked if the client process is killed after the start of the transaction

Question

I have a C # application that manages data in a table in a SQL Server database using transactions. The code is very simple and basically looks like this:

public string ConnectionString; public OleDbConnection DbConnection; public OleDbTransaction DbTransaction; // ... some initialization stuff ... DbTransaction = DbConnection.BeginTransaction(); try { // ... some insert/update/delete here ... DbTransaction.Commit(); } catch (Exception e) { // ... DbTransaction.Rollback(); } 

Now, the client was informed about a situation where the table / rowset remained locked and the active instance of the application was not running. His first assumption was that an error occurred during the transaction and there is no try-catch blocking with rollback (but this is definitely not the case, as there is proper error handling). I can reproduce the situation if I set a breakpoint in the debugger before DbTransaction.Commit(); and then kill the process from the windows task manager. Then the transaction remains open (I see that it is running DBCC OPENTRAN ), and the lock remains, which prevents further work with a new instance of the application.

My question is: how can I safely deal with this situation - the process is killed after the start of the transaction and does not have the ability to commit / cancel the transaction? As far as I know, I can’t recognize if the application is killed from the Task Manager (tab "Process"). Can I somehow automatically terminate the transaction (for example, after some timeout) or what else can I do? Please, help.

Answer 1

maybe you should

 SET XACT_ABORT ON 

therefore, the sql server automatically rolls back the current transaction in case of an error.

http://technet.microsoft.com/de-de/library/ms188792.aspx