SQL Server 中的同時批量插入是否應該導致死鎖，PK 是標識列？

在分析死鎖圖時，看起來受害者是一個大容量插入，而阻塞者是同一個大容量插入。使用消息匯流排，許多訂閱者最終可以大致同時進行批量插入。死鎖通常會發生，但並非總是如此。

我認為這些同時進行的批量插入是發生死鎖的原因。但是一位同事提到我的設置應該不可能。批量插入插入到同一個表中。該表的 PK 是一個標識列。在這種情況下是否可能發生死鎖？它們是在事務中完成的。

這是執行插入的程式碼行：

await bulkCopy.WriteToServerAsync(dataTableWithEnumStrings);

這是來自死鎖圖。受害者和阻止者都顯示相同的 SQL 語句。

受害者：

阻滯劑：

SQL：

這是批量複製程式碼：

   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
   {
       bulkCopy.BulkCopyTimeout = 0;
       bulkCopy.BatchSize = 10000;
       bulkCopy.DestinationTableName = destinationTableName;

       var dataTable = ToDataTable(histories, columnNames);
       var dataTableWithEnumStrings = ConvertDataTableEnum(dataTable);

       // Add column mappings so we don't have to worry about order when adding new columns/properties.
       foreach (DataColumn column in dataTableWithEnumStrings.Columns)
       {
           // The column mappings are case sensitive, so grab the destination column so we can use its casing.
           string destColumn = columnNames.Single(x => x.Equals(column.ColumnName, StringComparison.OrdinalIgnoreCase));
           bulkCopy.ColumnMappings.Add(column.ColumnName, destColumn);
       }

       await bulkCopy.WriteToServerAsync(dataTableWithEnumStrings);
   }

SentryOne 計劃資源管理器圖：

這是XML (XDL)

據我所知，它看起來像一個頁面鎖。所以可能一次插入太多？

行數可能會有所不同，但高端可能是 4,000 行。

這是表的架構，由創建腳本生成：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AccrualHistory](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [CompanyID] [int] NOT NULL,
   [EmployeeID] [bigint] NOT NULL,
   [AccrualID] [bigint] NOT NULL,
   [ChangeAmount] [decimal](12, 6) NOT NULL,
   [ProcessingDateTime] [datetime] NOT NULL,
   [AppliedDate] [date] NOT NULL,
   [ActionApplication] [varchar](20) NOT NULL,
   [ActionDescription] [varchar](300) NOT NULL,
   [LastChangeDate] [datetime2](7) NULL,
   [LastChangeUserID] [bigint] NOT NULL,
   [FrequencyType] [char](1) NOT NULL,
   [ServerName] [varchar](100) NOT NULL,
   [ApplicationName] [varchar](100) NOT NULL,
   [CalculationID] [uniqueidentifier] NULL,
   [CalendarID] [uniqueidentifier] NULL,
   [IncludedInBalance] [bit] NOT NULL,
CONSTRAINT [PK_AccrualHistory_ID] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AccrualHistory] ADD  CONSTRAINT [dft_AccrualHistory_LastChangeDate]  DEFAULT (getdate()) FOR [LastChangeDate]
GO

ALTER TABLE [dbo].[AccrualHistory] ADD  CONSTRAINT [dft_AccrualHistory_LastChangeUserID]  DEFAULT ((0)) FOR [LastChangeUserID]
GO

ALTER TABLE [dbo].[AccrualHistory] ADD  CONSTRAINT [DF_AccrualHistory_FrequencyType]  DEFAULT ('') FOR [FrequencyType]
GO

ALTER TABLE [dbo].[AccrualHistory] ADD  CONSTRAINT [DF_AccrualHistory_ServerName]  DEFAULT ('') FOR [ServerName]
GO

ALTER TABLE [dbo].[AccrualHistory] ADD  CONSTRAINT [DF_AccrualHistory_ApplicationName]  DEFAULT ('') FOR [ApplicationName]
GO

ALTER TABLE [dbo].[AccrualHistory] ADD  DEFAULT ((1)) FOR [IncludedInBalance]
GO

我剛剛注意到桌子的 PK 有這個：

ALLOW_PAGE_LOCKS = ON

老實說，我不知道是否應該改變它。

這是也在桌子上的非聚集索引：

CREATE NONCLUSTERED INDEX [IX_AccrualHistory_EmployeeID_AccrualID] ON [dbo].[AccrualHistory]
(
   [EmployeeID] ASC,
   [AccrualID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

SQL Server 版本：14.0.3192.2

數據庫恢復模式：FULL

我不知道為什麼這是死鎖，我無法重現它。但就像許多死鎖場景一樣，沒有必要完全理解它們來修復它們。

死鎖是由鎖定太少和太晚引起的，通常可以通過強制更早和更排他的鎖來解決。他們在 SQL Server 中處理顯式鎖定的最佳方法是使用sp_getapplock。這將強制您的批量載入會話序列化，並一次載入一個，而不依賴於正常的行/頁鎖定來執行此操作。由於所有新行都必須放在索引的末尾，因此負載不能真正並行執行。

在呼叫 SqlBulkCopy 之前，在 SqlTransaction 上執行：

static void GetAppLock(string name, SqlTransaction tran)
{
   var cmd = new SqlCommand("sp_getapplock", tran.Connection);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Transaction = tran;
   var pResource = cmd.Parameters.Add(new SqlParameter("@Resource", SqlDbType.NVarChar, 255));
   pResource.Value = name;

   var pLockMode = cmd.Parameters.Add(new SqlParameter("@LockMode", SqlDbType.VarChar, 32));
   pLockMode.Value = "Exclusive";

   cmd.ExecuteNonQuery();
}

或者由於目標表不是堆，您應該使用SqlBulkCopyOptions.TableLock. 但請注意，如果您未處於 READ COMMITTED SNAPSHOT 模式，這將阻止對錶的所有讀取，並且在任何情況下都會阻止其他非批量寫入。

引用自：https://dba.stackexchange.com/questions/274262