实体框架死锁 - 如何重新运行事务?

发布于 2024-12-20 23:19:23 字数 2094 浏览 7 评论 0原文

这里的情况有点困难:我们的系统偶尔会遇到死锁。而且我在数据库并发方面根本没有很强的背景。

System.Data.SqlClient.SqlException: Transaction (Process ID 69) was deadlocked on
lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.

有多个应用程序访问数据库:使用实体框架访问数据库的主 MVC 应用程序和几个简单的控制台应用程序,每个应用程序使用 ADO.NET 和原始 SQL 查询数据库,并通过 BinaryTap ActiveRecord 插入数据。

不幸的是,我是客户组织的 FNG,所以我无法部署和测试新想法。另外,我们使用的是 SSMS Express,因此我无法访问 SQL Profiler。但立即解决问题并不重要,更重要的是记录我对问题的分析。

当错误消息说我应该重新运行事务时,它是否属实?这是我们的 DaoBase - 我们为每个 HttpContext 使用一个 ObjectContext(通过 Db 属性)。我们总是将 Dao 更新(但不是查询)放在 SafeAction 中,以便它们包含在事务中。我是否尝试正确重新运行交易?

public abstract class DaoBase
{
    protected static CaseMateEntities Db
    {
        get
        {
            return ContextHelper<CaseMateEntities>.GetCurrentContext();
        }
    }


    protected static void SafeAction(Action<ObjectContext> action)
    {
        Exception exception = null;

        try {
            using (var scope = new TransactionScope()) {
                try {
                    if (Db.Connection.State != ConnectionState.Open)
                        Db.Connection.Open();

                    if (action != null)
                        action(Db);

                    Db.SaveChanges(SaveOptions.DetectChangesBeforeSave);

                    scope.Complete();
                } catch (Exception ex) {
                    exception = ex;
                    if (exception is UpdateException)
                        // TODO: Is this a proper way to rerun a transaction?
                        scope.Complete();
                }
            }

            if (exception == null) {
                Db.AcceptAllChanges();
            } else {
                throw exception;
            }
        } finally {
            Db.Connection.Close();
        }
    }
}

其他应用程序通过 ADO.NET/Raw SQL 查询数据库。它们各自的 SELECT 语句没有 WITH (NOLOCK) 指定 - 也许它们应该?是否存在您确实希望纯查询锁定的情况?查询会创建什么类型的锁:行锁和页锁?对于实体框架生成的查询怎么样,我应该告诉 EF 不要锁定查询吗?

感谢所有读到这里的人。我知道这是一个复杂的问题,我有很多阅读要做。

Got a bit of a tough situation here: we're experiencing occasional deadlocks in our system. And I don't have a strong background in database concurrency at all.

System.Data.SqlClient.SqlException: Transaction (Process ID 69) was deadlocked on
lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.

There are several applications accessing the database: the main MVC app that accesses the database using Entity Framework and several simple console apps that each query the DB with ADO.NET and raw SQL, and insert data via BinaryTap ActiveRecord.

Unfortunately, I'm the FNG at the client organization, so I can't deploy and test new ideas. Also, we're using SSMS Express so I don't have access to SQL Profiler. But it's less important that I fix the problem immediately and more important that I document my analysis of the problem.

Is there any truth to the error message when it says I should rerun the transaction? Here's our DaoBase - we're using one ObjectContext per HttpContext (via the Db property). We're always putting our Dao updates (but not queries) in SafeAction so they get wrapped in a transaction. Am I attempting to rerun the transaction properly?

public abstract class DaoBase
{
    protected static CaseMateEntities Db
    {
        get
        {
            return ContextHelper<CaseMateEntities>.GetCurrentContext();
        }
    }


    protected static void SafeAction(Action<ObjectContext> action)
    {
        Exception exception = null;

        try {
            using (var scope = new TransactionScope()) {
                try {
                    if (Db.Connection.State != ConnectionState.Open)
                        Db.Connection.Open();

                    if (action != null)
                        action(Db);

                    Db.SaveChanges(SaveOptions.DetectChangesBeforeSave);

                    scope.Complete();
                } catch (Exception ex) {
                    exception = ex;
                    if (exception is UpdateException)
                        // TODO: Is this a proper way to rerun a transaction?
                        scope.Complete();
                }
            }

            if (exception == null) {
                Db.AcceptAllChanges();
            } else {
                throw exception;
            }
        } finally {
            Db.Connection.Close();
        }
    }
}

The other apps query the database through ADO.NET/Raw SQL. Their respective SELECT statement do not have the WITH (NOLOCK) designation - perhaps they should? Is there any circumstance where you do want pure queries to lock? And what types of locks would a query create: row and page locks? What about the queries generated by Entity Framework, should I tell EF not to lock on queries?

Thanks to everyone who read this far. I know this is a complex issue and I've got lots of reading to do..

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

謸气贵蔟 2024-12-27 23:19:23

死锁分析需要访问SQL Profiler来查看死锁发生时数据库服务器上的情况。特别是如果您不是在数据库上执行的 SQL 查询的所有者,这是必要的。使用 EF 时,您不是所有者 - EF 生成查询。必须解决数据库查询和事务中执行的数据库操作顺序的死锁=您必须知道数据库中发生了什么。

使用隔离级别需要非常了解您的应用程序以及数据库上运行的任何其他应用程序。如果将隔离级别设置为读取未提交的内容,那么您将违反事务的核心规则之一 - 隔离。在读取未提交模式下运行的事务可以读取其他事务未提交的数据(脏数据) - 如果该事务回滚,您的代码可以处理无效数据并将数据库移动到不一致状态(或在某些数据库约束上失败)。 SQL 查询中的 NOLOCK 提示与全局使用未提交读相同,但该提示仅针对单个查询中的单个表。

使用 NOLOCK 或未提交读取是否不好?不可以,但是您必须绝对确定何时执行此操作 = 您必须了解您的应用程序(以及使用数据库的其他应用程序),并确保这些用于获取未提交数据的查询不会用于任何其他数据修改或任何风险决策。

TransactionScope 的默认隔离级别是可序列化的,这是对事务最严格的级别(= 更频繁地导致死锁)。您应该首先使用读取已提交隔离级别(但必须确保在事务期间不会从数据库中多次读取相同的数据)来减少数据库锁定,但这很可能无法解决问题(它可以降低频率) )。 详细了解有关隔离级别的信息。

Deadlock analysis require access to SQL profiler to see the situation on database server at time off deadlock. Especially if you are not owner of SQL queries executed on DB this is necessary. When using EF, you are not the owner - EF generates queries. Deadlock must be solved on database queries and order of database operations performed withing transaction = you must know what happened in the database.

Playing with isolation level requires very good knowledge of both your application and any other application running on the database. If you set isolation level to read uncommitted you are breaking one of the core rules of transactions - isolation. Transactions running in read uncommitted mode can read data uncommitted (dirty data) by other transaction - if that transaction rolls back your code can work with invalid data and move database to inconsistent state (or fail on some database constraint). NOLOCK hint in SQL query is the same as using read uncommitted globally but the hint targets only single table in single query.

Is it bad to use NOLOCK or read uncommitted? No but you must be absolutely sure when to do that = you must understand your application (and other applications using the database) and make sure that these queries used to get uncommitted data are not used for any other data modifications or any risk decisions.

Default isolation level for TransactionScope is serializable which is the most restrictive level for transaction (= causes deadlock more often). You should start by using Read committed isolation level (but you must ensure that same data are not read multiple times from the database during a transaction) to reduce database locking but it will most probably doesn't solve the problem (it can reduce the frequency). More about isolation levels.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文