C# 事务（进程 ID）在锁定资源上与另一个进程发生死锁，并已被选为死锁牺牲品。重新运行事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9212255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
提问by user1018213
I have a C# application which is inserting data into SQL Server (2008) table using stored procedure. I am using multi-threading to do this. The stored procedure is being called from inside the thread. Now my stored procedure is using "tablock" while inserting data. While executing this code I am getting the following error: "Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
我有一个 C# 应用程序，它使用存储过程将数据插入到 SQL Server (2008) 表中。我正在使用多线程来做到这一点。正在从线程内部调用存储过程。现在我的存储过程在插入数据时使用“tablock”。执行此代码时，我收到以下错误：“事务（进程 ID）在锁定资源上与另一个进程发生死锁，并已被选为死锁受害者。重新运行事务。”
Can anyone please help me with any solution to this?
任何人都可以帮我解决这个问题吗？
回答by Ben
This occurs when two Sql Server processes are accessing the same resources, but in a different order. Therefore they end up both waiting for the other process, which is a deadlock.
当两个 Sql Server 进程以不同的顺序访问相同的资源时，就会发生这种情况。因此他们最终都在等待另一个进程，这是一个死锁。
There are a number of ways to prevent it, including:
有很多方法可以防止它，包括：
- Avoid taking unneccessary locks. Review the transaction isolation level required for the query, use
with (nolock)locking hint for queries where appropriate. - Make sure that when taking locks you take locks on objects in the same order in each query.
- 避免使用不必要的锁。查看查询所需的事务隔离级别，
with (nolock)在适当的情况下对查询使用锁定提示。 - 确保在锁定时，您在每个查询中以相同的顺序锁定对象。
E.g. if Proc1 locks table1 and then table2, but Proc2 locks table2 and then table1, the problem can arise. You can rewrite either proc to take locks in the same order to avoid this problem.
例如，如果 Proc1 锁定 table1 然后 table2，但 Proc2 锁定 table2 然后 table1，就会出现问题。您可以重写任一 proc 以按相同顺序获取锁以避免此问题。
回答by Mez
You can encapsulate your query in a TRY CATCH block, and catching error numbers (related to locks)
您可以将查询封装在 TRY CATCH 块中，并捕获错误号（与锁相关）
Then you can automate retries, up to a certain number.. So you would do something like the following;
然后你可以自动重试，最多达到一定的次数。所以你会做如下的事情；
DECLARE @RetryNo Int = 1
,@RetryMaxNo Int = 5;
WHILE @RetryNo < @RetryMaxNo
BEGIN
BEGIN TRY
-- put your query that generates locks here....
SELECT @RetryNo = @RetryMaxNo;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (1204, 1205, 1222)
BEGIN
SET @RetryNo += 1;
-- it will wait for 10 seconds to do another attempt
WAITFOR DELAY '00:00:10';
END
ELSE
THROW;
END CATCH
END
You can also use table hints such as UPDLOCK.
您还可以使用表提示，例如UPDLOCK。
回答by xfar
you can use from Lock object
您可以从 Lock 对象中使用
static object _lock = new object();
public static void _main()
{
lock (_lock)
{
_bulkcopy(myData);
}
}
public static void _bulkcopy(DataTable dt)
{
try
{
using (var connection = new SqlConnection(ConfigurationSettings.AppSettings.Get("DBConnection")))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = 100;
bulkCopy.DestinationTableName = "dbo.MyTable";
try
{
bulkCopy.WriteToServer(dt);
}
catch (Exception)
{
transaction.Rollback();
connection.Close();
}
}
transaction.Commit();
}
}
catch { }
}
回答by Naveen
Be sure what field you are going to update or insert, this field have non clustered index. If not availble you can first create nonclustered index of this field on this table and after create follow below steps.
确定您要更新或插入的字段，该字段具有非聚集索引。如果不可用，您可以先在此表上创建此字段的非聚集索引，然后按照以下步骤创建。
Right click on table and select properties.
Select Option in right panel in properties.
In lock tab Allow page lock make 'False' and Allow row lock must be 'True' and then press Ok.
- Press New Query button and write command 'update statistics tablename' and execute
- Rebuild non clustered index.
右键单击表并选择属性。
在属性的右侧面板中选择选项。
在锁定选项卡中，允许页面锁定使 'False' 和允许行锁定必须为 'True'，然后按确定。
- 按“新建查询”按钮并写入命令“更新统计表名”并执行
- 重建非聚集索引。
回答by Decula
Here is a solution from MSDN by S Kumar Dubey
这是 S Kumar Dubey 来自 MSDN 的解决方案
Execute SP: SP_LOCK In Results you will get SPID, DBID, OBJID, INDID, TYPE, RESOURCE, MODE, STATUS Now check the status column, if it is showing wait then kill that SPID. To kill a particular SPID Execute SP: Kill 65 (Where 65 is SPID)
执行 SP: SP_LOCK 在结果中你会得到 SPID、DBID、OBJID、INDID、TYPE、RESOURCE、MODE、STATUS 现在检查状态列，如果它显示等待然后杀死那个 SPID。要终止特定的 SPID 执行 SP：Kill 65（其中 65 是 SPID）
It seems you need to be the SQL server admin to resolve this issue.
您似乎需要成为 SQL 服务器管理员才能解决此问题。
