SQL事务处于死锁状态 [英] SQL Transaction was deadlocked

问题描述

有时候我在不太忙的SQL服务器上得到这种异常：

Sometimes I get this kind of exception on not very busy SQL server:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Line number: 1
Error Number: 1205
Procedure: 
Server name: P01
Error Source: .Net SqlClient Data Provider
Error State: 47

我无法重现。我试图在同一时间从不同的客户端运行多个查询，但没有出现。
当程序或内部触发器发生时，处理这种问题的最佳方式是什么？我的意思是，如何重新运行交易？

I'm not able to reproduce it. I tried to run several queries from different clients at the same time, but it didn't show up. What is the best way to handle this kind of issue when it happens inside procedure or inside trigger? I mean, how to rerun the transaction?

当触发器调用的过程中发生异常时，如何执行此操作，这是由某些过程所做的插入调用的（那个是：procedure01 - > insert - > trigger - > procedure02！）

How to do it when exception occurs inside procedure called from the trigger, which was called by the insert made by some procedure (that is: procedure01 -> insert -> trigger -> procedure02 !)

推荐答案

我建议你从两个观点。

1. 陷阱或捕获死锁错误，以便您可以重新运行由SQL选择的死锁受害者的事务服务器数据库引擎。

1. Trap or Catch Deadlock Errors so that you can re-run the transaction that is chosen as the Deadlock Victim by the SQL Server database engine.

了解导致您的死锁事件是什么。
您可以在两个方法是运行SQL Server Profiler跟踪来捕获并记录死锁事件，或者您可以启用一些 SQL Server 跟踪标志将会将死锁事件的详细信息记录到SQL Server错误日志。

Find out what is causing your Deadlock Events. You can do this in one of two ways, either run a SQL Server Profiler Trace to catch and record the Deadlock Event or you can enable some SQL Server Trace Flags that will record the details of the Deadlock Event to the SQL Server Error Log.

在绝大多数

In the vast majority of cases, you can identify the cause of your Deadlock Events and remedy the situation through either a structural change in the database schema or a logical change to the code involved/responsible for the Deadlock Event.

要进一步阅读，请查看：

For further reading take a look at:

• 如何使用SQL Server Profiler追踪死锁


• 最大限度地减少死锁


• 检测并结束死锁

• How to track down Deadlocks Using SQL Server Profiler
• Minimizing Deadlocks
• Detecting and Ending Deadlocks

我希望我已经回答了你的问题，但是让我知道如果我可以帮助你r以任何方式。

I hope I've answered your question but do let me know if I can help you further in any way.

这篇关于SQL事务处于死锁状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！