title: Schema level deadlock on SQL Server between SELECTS (or other queries) and ALTER ROLE ... ADD MEMBER when creating credentials
body: <p><strong>Context</strong></p>
  <p>We have a few apps reading and writing to SQL Server. These apps get their credentials at startup from a central Hashicorp vault which creates the credentials at app startup time.</p>
  <p><strong>Problem</strong></p>
  <p>Often (3-5 times a week) we get deadlocks and always the culprit is the same - some innocuous app is running a query (even a simple, single table select statement) and the ALTER ROLE statement is being ran by Hashicorp to add some new credentials for another app to the dbwriter/reader roles.</p>
  <p>The role creation SQL looks like this:</p>
  <pre><code>USE MASTER;
  
  CREATE LOGIN [{{name}}] WITH PASSWORD = N'{{password}}';
  
  USE SomeDb;
  
  CREATE USER [{{name}}] FOR LOGIN [{{name}}];
  
  EXEC sp_addrolemember db_datareader, [{{name}}];
  EXEC sp_addrolemember db_datawriter, [{{name}}];
  </code></pre>
  <p>The problem appears to occur in the <code>ALTER ROLE</code> call made by <code>sp_addrolemember</code>.</p>
  <p>It appears the <code>ALTER ROLE</code> statement acquires a <code>Sch-m</code> (Schema Modification) lock on <code>PERMISSIONS</code> and then <code>DATABASE_PRINCIPAL</code>. Any query (e.g. a <code>SELECT</code>) needs to acquire Schema Stability (<code>Sch-S</code>) lock on these, which can cause a deadlock (e.g. query locks <code>DATABASE_PRINCIPAL</code> first, <code>ALTER</code> locks <code>PERMISSIONS</code> first).</p>
  <p>What I've investigated:</p>
  <ul>
  <li><p>I very frustratingly cannot recreate this against a dev DBMS - I have tried running a python script to create credentials constantly while querying for data.</p>
  </li>
  <li><p>I couldn't find any documentation on how to acquire these locks upfront (e.g. if the user creation code acquired both locks together/waited for them both to be free)</p>
  </li>
  </ul>
  <p>So I would love any ideas about this (or why it may not be recreatable!).</p>
  <p>One solution that has been raised is switching to GRANT instead of using roles, apparently this may not take schema modification locks. However, I am unsure if this is truly the case and I don't want to put these changes into production without some assurance that they will improve the situation.</p>
  <p>Here's the data from ignite viewer:</p>
  <p>The survivor query:</p>
  <pre><code>/* InputBuf */
  EXEC sp_addrolemember db_datareader, [v-rcon_approle-svc_****_rw-WOqDPce4L742J1mpoMfM-1639090813]
  
  /* Frame 1  procname=adhoc, line=1 */
  alter role [db_datareader] add member [v-rcon_approle-svc_****_rw-WOqDPce4L742J1mpoMfM-1639090813]
  
  /* Frame 2  procname=mssqlsystemresource.sys.sp_addrolemember, line=47 */
  exec (@stmtR
  
  /* Frame 3  procname=adhoc, line=1 */
  EXEC sp_addrolemember db_datareader, [v-rcon_approle-svc_****_rw-WOqDPce4L742J1mpoMfM-1639090813
  </code></pre>
  <p>The victim query</p>
  <pre><code>/* Frame 1  procname=adhoc, line=1 */
  SELECT **** ...`
  </code></pre>
  <p>Here's the xdl file:</p>
  <pre><code>&lt;deadlock&gt;&lt;victim-list&gt;&lt;victimProcess id=&quot;process16929ec3088&quot;/&gt;&lt;/victim-list&gt;&lt;process-list&gt;&lt;process id=&quot;process16929ec3088&quot; taskpriority=&quot;0&quot; logused=&quot;0&quot; waitresource=&quot;METADATA: database_id = 1 PERMISSIONS(class = 100, major_id = 0), lockPartitionId = 11&quot; waittime=&quot;2640&quot; ownerId=&quot;5731154681&quot; transactionname=&quot;Load Permission Object Cache&quot; lasttranstarted=&quot;2021-12-10T10:00:13.853&quot; XDES=&quot;0x1748f223be8&quot; lockMode=&quot;Sch-S&quot; schedulerid=&quot;12&quot; kpid=&quot;9904&quot; status=&quot;suspended&quot; spid=&quot;122&quot; sbid=&quot;0&quot; ecid=&quot;0&quot; priority=&quot;0&quot; trancount=&quot;0&quot; lastbatchstarted=&quot;2021-12-10T10:00:13.853&quot; lastbatchcompleted=&quot;2021-12-10T09:51:34.830&quot; lastattention=&quot;1900-01-01T00:00:00.830&quot; hostname=&quot;***&quot; hostpid=&quot;15179&quot; loginname=&quot;v-rcon_approle-svc_mars_rts_ro-72LuqPkS958rLBVFBUM8-1639083781&quot; isolationlevel=&quot;read committed (2)&quot; xactid=&quot;5731154673&quot; currentdb=&quot;31&quot; currentdbname=&quot;*****&quot; lockTimeout=&quot;4294967295&quot;&gt;&lt;executionStack&gt;&lt;frame procname=&quot;adhoc&quot; line=&quot;1&quot; stmtstart=&quot;-1&quot; sqlhandle=&quot;0x01001f00804d1e15607b4e1e7f01000000000000000000000000000000000000000000000000000000000000&quot;&gt;
  SELECT ***  &lt;/frame&gt;&lt;/executionStack&gt;&lt;inputbuf&gt;
  SELECT ***   &lt;/inputbuf&gt;&lt;/process&gt;&lt;process id=&quot;process196eff9f468&quot; taskpriority=&quot;0&quot; logused=&quot;0&quot; waitresource=&quot;METADATA: database_id = 1 PERMISSIONS(class = 100, major_id = 0), lockPartitionId = 11&quot; waittime=&quot;3047&quot; ownerId=&quot;5731154594&quot; transactionname=&quot;Load Permission Object Cache&quot; lasttranstarted=&quot;2021-12-10T10:00:13.450&quot; XDES=&quot;0x174e1e9fbe8&quot; lockMode=&quot;Sch-S&quot; schedulerid=&quot;12&quot; kpid=&quot;14048&quot; status=&quot;suspended&quot; spid=&quot;118&quot; sbid=&quot;0&quot; ecid=&quot;0&quot; priority=&quot;0&quot; trancount=&quot;0&quot; lastbatchstarted=&quot;2021-12-10T09:55:58.547&quot; lastbatchcompleted=&quot;2021-12-10T09:55:58.547&quot; lastattention=&quot;1900-01-01T00:00:00.547&quot; clientapp=&quot;.Net SqlClient Data Provider&quot; hostname=&quot;***&quot; hostpid=&quot;4904&quot; loginname=&quot;****\****&quot; isolationlevel=&quot;read committed (2)&quot; xactid=&quot;0&quot; currentdb=&quot;1&quot; currentdbname=&quot;master&quot; lockTimeout=&quot;4294967295&quot; clientoption1=&quot;671088672&quot; clientoption2=&quot;128056&quot;&gt;&lt;executionStack/&gt;&lt;inputbuf&gt;
  ****   &lt;/inputbuf&gt;&lt;/process&gt;&lt;process id=&quot;process18934ab7848&quot; taskpriority=&quot;0&quot; logused=&quot;1392&quot; waitresource=&quot;METADATA: database_id = 31 DATABASE_PRINCIPAL(principal_id = 16390), lockPartitionId = 11&quot; waittime=&quot;404&quot; ownerId=&quot;5731153668&quot; transactionname=&quot;user_transaction&quot; lasttranstarted=&quot;2021-12-10T10:00:13.310&quot; XDES=&quot;0x181e7460040&quot; lockMode=&quot;Sch-M&quot; schedulerid=&quot;5&quot; kpid=&quot;17184&quot; status=&quot;suspended&quot; spid=&quot;135&quot; sbid=&quot;0&quot; ecid=&quot;0&quot; priority=&quot;0&quot; trancount=&quot;3&quot; lastbatchstarted=&quot;2021-12-10T10:00:14.053&quot; lastbatchcompleted=&quot;2021-12-10T10:00:14.053&quot; lastattention=&quot;1900-01-01T00:00:00.053&quot; clientapp=&quot;vault&quot; hostname=&quot;****&quot; hostpid=&quot;0&quot; loginname=&quot;****\_HCVault_SQL_****&quot; isolationlevel=&quot;read committed (2)&quot; xactid=&quot;5731153668&quot; currentdb=&quot;31&quot; currentdbname=&quot;*****&quot; lockTimeout=&quot;4294967295&quot; clientoption1=&quot;673185824&quot; clientoption2=&quot;128056&quot;&gt;&lt;executionStack&gt;&lt;frame procname=&quot;adhoc&quot; line=&quot;1&quot; sqlhandle=&quot;0x01001f004dd61113a069b4a77501000000000000000000000000000000000000000000000000000000000000&quot;&gt;
  alter role [db_datareader] add member [*****]    &lt;/frame&gt;&lt;frame procname=&quot;mssqlsystemresource.sys.sp_addrolemember&quot; line=&quot;47&quot; stmtstart=&quot;2544&quot; stmtend=&quot;2568&quot; sqlhandle=&quot;0x0300ff7f9a42b4dd67361d01acad000001000000000000000000000000000000000000000000000000000000&quot;&gt;
  exec (@stmtR    &lt;/frame&gt;&lt;frame procname=&quot;adhoc&quot; line=&quot;1&quot; stmtend=&quot;200&quot; sqlhandle=&quot;0x01001f006d2168174069b4a77501000000000000000000000000000000000000000000000000000000000000&quot;&gt;
  EXEC sp_addrolemember db_datareader, [****    &lt;/frame&gt;&lt;/executionStack&gt;&lt;inputbuf&gt;
  EXEC sp_addrolemember db_datareader, [****]   &lt;/inputbuf&gt;&lt;/process&gt;&lt;/process-list&gt;&lt;resource-list&gt;&lt;metadatalock subresource=&quot;PERMISSIONS&quot; classid=&quot;class = 100, major_id = 0&quot; dbid=&quot;1&quot; lockPartition=&quot;11&quot; id=&quot;lock184944dc100&quot; mode=&quot;Sch-M&quot;&gt;&lt;owner-list&gt;&lt;owner id=&quot;process196eff9f468&quot; mode=&quot;Sch-S&quot; requestType=&quot;wait&quot;/&gt;&lt;/owner-list&gt;&lt;waiter-list&gt;&lt;waiter id=&quot;process16929ec3088&quot; mode=&quot;Sch-S&quot; requestType=&quot;wait&quot;/&gt;&lt;/waiter-list&gt;&lt;/metadatalock&gt;&lt;metadatalock subresource=&quot;PERMISSIONS&quot; classid=&quot;class = 100, major_id = 0&quot; dbid=&quot;1&quot; lockPartition=&quot;11&quot; id=&quot;lock184944dc100&quot; mode=&quot;Sch-M&quot;&gt;&lt;owner-list&gt;&lt;owner id=&quot;process18934ab7848&quot; mode=&quot;Sch-M&quot;/&gt;&lt;/owner-list&gt;&lt;waiter-list&gt;&lt;waiter id=&quot;process196eff9f468&quot; mode=&quot;Sch-S&quot; requestType=&quot;wait&quot;/&gt;&lt;/waiter-list&gt;&lt;/metadatalock&gt;&lt;metadatalock subresource=&quot;DATABASE_PRINCIPAL&quot; classid=&quot;principal_id = 16390&quot; dbid=&quot;31&quot; lockPartition=&quot;11&quot; id=&quot;lock1760380e580&quot; mode=&quot;Sch-S&quot;&gt;&lt;owner-list&gt;&lt;owner id=&quot;process16929ec3088&quot; mode=&quot;Sch-S&quot;/&gt;&lt;/owner-list&gt;&lt;waiter-list&gt;&lt;waiter id=&quot;process18934ab7848&quot; mode=&quot;Sch-M&quot; requestType=&quot;wait&quot;/&gt;&lt;/waiter-list&gt;&lt;/metadatalock&gt;&lt;/resource-list&gt;&lt;/deadlock&gt;
  </code></pre>