gpt4 book ai didi

sql-server - 仅插入新行而不执行任何选择时 SQL Server 死锁

转载 作者:行者123 更新时间:2023-12-03 16:10:44 25 4
gpt4 key购买 nike

我在我的应用程序中看到持续的死锁,即使它不执行 select 语句、delete 语句和 update 语句。它只是插入全新的数据。
TL;博士 : 好像和外键有关。如果我删除它,那么我根本不会遇到任何死锁。但由于显而易见的原因,这不是一个可接受的解决方案。
鉴于下表结构

CREATE TABLE [dbo].[IncomingFile]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[ConcurrencyVersion] RowVersion NOT NULL,
CONSTRAINT [PK_IncomingFile] PRIMARY KEY CLUSTERED([Id])
)
GO

CREATE TABLE [dbo].[IncomingFileEvent]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[ConcurrencyVersion] RowVersion NOT NULL,
[IncomingFileId] UNIQUEIDENTIFIER NOT NULL,
CONSTRAINT [PK_IncomingFileEvent] PRIMARY KEY CLUSTERED([Id]),
CONSTRAINT [FK_IncomingFileEvent_IncomingFileId]
FOREIGN KEY ([IncomingFileId])
REFERENCES [dbo].[IncomingFile] ([Id])
)
GO
当我遇到多个并发任务插入数据时,我总是看到一个死锁。 READ_COMMITTED_SNAPSHOT在我的数据库选项中启用(即使我没有阅读)。
这是将重现该问题的代码。如果您没有遇到问题,请增加 NumberOfTasksPerCpu程序顶部的常量。
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace SqlServerDeadlockRepro
{
class Program
{
private const int NumberOfTasksPerCpu = 8; // Keep increasing this by one if you do not get a deadlock!
private const int NumberOfChildRows = 1_000;
private const string MSSqlConnectionString = "Server=DESKTOP-G05BF1U;Database=EFCoreConcurrencyTest;Trusted_Connection=True;";

private static int NumberOfConcurrentTasks;

static async Task Main(string[] args)
{
NumberOfConcurrentTasks = Environment.ProcessorCount * NumberOfTasksPerCpu;

var readySignals = new Queue<ManualResetEvent>();
var trigger = new ManualResetEvent(false);
var processingTasks = new List<Task>();
for (int index = 0; index < NumberOfConcurrentTasks; index++)
{
var readySignal = new ManualResetEvent(false);
readySignals.Enqueue(readySignal);
var task = CreateDataWithSqlCommand(trigger, readySignal);
processingTasks.Add(task);
}
Console.WriteLine("Waiting for tasks to become ready");
while (readySignals.Count > 0)
{
var readySignalBatch = new List<WaitHandle>();
for(int readySignalCount = 0; readySignals.Count > 0 && readySignalCount < 64; readySignalCount++)
{
readySignalBatch.Add(readySignals.Dequeue());
}
WaitHandle.WaitAll(readySignalBatch.ToArray());
}
Console.WriteLine("Saving data");
var sw = Stopwatch.StartNew();
trigger.Set();
await Task.WhenAll(processingTasks.ToArray());
sw.Stop();
Console.WriteLine("Finished - " + sw.ElapsedMilliseconds);
}

private static int TaskNumber = 0;
private static async Task CreateDataWithSqlCommand(ManualResetEvent trigger, ManualResetEvent readySignal)
{
await Task.Yield();
using var connection = new SqlConnection(MSSqlConnectionString);
await connection.OpenAsync().ConfigureAwait(false);
var transaction = (SqlTransaction)await connection.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted).ConfigureAwait(false);

Console.WriteLine("Task " + Interlocked.Increment(ref TaskNumber) + $" of {NumberOfConcurrentTasks} ready ");
readySignal.Set();
trigger.WaitOne();
Guid parentId = Guid.NewGuid();
string fileCommandSql = "insert into IncomingFile (Id) values (@Id)";
using var fileCommand = new SqlCommand(fileCommandSql, connection, transaction);
fileCommand.Parameters.Add("@Id", System.Data.SqlDbType.UniqueIdentifier).Value = parentId;
await fileCommand.ExecuteNonQueryAsync().ConfigureAwait(false);

using var fileEventCommand = new SqlCommand
{
Connection = connection,
Transaction = transaction
};
var commandTextBulder = new StringBuilder("INSERT INTO [IncomingFileEvent] ([Id], [IncomingFileId]) VALUES ");
for (var i = 1; i <= NumberOfChildRows * 2; i += 2)
{
commandTextBulder.Append($"(@p{i}, @p{i + 1})");
if (i < NumberOfChildRows * 2 - 1)
commandTextBulder.Append(',');

fileEventCommand.Parameters.AddWithValue($"@p{i}", Guid.NewGuid());
fileEventCommand.Parameters.AddWithValue($"@p{i + 1}", parentId);
}

fileEventCommand.CommandText = commandTextBulder.ToString();
await fileEventCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
await transaction.CommitAsync().ConfigureAwait(false);
}
}
}
更新
还尝试制作主键 NONCLUSTERED并添加一个 CLUSTERED基于当前日期和时间的索引。
CREATE TABLE [dbo].[IncomingFile]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[ConcurrencyVersion] RowVersion NOT NULL,
[CreatedUtc] DateTime2 DEFAULT GETDATE(),
CONSTRAINT [PK_IncomingFile] PRIMARY KEY NONCLUSTERED([Id])
)
GO
CREATE CLUSTERED INDEX [IX_IncomingFile_CreatedUtc] on [dbo].[IncomingFile]([CreatedUtc])
GO

