Interbloqueo y bloqueo de modificación de esquema

Question

Tengo un punto muerto para el que necesito información

<deadlock>
<victim-list>
<victimProcess id="process4c86a61868" />
</victim-list>
<process-list>
<process id="process4c86a61868" taskpriority="0" logused="0" waitresource="METADATA: database_id = 5 SECURITY_CACHE($hash = 0x5:0x0)" waittime="2364" ownerId="15082818710" transactionname="SEC Cache Coherency" lasttranstarted="2020-09-25T13:05:02.643" XDES="0x245ed33d320" lockMode="Sch-M" schedulerid="16" kpid="8184" status="suspended" spid="172" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-09-25T13:05:02.550" lastbatchcompleted="2020-09-25T13:05:02.550" lastattention="1900-01-01T00:00:00.550" clientapp="[Shadow NDO Session 00000189]" hostname="CTXVDA-005" hostpid="15024" loginname="Domain\AOS-DK" isolationlevel="read uncommitted (1)" xactid="15082818429" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="mssqlsystemresource.sys.sp_grantdbaccess" line="80" stmtstart="4280" stmtend="4458" sqlhandle="0x0300ff7fae2d27c39e78130137a5000001000000000000000000000000000000000000000000000000000000"> select @owner_uid = principal_id from sys.database_principals where name = @name_in_db </frame> </executionStack> <inputbuf> Proc [Database Id = 32767 Object Id = -1020842578] </inputbuf> </process> <process id="processb97f92c928" taskpriority="0" logused="0" waitresource="METADATA: database_id = 5 PERMISSIONS(class = 0, major_id = 0)" waittime="2365" ownerId="15082818573" transactionname="read permissions" lasttranstarted="2020-09-25T13:05:02.640" XDES="0x178bed2d930" lockMode="Sch-S" schedulerid="20" kpid="12996" status="suspended" spid="188" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-09-25T13:05:02.637" lastbatchcompleted="2020-09-25T13:05:02.630" lastattention="2020-09-25T13:04:55.993" clientapp="Microsoft Dynamics NAV Classic client" hostname="CTXVDA-003" hostpid="56896" loginname="Domain\EGC-ES" isolationlevel="read uncommitted (1)" xactid="15069357664" currentdb="5" lockTimeout="4294967295" clientoption1="671156320" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="114" sqlhandle="0x02000000f4611a06e91ca2d53472b55965ad7e0b91ac18440000000000000000000000000000000000000000"> SELECT FROM "SOME_DATABASE_NAME"."dbo"."COUNTRY_S$Purchase Line" WITH (READUNCOMMITTED) WHERE "Document Type"=@P1 AND "Document No."=@P2 AND "Line No."=@P3 AND (("Document Type"=@P4)) AND (("Document No."=@P5)) OPTION (OPTIMIZE FOR UNKNOWN) </frame>
<frame procname="mssqlsystemresource.sys.sp_execute" line="1" stmtstart="-1" sqlhandle="0x0400ff7f47dacef5010000000000000000000000000000000000000000000000000000000000000000000000">
sp_execute </frame>
</executionStack>
<inputbuf>
(@P1 int,@P2 varchar(30),@P3 int,@P4 int,@P5 varchar(30))SELECT FROM "SOME_DATABASE_NAME"."dbo"."COUNTRY_S$Purchase Line" WITH (READUNCOMMITTED) WHERE "Document Type"=@P1 AND "Document No."=@P2 AND "Line No."=@P3 AND (("Document Type"=@P4)) AND (("Document No."=@P5)) OPTION (OPTIMIZE FOR UNKNOWN) </inputbuf> </process> <process id="process54bff4ccf8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 5 SECURITY_CACHE($hash = 0x5:0x0)" waittime="2365" ownerId="15082818576" transactionname="SEC Cache Coherency" lasttranstarted="2020-09-25T13:05:02.640" XDES="0x1606e8f6da0" lockMode="Sch-M" schedulerid="28" kpid="12548" status="suspended" spid="527" sbid="1" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-09-25T13:05:02.527" lastbatchcompleted="2020-09-25T13:05:02.527" lastattention="2020-09-25T13:00:18.717" clientapp="Microsoft SQL Server" hostname="SQL-07" hostpid="2784" loginname="LinkedBISQL" isolationlevel="read committed (2)" xactid="15082801850" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_columns_100_rowset" line="9" stmtstart="340" sqlhandle="0x0300ff7f773d64f24d80150137a5000001000000000000000000000000000000000000000000000000000000">
select
TABLE_CATALOG = s_cv.TABLE_CATALOG,
TABLE_SCHEMA = s_cv.TABLE_SCHEMA,
TABLE_NAME = s_cv.TABLE_NAME,
COLUMN_NAME = s_cv.COLUMN_NAME,
COLUMN_GUID = s_cv.COLUMN_GUID,
COLUMN_PROPID = s_cv.COLUMN_PROPID,
ORDINAL_POSITION = s_cv.ORDINAL_POSITION,
COLUMN_HASDEFAULT = s_cv.COLUMN_HASDEFAULT,
COLUMN_DEFAULT = s_cv.COLUMN_DEFAULT,
COLUMN_FLAGS = s_cv.COLUMN_FLAGS,
IS_NULLABLE = s_cv.IS_NULLABLE,
DATA_TYPE = s_cv.DATA_TYPE, -- Used by Katmai+ clients
TYPE_GUID = s_cv.TYPE_GUID,
CHARACTER_MAXIMUM_LENGTH= s_cv.CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH = s_cv.CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION = s_cv.NUMERIC_PRECISION,
NUMERIC_SCALE = s_cv.NUMERIC_SCALE,
DATETIME_PRECISION = s_cv.DA </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -228311689] </inputbuf>
</process>
</process-list>
<resource-list>
<metadatalock subresource="SECURITY_CACHE" classid="$hash = 0x5:0x0" dbid="5" id="locka1e4fe2500" mode="Sch-M"> <owner-list> <owner id="process54bff4ccf8" mode="Sch-M" requestType="wait" /> </owner-list> <waiter-list> <waiter id="process4c86a61868" mode="Sch-M" requestType="wait" /> </waiter-list> </metadatalock> <metadatalock subresource="PERMISSIONS" classid="class = 0, major_id = 0" dbid="5" id="lock708d967580" mode="Sch-M"> <owner-list> <owner id="process4c86a61868" mode="Sch-M" /> </owner-list> <waiter-list> <waiter id="processb97f92c928" mode="Sch-S" requestType="wait" /> </waiter-list> </metadatalock> <metadatalock subresource="SECURITY_CACHE" classid="$hash = 0x5:0x0" dbid="5" id="locka1e4fe2500" mode="Sch-M">
<owner-list>
<owner id="processb97f92c928" mode="Sch-M" />
</owner-list>
<waiter-list>
<waiter id="process54bff4ccf8" mode="Sch-M" requestType="wait" />
</waiter-list>
</metadatalock>
</resource-list>
</deadlock>

