Deadlock and schema modification lock

Question

Deadlock and schema modification lock

agepeder 271

I have a deadlock that I need some input for

<deadlock>
<victim-list>
<victimProcess id="process1" />
</victim-list>
<process-list>
<process id="process1" 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="Prod-citrix-01" hostpid="15024" loginname="DOMAIN\AOS-1" 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 @AmiChanBKK _uid = principal_id from sys.database_principals where name = @DeezNutz _in_db </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -1020842578] </inputbuf>
</process>
<process id="process2" 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="Prod-citrix-02" 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="process3" 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="PROD-server" hostpid="2784" loginname="LinkedUSER" 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="process3" mode="Sch-M" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process1" 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="process1" mode="Sch-M" />
</owner-list>
<waiter-list>
<waiter id="process2" 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="process2" mode="Sch-M" />
</owner-list>
<waiter-list>
<waiter id="process3" mode="Sch-M" requestType="wait" />
</waiter-list>
</metadatalock>
</resource-list>
</deadlock>

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

Its a select statement so Im kind of surprised that its claiming such a broad lock. Would the hints in the statement trigger such a behavior ?

JRStern 1 Reputation point

2020-10-16T22:18:55.28+00:00

Even though process c928 shows as isolation=readuncommitted (???), it does show as trancount=1 and it may be in a larger transaction that has already done something that makes it effectively a serialized transaction, though it doesn't show that way on the report.

Just a guess.

J.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2020-10-17T13:04:50.41+00:00

Yes, but note that the transaction started after the batch was submitted, so the transaction is the same batch. Furthermore, stored procedure is sp_execute, and stmt_start is 1, so this has to be the first statement in the transaction.

And look at the name of the transaction "ReadPermissions". To me that actually looks like it's trying to figure out whether the user has permission to access those tables.

2 answers

Your answer

JRStern 1 Reputation point

2020-10-16T22:18:55.28+00:00

Even though process c928 shows as isolation=readuncommitted (???), it does show as trancount=1 and it may be in a larger transaction that has already done something that makes it effectively a serialized transaction, though it doesn't show that way on the report.

Just a guess.

J.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2020-10-17T13:04:50.41+00:00

Yes, but note that the transaction started after the batch was submitted, so the transaction is the same batch. Furthermore, stored procedure is sp_execute, and stmt_start is 1, so this has to be the first statement in the transaction.

And look at the name of the transaction "ReadPermissions". To me that actually looks like it's trying to figure out whether the user has permission to access those tables.

Answer 1

So you are experiencing this deadlock repeatedly? Yes, the call to sp_grantdbaccess is likely to be an important component as it actually performs updates.

As I said, the best would be to open a support case, but keep in mind that SQL 2012 is extended support.

I heard from an MVP colleague that he had experienced a deadlock of this kind on SQL 2014 at some point. They opened a support case, which itself did not lead to something, but it has not come back, so he suspects that it somehow has been fixed.

I believe you are on SQL 2012 SP3. My own SQL 2012 instance is 11.0.7493 (SP4-GDR), so I think that no matter what you should slap on SP4. No guarantees that it will fix the issue, though.

Answer 2

Erland Sommarskog 134K MVP Volunteer Moderator

I can't give a complete answer here, but this is obviously a matter of some internals here. All processes involved in the deadlock are running system procedures.

The resource they are fighting about is a SECURITY_CACHE. Maybe they are loading something into this cache under the covers when they run this query in sp_columns_100_rowset, and because they modify it, they need a Sch-M lock on it.

agepeder 271 Reputation points

2020-10-17T09:15:22.927+00:00

Thank you for taking your time to answer Erland.
The system is has transactionel replication configured for other tables. Could that also be adding to the deadlock issue we have ?
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2020-10-17T09:19:52.753+00:00

To be honest, I don't know. My gut feeling says that it should not matter, but as this is an internals thing, it is very difficult to say for sure without deeper knowledge.
agepeder 271 Reputation points

2020-10-18T05:00:30.653+00:00

Thanks again Erland.
Do you have a suggestion to a logical way of moving forward to gain deeper knowledge ?
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2020-10-18T11:47:14+00:00

If this is something that happens repeatedly, I would recommend that you open a support case. I think this deadlock is difficult to understand without access to source code or internal documentation.

By the way, what does "SELECT @@version" report?
agepeder 271 Reputation points

2020-10-23T09:07:40.157+00:00

Hi Erland.
The current patch level is 11.0.6260. I am still wondering if this could be traced deeper.

it seems like this part is always a participant of the deadlock.

<frame procname="mssqlsystemresource.sys.sp_grantdbaccess" line="80" stmtstart="4280" stmtend="4458" sqlhandle="0x0300ff7fae2d27c39e78130137a5000001000000000000000000000000000000000000000000000000000000">
select @AmiChanBKK _uid = principal_id from sys.database_principals where name = @DeezNutz _in_db </frame>

new users is being created throug the application. could a large number of users created in the system contribute to this issue ?

select count() from master.sys.database_principals --1450
select count() from USERDATABASE.sys.database_principals --1065

Share via

Deadlock and schema modification lock

2 answers

Your answer