如果客户端进程在事务启动后被终止，则数据库表保持锁定状态

I have a C# application which manipulates data in a table within a SQL Server database using transactions. The code is very simple and basically goes like this:

我有一个C#应用程序,它使用事务处理SQL Server数据库中的表中的数据。代码非常简单,基本上是这样的:

 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, a situation was reported by a customer, that the table/rowset remained locked, while there was no active application instance running. His first guess was, that an error occurred during the transaction and no try-catch-block with a rollback is there (but this is definitely not the case, since there is a proper error handling). I can reproduce the situation, if I set a break point in the debugger before DbTransaction.Commit(); and then kill the process from Windows Task Manager. Then the transaction remains open (I can see it running DBCC OPENTRAN) and a lock remains, which prohibits further working with a new instance of the application.

现在,客户报告了一种情况,即表/行集保持锁定状态,而没有活动的应用程序实例正在运行。他的第一个猜测是,在事务期间发生了错误,并且没有带回滚的try-catch-block(但绝对不是这种情况,因为存在正确的错误处理)。如果我在DbTransaction.Commit()之前在调试器中设置了一个断点,我可以重现这种情况;然后从Windows任务管理器中终止该进程。然后事务保持打开状态(我可以看到它运行DBCC OPENTRAN)并且仍然存在锁定,这会阻止进一步使用应用程序的新实例。

My question: How can I safely handle a situation like this - the process is killed after transaction start and has no chance to commit/rollback the transaction? As far as I know, I cannot recognize, if the application is being killed from the Task Manager ("Process" tab). So can I somehow automatically abort the transaction (e.g. after some timeout) or what else can I do? Please help.

我的问题:我怎样才能安全地处理这样的情况 - 在事务开始后进程被终止并且没有机会提交/回滚事务?据我所知,如果应用程序被任务管理器(“进程”选项卡)杀死,我无法识别。那么我可以以某种方式自动中止交易(例如在一些超时后)或我还能做什么?请帮忙。

1 个解决方案

#1

maybe you should

也许你应该

SET XACT_ABORT ON

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

所以如果出错,sql server会自动回滚当前事务。

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

#1