SQL Serverで、同時一括挿入により、PKがID列であるデッドロックが発生する必要がありますか？
デッドロックグラフを分析すると、被害者はバルクインサートであり、ブロッカーは同じバルクインサートであるように見えます。メッセージバスを使用すると、多くの加入者がほぼ同時に一括挿入を行うことになります。デッドロックは通常発生しますが、常に発生するとは限りません。
これらの同時一括挿入がデッドロックが発生している理由だと思います。しかし、同僚は、私の設定ではそれが不可能であるべきだと述べました。バルクインサートは同じテーブルに挿入されています。そして、そのテーブルのPKはID列です。このシナリオでデッドロックが発生する可能性はありますか？それらはトランザクション内で行われます。
これは、挿入を行うコード行です。
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
DBリカバリモデル：FULL
回答
なぜこれが行き詰まっているのかわかりませんし、再現できません。ただし、多くのデッドロックシナリオと同様に、それらを完全に理解して修正する必要はありません。
デッドロックは、ロックがほとんど、遅すぎることによって発生します。通常、デッドロックは、より早く、より排他的なロックを強制することで解決できます。また、SQL Serverで明示的なロックを処理する最良の方法は、sp_getapplockを使用することです。これにより、バルクロードセッションが強制的にシリアル化され、通常の行/ページロックに依存せずに一度に1つずつロードされます。すべての新しい行はインデックスの最後に配置する必要があるため、最初からロードを実際に並行して実行することはできません。
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モードでない場合は、他の非バルク書き込みをブロックすることに注意してください。