Ok, estoy totalmente perdido en el tema del punto muerto. Simplemente no sé cómo resolver esto.
Tengo estas tres tablas (he eliminado columnas no importantes):
CREATE TABLE [dbo].[ManageServicesRequest] ( [ReferenceTransactionId] INT NOT NULL, [OrderDate] DATETIMEOFFSET(7) NOT NULL, [QueuePriority] INT NOT NULL, [Queued] DATETIMEOFFSET(7) NULL, CONSTRAINT [PK_ManageServicesRequest] PRIMARY KEY CLUSTERED ([ReferenceTransactionId]), ) CREATE TABLE [dbo].[ServiceChange] ( [ReferenceTransactionId] INT NOT NULL, [ServiceId] VARCHAR(50) NOT NULL, [ServiceStatus] CHAR(1) NOT NULL, [ValidFrom] DATETIMEOFFSET(7) NOT NULL, CONSTRAINT [PK_ServiceChange] PRIMARY KEY CLUSTERED ([ReferenceTransactionId],[ServiceId]), CONSTRAINT [FK_ServiceChange_ManageServiceRequest] FOREIGN KEY ([ReferenceTransactionId]) REFERENCES [ManageServicesRequest]([ReferenceTransactionId]) ON DELETE CASCADE, INDEX [IDX_ServiceChange_ManageServiceRequestId] ([ReferenceTransactionId]), INDEX [IDX_ServiceChange_ServiceId] ([ServiceId]) ) CREATE TABLE [dbo].[ServiceChangeParameter] ( [ReferenceTransactionId] INT NOT NULL, [ServiceId] VARCHAR(50) NOT NULL, [ParamCode] VARCHAR(50) NOT NULL, [ParamValue] VARCHAR(50) NOT NULL, [ParamValidFrom] DATETIMEOFFSET(7) NOT NULL, CONSTRAINT [PK_ServiceChangeParameter] PRIMARY KEY CLUSTERED ([ReferenceTransactionId],[ServiceId],[ParamCode]), CONSTRAINT [FK_ServiceChangeParameter_ServiceChange] FOREIGN KEY ([ReferenceTransactionId],[ServiceId]) REFERENCES [ServiceChange] ([ReferenceTransactionId],[ServiceId]) ON DELETE CASCADE, INDEX [IDX_ServiceChangeParameter_ManageServiceRequestId] ([ReferenceTransactionId]), INDEX [IDX_ServiceChangeParameter_ServiceId] ([ServiceId]), INDEX [IDX_ServiceChangeParameter_ParamCode] ([ParamCode]) )
Y estos dos procedimientos:
CREATE PROCEDURE [dbo].[spCreateManageServicesRequest] @ReferenceTransactionId INT, @OrderDate DATETIMEOFFSET, @QueuePriority INT, @Services ServiceChangeUdt READONLY, @Parameters ServiceChangeParameterUdt READONLY AS BEGIN SET NOCOUNT ON; BEGIN TRY /* VYTVOŘ NOVÝ REQUEST NA ZMĚNU SLUŽEB */ /* INSERT REQUEST */ INSERT INTO [dbo].[ManageServicesRequest] ([ReferenceTransactionId] ,[OrderDate] ,[QueuePriority] ,[Queued]) VALUES (@ReferenceTransactionId ,@OrderDate ,@QueuePriority ,NULL) /* INSERT SERVICES */ INSERT INTO [dbo].[ServiceChange] ([ReferenceTransactionId] ,[ServiceId] ,[ServiceStatus] ,[ValidFrom]) SELECT @ReferenceTransactionId AS [ReferenceTransactionId] ,[ServiceId] ,[ServiceStatus] ,[ValidFrom] FROM @Services AS [S] /* INSERT PARAMS */ INSERT INTO [dbo].[ServiceChangeParameter] ([ReferenceTransactionId] ,[ServiceId] ,[ParamCode] ,[ParamValue] ,[ParamValidFrom]) SELECT @ReferenceTransactionId AS [ReferenceTransactionId] ,[ServiceId] ,[ParamCode] ,[ParamValue] ,[ParamValidFrom] FROM @Parameters AS [P] END TRY BEGIN CATCH THROW END CATCH END CREATE PROCEDURE [dbo].[spGetManageServicesRequest] @ReferenceTransactionId INT AS BEGIN SET NOCOUNT ON; BEGIN TRY /* VRAŤ MANAGE SERVICES REQUEST PODLE ID */ SELECT [MR].[ReferenceTransactionId], [MR].[OrderDate], [MR].[QueuePriority], [MR].[Queued], [SC].[ReferenceTransactionId], [SC].[ServiceId], [SC].[ServiceStatus], [SC].[ValidFrom], [SP].[ReferenceTransactionId], [SP].[ServiceId], [SP].[ParamCode], [SP].[ParamValue], [SP].[ParamValidFrom] FROM [dbo].[ManageServicesRequest] AS [MR] LEFT JOIN [dbo].[ServiceChange] AS [SC] ON [SC].[ReferenceTransactionId] = [MR].[ReferenceTransactionId] LEFT JOIN [dbo].[ServiceChangeParameter] AS [SP] ON [SP].[ReferenceTransactionId] = [SC].[ReferenceTransactionId] AND [SP].[ServiceId] = [SC].[ServiceId] WHERE [MR].[ReferenceTransactionId] = @ReferenceTransactionId END TRY BEGIN CATCH THROW END CATCH END
Ahora, estos se usan de esta manera (es un método C# simplificado que crea un registro y luego publica el registro en una cola de microservicio):
public async Task Consume(ConsumeContext<CreateCommand> context) { using (var sql = sqlFactory.Cip) { /*SAVE REQUEST TO DATABASE*/ sql.StartTransaction(System.Data.IsolationLevel.Serializable); <----- First transaction starts /* Create id */ var transactionId = await GetNewId(context.Message.CorrelationId); /* Create manage services request */ await sql.OrderingGateway.ManageServices.Create(transactionId, context.Message.ApiRequest.OrderDate, context.Message.ApiRequest.Priority, services); sql.Commit(); <----- First transaction ends /// .... Some other stuff ... /* Fetch the same object you created in the first transaction */ Try { sql.StartTransaction(System.Data.IsolationLevel.Serializable); var request = await sql.OrderingGateway.ManageServices.Get(transactionId); <----- HERE BE THE DEADLOCK, request.Queued = DateTimeOffset.Now; await sql.OrderingGateway.ManageServices.Update(request); ... Here is a posting to a microservice queue ... sql.Commit(); } catch (Exception) { sql.RollBack(); } /// .... Some other stuff .... }
Ahora mi problema es. ¿Por qué estos dos procedimientos se bloquean? La primera y la segunda transacción nunca se ejecutan en paralelo para el mismo registro.
Aquí está el punto muerto detalle:
<deadlock> <victim-list> <victimProcess id="process1dbfa86c4e8" /> </victim-list> <process-list> <process id="process1dbfa86c4e8" taskpriority="0" logused="0" waitresource="KEY: 18:72057594046775296 (b42d8e559092)" waittime="2503" ownerId="33411557480" transactionname="user_transaction" lasttranstarted="2021-12-01T01:06:15.303" XDES="0x1ddd2df4420" lockMode="RangeS-S" schedulerid="20" kpid="23000" status="suspended" spid="55" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-12-01T01:06:15.310" lastbatchcompleted="2021-12-01T01:06:15.300" lastattention="1900-01-01T00:00:00.300" clientapp="Core Microsoft SqlClient Data Provider" hostpid="11020" isolationlevel="serializable (4)" xactid="33411557480" currentdb="18" currentdbname="xxx" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="xxx.dbo.spGetManageServicesRequest" line="10" stmtstart="356" stmtend="4256" sqlhandle="0x030012001374fc02f91433019aad000001000000000000000000000000000000000000000000000000000000"></frame> </executionStack> </process> <process id="process1dbfa1c1c28" taskpriority="0" logused="1232" waitresource="KEY: 18:72057594046971904 (ffffffffffff)" waittime="6275" ownerId="33411563398" transactionname="user_transaction" lasttranstarted="2021-12-01T01:06:16.450" XDES="0x3d4e842c420" lockMode="RangeI-N" schedulerid="31" kpid="36432" status="suspended" spid="419" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-01T01:06:16.480" lastbatchcompleted="2021-12-01T01:06:16.463" lastattention="1900-01-01T00:00:00.463" clientapp="Core Microsoft SqlClient Data Provider" hostpid="11020" isolationlevel="serializable (4)" xactid="33411563398" currentdb="18" currentdbname="xxx" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="xxx.dbo.spCreateManageServicesRequest" line="40" stmtstart="2592" stmtend="3226" sqlhandle="0x03001200f01ab84aeb1433019aad000001000000000000000000000000000000000000000000000000000000"></frame> </executionStack> </process> </process-list> <resource-list> <keylock hobtid="72057594046775296" dbid="18" objectname="xxx.dbo.ServiceChange" indexname="PK_ServiceChange" id="lock202ecfd0380" mode="X" associatedObjectId="72057594046775296"> <owner-list> <owner id="process1dbfa1c1c28" mode="X" /> </owner-list> <waiter-list> <waiter id="process1dbfa86c4e8" mode="RangeS-S" requestType="wait" /> </waiter-list> </keylock> <keylock hobtid="72057594046971904" dbid="18" objectname="xxx.dbo.ServiceChangeParameter" indexname="PK_ServiceChangeParameter" id="lock27d3d371880" mode="RangeS-S" associatedObjectId="72057594046971904"> <owner-list> <owner id="process1dbfa86c4e8" mode="RangeS-S" /> </owner-list> <waiter-list> <waiter id="process1dbfa1c1c28" mode="RangeI-N" requestType="wait" /> </waiter-list> </keylock> </resource-list> </deadlock>
¿Por qué está ocurriendo este punto muerto? ¿Cómo lo evito en el futuro?
Editar: aquí hay un plan para obtener el procedimiento: https://www.brentozar.com/pastetheplan/?id=B1UMMhaqF
Otra edición: después del comentario de GSerg, cambié el número de línea en el gráfico de punto muerto de 65 a 40, debido a las columnas eliminadas que no son importantes para la pregunta.
Es mejor evitar el nivel de aislamiento serializable. La forma en que se proporciona la garantía serializable a menudo es propensa a puntos muertos.
Si no puede modificar sus procesos almacenados para usar sugerencias de bloqueo más específicas que garanticen los resultados que necesita en un nivel de aislamiento menor, entonces puede evitar este escenario particular de interbloqueo que se muestra asegurándose de que todos los bloqueos se eliminen primero en ServiceChange
antes de que se tomen. en ServiceChangeParameter
.
Una forma de hacerlo sería introducir una variable de tabla en spGetManageServicesRequest
y materializar los resultados de
SELECT ... FROM [dbo].[ManageServicesRequest] AS [MR] LEFT JOIN [dbo].[ServiceChange] AS [SC] ON [SC].[ReferenceTransactionId] = [MR].[ReferenceTransactionId]
a la variable de la tabla.
Luego únase a eso contra [dbo].[ServiceChangeParameter]
para obtener los resultados finales.
La separación de fases introducida por la variable de tabla garantizará que la instrucción SELECT
adquiera los bloqueos en el mismo orden de objetos que la inserción, por lo que evitará interbloqueos en los que la instrucción SELECT
ya tiene un bloqueo en ServiceChangeParameter
y está esperando adquirir uno en ServiceChange
(como en el gráfico de punto muerto aquí).
Puede ser instructivo observar los bloqueos exactos eliminados por el SELECT
que se ejecuta en el nivel de aislamiento serializable. Estos se pueden ver con eventos extendidos o con el indicador de seguimiento no documentado 1200.
Actualmente, su plan de ejecución se encuentra a continuación.
Para los siguientes datos de ejemplo
INSERT INTO [dbo].[ManageServicesRequest] VALUES (26410821, GETDATE(), 1, GETDATE()), (26410822, GETDATE(), 1, GETDATE()), (26410823, GETDATE(), 1, GETDATE()); INSERT INTO [dbo].[ServiceChange] VALUES (26410821, 'X', 'X', GETDATE()), (26410822, 'X', 'X', GETDATE()), (26410823, 'X', 'X', GETDATE()); INSERT INTO [dbo].[ServiceChangeParameter] VALUES (26410821, 'X', 'P1','P1', GETDATE()), (26410823, 'X', 'P1','P1', GETDATE());
La salida del indicador de seguimiento (para WHERE [MR].[ReferenceTransactionId] = 26410822
) es
Process 51 acquiring IS lock on OBJECT: 7:1557580587:0 (class bit2000000 ref1) result: OK Process 51 acquiring IS lock on OBJECT: 7:1509580416:0 (class bit2000000 ref1) result: OK Process 51 acquiring IS lock on OBJECT: 7:1477580302:0 (class bit2000000 ref1) result: OK Process 51 acquiring IS lock on PAGE: 7:1:600 (class bit2000000 ref0) result: OK Process 51 acquiring S lock on KEY: 7:72057594044940288 (1b148afa48fb) (class bit2000000 ref0) result: OK Process 51 acquiring IS lock on PAGE: 7:1:608 (class bit2000000 ref0) result: OK Process 51 acquiring RangeS-S lock on KEY: 7:72057594045005824 (a69d56b089b6) (class bit2000000 ref0) result: OK Process 51 acquiring IS lock on PAGE: 7:1:632 (class bit2000000 ref0) result: OK Process 51 acquiring RangeS-S lock on KEY: 7:72057594045202432 (c37d1982c3c9) (class bit2000000 ref0) result: OK Process 51 acquiring RangeS-S lock on KEY: 7:72057594045005824 (2ef5265f2b42) (class bit2000000 ref0) result: OK
El orden de los bloqueos tomados se indica en la imagen a continuación. Los bloqueos de rango se aplican al rango de valores posibles desde el valor de clave dado, hasta el valor de clave más cercano debajo de él (en orden de clave, ¡así que arriba en la imagen!).
Primero se llama al nodo 1 y toma un bloqueo S
en la fila en ManageServicesRequest
, luego se llama al nodo 2 y se toma un bloqueo RangeS-S
en una clave en ServiceChange
, los valores de esta fila se usan para realizar la búsqueda en ServiceChangeParameter
: en este caso, no hay filas coincidentes para el predicado, pero aún se extrae un bloqueo RangeS-S
que cubre el rango desde la siguiente clave más alta hasta la anterior (rango (26410821, 'X', 'P1') ... (26410823, 'X', 'P1')
en este caso).
Luego se vuelve a llamar al nodo 2 para ver si hay más filas. Incluso en el caso de que no haya un bloqueo RangeS-S
adicional, se toma en la siguiente fila en ServiceChange
.
En el caso de su gráfico de interbloqueo, parece que el rango que se bloquea en ServiceChangeParameter
es el rango hasta el infinito (indicado por ffffffffffff
); esto sucederá aquí cuando busque un valor clave en o más allá de la última clave en el índice .
Una alternativa a la variable de tabla también podría ser cambiar la consulta como se muestra a continuación.
SELECT ... FROM [dbo].[ManageServicesRequest] AS [MR] LEFT JOIN [dbo].[ServiceChange] AS [SC] ON [SC].[ReferenceTransactionId] = [MR].[ReferenceTransactionId] LEFT HASH JOIN [dbo].[ServiceChangeParameter] AS [SP] ON [SP].[ReferenceTransactionId] = [MR].[ReferenceTransactionId] AND [SP].[ServiceId] = [SC].[ServiceId] WHERE [MR].[ReferenceTransactionId] = @ReferenceTransactionId
El predicado final en [dbo].[ServiceChangeParameter] se cambia a la referencia [MR].[ReferenceTransactionId]
en lugar de [SC].[ReferenceTransactionId]
y se agrega una sugerencia de unión hash explícita.
Esto da un plan como el siguiente donde todos los bloqueos en ServiceChange
se toman durante la etapa de creación de la tabla hash antes de que se tomen en ServiceChangeParameter
, sin cambiar la condición de ReferenceTransactionId
, el nuevo plan tuvo un escaneo en lugar de una búsqueda en ServiceChangeParameter
, razón por la cual ese cambio se hizo (permite que el optimizador use el predicado de igualdad implícito en @ReferenceTransactionId)