Estoy tratando de resolver un problema de interbloqueo de SQL. A continuación se muestra un informe system_health

<deadlock>
  <victim-list>
    <victimProcess id="process87d03ccf8" />
  </victim-list>
  <process-list>
    <process id="process87d03ccf8" taskpriority="0" logused="0" waitresource="KEY: 7:72057901332627456 (f323ae9efc53)" waittime="1087" ownerId="20788909869" transactionname="SELECT" lasttranstarted="2020-12-03T23:13:56.500" XDES="0x338706d10" lockMode="S" schedulerid="6" kpid="38240" status="suspended" spid="103" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-12-03T23:13:56.490" lastbatchcompleted="2020-12-03T23:13:56.490" lastattention="1900-01-01T00:00:00.490" clientapp=".Net SqlClient Data Provider" hostname="ID45846" hostpid="58020" loginname="ubuser" isolationlevel="read committed (2)" xactid="20788909869" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="x.dbo.OrderData_GetByOrderID" line="6" stmtstart="124" sqlhandle="0x03000700cddb7412e6afdc00a0a9000001000000000000000000000000000000000000000000000000000000"></frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 7 Object Id = 309648333]   </inputbuf>
    </process>
    <process id="process32f127868" taskpriority="0" logused="112" waitresource="KEY: 7:72057901332692992 (004616e83cc3)" waittime="1087" ownerId="20788909868" transactionname="UPDATE" lasttranstarted="2020-12-03T23:13:56.500" XDES="0x81bad63a8" lockMode="X" schedulerid="15" kpid="66292" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-03T23:13:56.490" lastbatchcompleted="2020-12-03T23:13:56.490" lastattention="1900-01-01T00:00:00.490" clientapp=".Net SqlClient Data Provider" hostname="ID45846" hostpid="58020" loginname="ubuser" isolationlevel="read committed (2)" xactid="20788909868" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="x.dbo.OrderData_Set" line="36" stmtstart="1662" sqlhandle="0x030007002608c15b50af010156ac000001000000000000000000000000000000000000000000000000000000"></frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 7 Object Id = 1539377190]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057901332627456" dbid="7" objectname="unidbmaster.dbo.OrderData" indexname="PK_OrderData" id="lock494546200" mode="X" associatedObjectId="72057901332627456">
      <owner-list>
        <owner id="process32f127868" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process87d03ccf8" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057901332692992" dbid="7" objectname="unidbmaster.dbo.OrderData" indexname="IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue" id="lock73ecc1b80" mode="S" associatedObjectId="72057901332692992">
      <owner-list>
        <owner id="process87d03ccf8" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process32f127868" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Basado en esto es lo siguiente correcto:

  • ¿El proceso de actualización tiene un bloqueo en PK_OrderData y el proceso de selección lo quiere?
  • ¿El proceso de selección tiene un bloqueo en IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue y el proceso de actualización lo quiere?
  • El proceso de selección selecciona la misma fila que el proceso de actualización desea actualizar

Supongo que una forma de resolver este problema sería eliminar el índice IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue, sin embargo, se usa en otros lugares.

Entonces, mi pregunta es, ¿qué opciones existen para solucionar este problema? Sé que podría agregar una directiva a la declaración Select para decir 'los datos sucios están bien', sin embargo, se siente mal ...

0
markpirvine 4 dic. 2020 a las 12:04

2 respuestas

La mejor respuesta

El punto muerto entre la selección y la actualización se produce porque la selección utiliza primero el índice que encuentra los registros más rápido, luego utiliza la tabla base (índice agrupado) para recuperar el resto de la información, ya que está seleccionando todas las columnas.

La actualización, por otro lado, está actualizando la tabla base (índice agrupado) primero, lo que luego hace que todos los demás índices que hacen referencia a las columnas que se actualizan se actualicen después de eso.

Como puede ver, el orden de acceso de los índices se invierte, y debido a que ambas declaraciones ocurren exactamente al mismo tiempo que se bloquean.

select * from table where fk = @Id

Una posible solución es otro índice solo en la columna de clave externa. Suponiendo que esta columna no es parte de la actualización, no se verá afectada por la actualización.

Otra posible solución es restringir las columnas que está seleccionando a las que necesita (select * casi siempre es una mala idea) y crear un índice de cobertura de todas las columnas que está seleccionando, así como la clave externa. De esta manera, la selección solo llegará a un índice.

1
Dale K 4 dic. 2020 a las 11:09

En términos generales, los puntos muertos como este se producen cuando dos transacciones tienen bloqueos en las tablas y ninguna puede continuar porque cada transacción bloquea las tablas que necesita la otra.

Supongo que SELECT es en realidad parte de la transacción (en lugar de solo una consulta independiente). Si fuera solo una consulta independiente y la transacción UPDATE hubiera comenzado, entonces la consulta SELECT habría esperado hasta que la UPDATE hubiera terminado. Sin embargo, algo en la misma transacción que SELECT está bloqueando algo que UPDATE luego necesita.

Sin ver las consultas, sus amplias opciones son

  • Pregúntese si las transacciones son necesarias. ¿Se pueden sacar algunos o todos los extractos de las transacciones?
  • Para ambas transacciones, ACTUALICE las tablas en el mismo orden (por ejemplo, ambas transacciones actualizan la tabla A primero, luego la tabla B en segundo lugar, en lugar de que una haga A, luego B y la otra haga B y luego A).
    • ... y si es posible, actualice cada tabla solo una vez

Brent Ozar tiene un excelente video sobre interbloqueos. Lo recomiendo encarecidamente, aunque casi una hora, es una gran demostración y cómo solucionarlo. De hecho, mis respuestas aquí se basan básicamente en su video.

0
seanb 4 dic. 2020 a las 09:55