sql中的锁定资源死锁

Question

我在windows应用程序中工作c#多线程功能(5线程(在用我的内部代码删除时，超过2M的数据突然发生死锁错误，帮助如何纠正和解决问题错误：事务(进程ID 59(在锁定资源上与另一个进程死锁，已被选为死锁牺牲品。重新运行事务

我参考了网站，找到了一个查询来解决这个问题。我提到了那个查询，但有时它不起作用，有时它起作用SQL：ALTER DATABASE DBName在上设置READ_COMMITTED_SNAPSHOT并立即回滚GO

帮助如何解决这个问题我应该在代码或sql管理中更改什么

代码

if (SqlCon.State == ConnectionState.Closed)
{
OpenConnection();
}
string sqlStmt = "Delete em from Order em inner join OrderHeader ch on ch.CartonId=em.CartonId and ch.OrderNumber = " + "'" + OrderNumber + "'";
SqlCmd.CommandText = sqlStmt;
SqlCommand.CommandTimeout = TimeoutPeriod();
SqlTransaction transaction;
// Start a local transaction.
transaction = SqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
// Must assign both transaction object and connection
// to Command object for a pending local transaction
SqlCommand.Connection = SqlCon;
SqlCommand.Transaction = transaction;
try
{
int val = SqlCommand.ExecuteNonQuery();
// Attempt to commit the transaction.
transaction.Commit();
return true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection(SqlCon);
}

Answer 1

READ_COMMITTED_SNAPSHOT数据库选项将有助于避免与读取器和写入器(而不是写入器和写入器(发生阻塞/死锁(并发DELETE语句(。

运行以下查询从system_health跟踪获取最近的死锁详细信息：

WITH
CurrentSystemHealthTraceFile AS (
SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
FROM sys.dm_xe_session_targets
WHERE
target_name = 'event_file'
AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%system[_]health%'
)
, AllSystemHealthFiles AS (
SELECT 
REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
FROM CurrentSystemHealthTraceFile
)
, DeadLockReports AS (
SELECT CAST(event_data AS xml) AS event_data
FROM AllSystemHealthFiles
CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
WHERE xed.object_name like 'xml_deadlock_report'
)
SELECT TOP 10
DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
, event_data AS DeadlockReport
FROM DeadLockReports;

通常可以避免死锁的方法是通过查询和索引调优，以便只接触任务所需的那些行。在这种情况下，Order.CartonID上的索引OrderHeader上的复合(理想情况下是唯一的(索引(OrderID，CartonID(看起来很有用。

此外，使用与列数据类型匹配的OrderID参数类型对查询进行参数化。