CREATE TABLE [dbo].[IncomingFileEvent]
(
[Id] UNIQUEIDENTIFIER NOT NULL,
[ConcurrencyVersion] RowVersion NOT NULL,
[IncomingFileId] UNIQUEIDENTIFIER NOT NULL,
[CreatedUtc] DateTime2 DEFAULT GETDATE(),
CONSTRAINT [PK_IncomingFileEvent] PRIMARY KEY NONCLUSTERED([Id]),
CONSTRAINT [FK_IncomingFileEvent_IncomingFileId]
FOREIGN KEY ([IncomingFileId])
REFERENCES [dbo].[IncomingFile] ([Id])
)
GO
CREATE CLUSTERED INDEX [IX_IncomingFileEvent_CreatedUtc] on [dbo].[IncomingFileEvent]([CreatedUtc])
GO
更新 2
我尝试了取自 here 的顺序 guid ,这没有区别。
更新 3
好像和外键有关。如果我删除它,那么我根本不会遇到任何死锁。
更新 4
来自 Sql Server 产品组的一些建议的回复已发布在我原来的 github 问题上。
https://github.com/dotnet/efcore/issues/21899#issuecomment-683404734​​​​​​​

最佳答案

死锁是由于检查参照完整性所需的执行计划。 IncomingFile的全表扫描将大量 (1K) 行插入相关 IncomingFileEvent 时执行 table table 。扫描获取在事务期间持有的共享表锁,当不同的 session 分别在刚刚插入的 IncomingFile 上持有独占行锁时,会导致死锁。行并被另一个 session 排他行锁阻塞。
下面是显示这一点的执行计划:
plan with full scan
避免死锁的一种方法是使用 OPTION (LOOP JOIN) IncomingFileEvent 上的查询提示插入查询:

    var commandTextBulder = new StringBuilder("INSERT INTO [IncomingFileEvent] ([Id], [IncomingFileId]) VALUES ");
for (var i = 1; i <= NumberOfChildRows * 2; i += 2)
{
commandTextBulder.Append($"(@p{i}, @p{i + 1})");
if (i < NumberOfChildRows * 2 - 1)
commandTextBulder.Append(',');

fileEventCommand.Parameters.AddWithValue($"@p{i}", Guid.NewGuid());
fileEventCommand.Parameters.AddWithValue($"@p{i + 1}", parentId);
}
commandTextBulder.Append(" OPTION (LOOP JOIN);");
这是带有提示的计划:
plan with hint
另一方面,请考虑将现有主键更改为下面的主键。从数据建模的角度(识别关系)来看,这是更正确的,并且将提高插入和选择的性能,因为相关行在物理上聚集在一起。
CONSTRAINT [PK_IncomingFileEvent] PRIMARY KEY CLUSTERED(IncomingFileId, Id)

关于sql-server - 仅插入新行而不执行任何选择时 SQL Server 死锁,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63314044/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com