如何捕捉所造成的僵局SQLEXCEPTION？ [英] How to catch SqlException caused by deadlock?

问题描述

这是一个.NET 3.5 / C＃应用程序，我想搭上 SQLEXCEPTION ，但只有当它是由死锁在SQL Server 2008年实例。

From a .NET 3.5 / C# app, I would like to catch SqlException but only if it is caused by deadlocks on a SQL Server 2008 instance.

典型的错误信息是事务（进程ID 58）已被死锁的锁资源与另一个进程，并已被选作死锁牺牲品。重新运行该事务。

不过，这似乎并没有成为一个记录错误code 了解此异常。

Yet, it does not seem to be a documented error code for this exception.

筛选异常对僵局的关键字在他们的消息的presence似乎是一个非常丑陋的方式来实现这一行为。是否有人知道这样做的正确方法？

Filtering exception against the presence of the deadlock keyword in their message seems a very ugly way to achieve this behavior. Does someone know the right way of doing this?

推荐答案

选择微软公司的SQL Server的特定错误$ C $下死锁是1205所以你需要处理的SQLException并检查了。因此，如如果为所有其他类型的SQLException你想要的泡沫例外了：

The Microsft SQL Server-specific error code for a deadlock is 1205 so you'd need to handle the SqlException and check for that. So, e.g. if for all other types of SqlException you want the bubble the exception up:

catch (SqlException ex)
{
    if (ex.Number == 1205)
    {
        // Deadlock 
    }
    else
        throw;
}

一个方便的事情要做，以找到实际的SQL错误code对于给定的消息，是看在sys.messages在SQL Server中。

A handy thing to do to find the actual SQL error code for a given message, is to look in sys.messages in SQL Server.

例如。

SELECT * FROM sys.messages WHERE text LIKE '%deadlock%' AND language_id=1033

这是另一种方式来处理死锁（从SQL Server 2005及以上），是使用TRY在存储过程中做... CATCH支持：

An alternative way to handle deadlocks (from SQL Server 2005 and above), is to do it within a stored procedure using the TRY...CATCH support:

BEGIN TRY
    -- some sql statements
END TRY
BEGIN CATCH
    IF (ERR_NUMBER() = 1205)
        -- is a deadlock
    ELSE
        -- is not a deadlock
END CATCH

有一个完整的示例这里如何实现纯粹的内SQL死锁重试逻辑MSDN中

There's a full example here in MSDN of how to implement deadlock retry logic purely within SQL.

这篇关于如何捕捉所造成的僵局SQLEXCEPTION？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！