Blocage et verrouillage de modification de schéma

Question

J'ai une impasse pour laquelle j'ai besoin d'informations

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

C'est une déclaration sélective, donc je suis un peu surpris qu'il revendique un verrou aussi large. Les indices dans la déclaration déclencheraient-ils un tel comportement? Le système a la réplication configurée mais pas pour cette table. J'ai vérifié que nous n'avons aucun déclencheur sur la table de cette table

Le blocage est récurrent. Quelle serait une manière logique de résoudre ce problème?

Answer 1

En regardant tout en bas de l'événement de blocage, je peux voir que les 3 processus impliqués attendent le verrou de modification de schéma (Sch-M) qui est acquis lors de l'exécution d'une instruction DDL, et dans un tel cas, le niveau d'isolation de lecture non validée qui spid 188 est exécuté car l'indication de table n'est pas prise en compte. Le point clé ici est d' identifier la commande DDL . Vous avez mentionné qu'il n'y a pas de déclencheurs mais que les déclencheurs sont liés aux opérations DML qui peuvent conduire à des blocages en raison du niveau d'isolement Serializable qu'ils imposent mais ce n'est pas le cas ici, une commande DDL est le point clé.