Deadlock e blocco della modifica dello schema

Question

Ho un deadlock per il quale ho bisogno di input

<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>

È un'affermazione selezionata, quindi sono un po 'sorpreso che rivendichi un blocco così ampio. I suggerimenti nella dichiarazione innescherebbero un simile comportamento? Il sistema ha la replica configurata ma non per questa tabella. Ho verificato che non abbiamo trigger sulla tabella di questa tabella

La situazione di stallo è ricorrente. Quale sarebbe un modo logico per risolvere questo problema?

Answer 1

Osservando la parte inferiore dell'evento deadlock, posso vedere che tutti e 3 i processi coinvolti sono in attesa del blocco di modifica dello schema (Sch-M) che viene acquisito all'esecuzione di un'istruzione DDL, e in tal caso il livello di isolamento di lettura senza commit che spid 188 viene eseguito perché il suggerimento di tabella viene ignorato. Il punto chiave qui è identificare il comando DDL . Hai detto che non ci sono trigger, ma i trigger sono correlati alle operazioni DML che possono portare al blocco a causa del livello di isolamento serializzabile che impongono, ma questo non è il caso qui, un comando DDL è il punto chiave.