如何在不使用 catch Exception 的情况下从 .NET 应用程序检测 sql server 超时 [英] how to detect sql server timeout from .NET application without using catch Exception

问题描述

在我当前的应用程序中，我通过调用 T-SQL 更新命令来执行更新.问题是当时其他用户锁定了同一条记录.

In my current application, i am performing an update by invoking T-SQL Update command. The problem is when the same record is locked by other users at that time.

在.NET应用程序中，应用程序会等到SQL Server超时，然后抛出SqlException超时.

At .NET application, the application will wait until SQL Server timeout, then it will throw the SqlException timeout.

是否可以先检查特定记录是否被其他进程锁定而不是捕获异常?

Is it possible to perform a check first whether a particular record is locked by other process rather than catching the exception ?

推荐答案

不，不是真的.

标准方法是使用 try/catch 并处理 SqlException 编号 1205(死锁受害者)，然后重试查询:

The standard way is to use try/catch and handle SqlException Number 1205 (deadlock victim), and retry your query:

    try
    {
        // do stuff...
    }
    catch (SqlException sqlEx)
    {
        switch (sqlEx.Number)
        {
            case -2:   // Client Timeout
            case 701:  // Out of Memory
            case 1204: // Lock Issue 

            case 1205: // >>> Deadlock Victim
                // handle deadlock
                break;

            case 1222: // Lock Request Timeout
            case 2627: // Primary Key Violation
            case 8645: // Timeout waiting for memory resource 
            case 8651: // Low memory condition 
            ...
        }
    }

[注意:为了紧凑，没有添加 break 语句

[Note: break statements not added for compactness

另请注意，通过提供适当的覆盖索引可以消除许多锁定问题.

Also note, many locking issues can be eliminated by providing the appropriate covering indexes.

这篇关于如何在不使用 catch Exception 的情况下从 .NET 应用程序检测 sql server 超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！