I am wondering why process id="processb97f92c928 would need a schema modification lock

<metadatalock subresource="SECURITY_CACHE" classid="$hash = 0x5:0x0" dbid="5" id="locka1e4fe2500" mode="Sch-M">
<owner-list>
<owner id="processb97f92c928" mode="Sch-M" />
</owner-list>
<waiter-list>
<waiter id="process54bff4ccf8" mode="Sch-M" requestType="wait" />
</waiter-list>
</metadatalock>

Es una declaración selecta, así que estoy un poco sorprendido de que reclame un candado tan amplio. ¿Las sugerencias en la declaración desencadenarían tal comportamiento? El sistema tiene la replicación configurada pero no para esta tabla. He comprobado que no tenemos ningún activador en la mesa de esta mesa.

El estancamiento es recurrente. ¿Cuál sería una forma lógica de solucionar este problema?

Answer 1

Al observar la parte inferior del evento de interbloqueo, puedo ver que los 3 procesos involucrados están esperando el bloqueo de modificación de esquema (Sch-M) que se adquiere tras la ejecución de una declaración DDL y, en tal caso, el nivel de aislamiento no confirmado de lectura que spid 188 se ejecuta debido a que la sugerencia de la tabla no se tiene en cuenta. El punto clave aquí es identificar el comando DDL . Ha mencionado que no hay desencadenantes, pero los desencadenantes están relacionados con las operaciones DML que pueden llevar a un bloqueo debido al nivel de aislamiento serializable que imponen, pero este no es el caso aquí, un comando DDL es el punto clave.