jaya1122 avatar image
jaya1122 asked

SQL Server Deadlock on subresource PERMISSIONS when sp_executesql with #temptables

We are seeing a high volume of deadlocks since we introduced Vault process that gives permissions on schema.
Here the process1 runs the following GRANT statements
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: sharedproxy TO [v-tk-ca7baef9-f8e0---xHwo6QQTzfhZKx7y7zqM-1638452722]
Process 1 holds a SCH_M lock on the resource 'SECURITY CACHE' under the transaction name 'SEC Cache Coherency'

process 2 runs a stored proc mar_dev.dbo.spDD_MarriageView_Search which has dynamic sql that uses sp_executesql to insert into a #temp table.
This has never been a problem before. Now from the deadlock graph we can see that sp_executesql holds a SCH_M lock on METADATA: database_id = 39 PERMISSIONS(class = 0, major_id = 0) under the transactionname 'read permissions'
This is interfering with SECURITY CACHE from process 1.

There is no documentation around xml nodes transaction name any where.
Could someone please throw some light on why sp_executesql is accessing PERMISSIONS for a simple insert into #temp table.
Any ideas to resolve this deadlocks?

  1. <deadlock>
  2. <victim-list>
  3. <victimProcess id="process2c98d755468" />
  4. </victim-list>
  5. <process-list>
  6. <process id="process2c98d755468" taskpriority="0" logused="0" waitresource="METADATA: database_id = 39 SECURITY_CACHE($hash = 0x27:0x0), lockPartitionId = 0" waittime="4309" ownerId="17175321444" transactionname="SEC Cache Coherency" lasttranstarted="2021-12-02T08:45:23.043" XDES="0x2cea0dd1a40" lockMode="Sch-M" schedulerid="1" kpid="10016" status="suspended" spid="101" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-02T08:45:23.040" lastbatchcompleted="2021-12-02T08:45:23.040" lastattention="1900-01-01T00:00:00.040" clientapp="mar-vault-appdb" hostname="myhost-65" hostpid="0" loginname="sp-vault-hash-d" isolationlevel="read committed (2)" xactid="17175320772" currentdb="39" currentdbname="mar_dev" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
  7. <executionStack>
  8. <frame procname="adhoc" line="1" sqlhandle="0x01002700b2bb6835407e560ed102000000000000000000000000000000000000000000000000000000000000">
  9. GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: sharedproxy TO [v-tk-ca7baef9-f8e0---xHwo6QQTzfhZKx7y7zqM-1638452722] </frame>
  10. </executionStack>
  11. <inputbuf>
  12. GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: sharedproxy TO [v-tk-ca7baef9-f8e0---xHwo6QQTzfhZKx7y7zqM-1638452722] </inputbuf>
  13. </process>
  14. <process id="process2c98d755088" taskpriority="0" logused="0" waitresource="METADATA: database_id = 39 PERMISSIONS(class = 0, major_id = 0), lockPartitionId = 0" waittime="4309" ownerId="17175321432" transactionname="read permissions" lasttranstarted="2021-12-02T08:45:23.043" XDES="0x2ca8a173a40" lockMode="Sch-S" schedulerid="1" kpid="832" status="suspended" spid="450" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2021-12-02T08:45:22.987" lastbatchcompleted="2021-12-02T08:45:22.987" lastattention="1900-01-01T00:00:00.987" clientapp="DataSvc" hostname="ase-rate-59" hostpid="5404" loginname="dm\sp-rate-d" isolationlevel="read committed (2)" xactid="17175321385" currentdb="39" currentdbname="mar_dev" lockTimeout="4294967295" clientoption1="536870944" clientoption2="128056">
  15. <executionStack>
  16. <frame procname="adhoc" line="2" stmtstart="12" stmtend="562" sqlhandle="0x020000004f75a605535205b81f671cce718aff7a9a3bd5fb0000000000000000000000000000000000000000">
  17. unknown </frame>
  18. <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
  19. sp_executesql </frame>
  20. <frame procname="mar_dev.dbo.spDD_MarriageView_Search" line="59" stmtstart="3458" stmtend="3502" sqlhandle="0x030027004f2a4e475121f000bca4000001000000000000000000000000000000000000000000000000000000">
  21. EXEC sp_executesql @sq </frame>
  22. </executionStack>
  23. <inputbuf>
  24. Proc [Database Id = 39 Object Id = 1196304975] </inputbuf>
  25. </process>
  26. </process-list>
  27. <resource-list>
  28. <metadatalock subresource="SECURITY_CACHE" classid="$hash = 0x27:0x0" dbid="39" lockPartition="0" id="lock2d0eaa62f80" mode="Sch-M">
  29. <owner-list>
  30. <owner id="process2c98d755088" mode="Sch-M" />
  31. </owner-list>
  32. <waiter-list>
  33. <waiter id="process2c98d755468" mode="Sch-M" requestType="wait" />
  34. </waiter-list>
  35. </metadatalock>
  36. <metadatalock subresource="PERMISSIONS" classid="class = 0, major_id = 0" dbid="39" lockPartition="0" id="lock2d050969700" mode="Sch-M">
  37. <owner-list>
  38. <owner id="process2c98d755468" mode="Sch-M" />
  39. </owner-list>
  40. <waiter-list>
  41. <waiter id="process2c98d755088" mode="Sch-S" requestType="wait" />
  42. </waiter-list>
  43. </metadatalock>
  44. </resource-list>
  45. </deadlock>
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.