Gli inserimenti in blocco simultanei dovrebbero causare deadlock, con una PK come colonna Identity, in SQL Server?
Quando si analizza un grafico di deadlock, sembra che la vittima sia un inserimento in blocco e che i bloccanti siano lo stesso inserimento in blocco. Utilizzando un bus di messaggi, molti abbonati possono finire per eseguire un inserimento in blocco all'incirca nello stesso momento. Il deadlock di solito si verifica, ma non sempre.
Penso che questi inserimenti in blocco simultanei siano il motivo per cui si sta verificando il deadlock. Ma un collega ha detto che non dovrebbe essere possibile con la mia configurazione. Gli inserti in blocco vengono inseriti nella stessa tabella. E il PK di quella tabella è una colonna di identità. Dovrebbe essere possibile che si verifichino deadlock in questo scenario? Vengono eseguiti all'interno di una transazione.
Questa è la riga di codice che fa gli inserimenti:
await bulkCopy.WriteToServerAsync(dataTableWithEnumStrings);
Questo è dal grafico deadlock. Sia la vittima che i bloccanti mostrano le stesse istruzioni SQL.
Vittima:
Bloccanti:
SQL:
Ecco il codice della copia in blocco:
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);
}
Il diagramma di SentryOne Plan Explorer:
Ecco l'XML (XDL)
Da quello che posso dire, sembra un blocco di pagina. Quindi forse solo troppi inserti contemporaneamente?
Il numero di righe può variare, ma la fascia alta potrebbe essere qualcosa come 4.000.
Ecco lo schema della tabella, generato da uno script di creazione:
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
Ho appena notato che il PK del tavolo ha questo:
ALLOW_PAGE_LOCKS = ON
Ad essere onesto, non ne so abbastanza per sapere se dovrei modificarlo.
Ed ecco l'indice non cluster che è anche sul tavolo:
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]
Versione di SQL Server: 14.0.3192.2
Modello di recupero DB: COMPLETO
Risposte
Non so perché questo sia un deadlock e non posso riprodurlo. Ma come molti scenari di deadlock non è necessario comprenderli completamente per risolverli.
Un deadlock è causato da un blocco troppo piccolo e troppo tardi e in genere possono essere risolti forzando un blocco precedente e più esclusivo. E il modo migliore per gestire il blocco esplicito in SQL Server è con sp_getapplock . Ciò forzerà la serializzazione delle sessioni di caricamento di massa e il caricamento una alla volta senza fare affidamento sul normale blocco riga / pagina per farlo. Poiché tutte le nuove righe devono andare alla fine dell'indice, i caricamenti non possono essere eseguiti in parallelo all'inizio.
Prima di chiamare SqlBulkCopy, eseguilo sul tuo 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();
}
O poiché la tabella di destinazione non è un heap, dovresti ottenere l'accesso esclusivo con SqlBulkCopyOptions.TableLock. Ma tieni presente che se non sei in modalità READ COMMITTED SNAPSHOT ciò bloccherà tutte le letture sulla tabella, e in ogni caso bloccherà altre scritture non in blocco.