Vault: How to minimize deadlocks in MSSQL when using GUNICORN and dynamic login credentials

[gwsampso]

I'm getting random deadlock errors in my application on sometimes the simplest of stored procedures.

The application environment where I'm experiencing the error uses the GUNICORN server in production connecting to SQL Server 2012 via pyodbc and ODBCDriver 17.

The application environment utilizes HashiCrop Vault, which generates database credentials dynamically and then alters the users based on configured roles for the MSSQL database, the logins expire around 10 mins.

I have found that running command ALTER ROLE [TableSelector] ADD MEMBER ... is the root cause. Sch-M is a lock mode of Schema Modification

Is there any ways to avoid deadlocks when working asynchronously?

XML Report

<deadlock>
 <victim-list>
  <victimProcess id="process8741e1c38" />
 </victim-list>
 <process-list>
  <process id="process8741e1c38" taskpriority="0" logused="0" waitresource="METADATA: database_id = 1 PERMISSIONS(class = 100, major_id = 0)" waittime="4651" ownerId="1576984016" transactionname="Load Permission Object Cache" lasttranstarted="2019-10-14T16:41:33.470" XDES="0x61bea9810" lockMode="Sch-S" schedulerid="2" kpid="33684" status="suspended" spid="74" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-10-14T16:41:33.473" lastbatchcompleted="2019-10-14T16:41:33.460" lastattention="1900-01-01T00:00:00.460" clientapp="python3.7" hostname="3702124fa30a" hostpid="245" loginname="v-approle-champ_readaccess_PRO-giVLBtHPPhdM4u8GHJDw-1571031688" isolationlevel="read committed (2)" xactid="0" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="CHAMP.dbo.SP_dashboard_load_beta_list" line="1" stmtstart="-1" sqlhandle="0x030013004fa3d411b251170178aa000001000000000000000000000000000000000000000000000000000000">
CREATE PROCEDURE [dbo].[SP_dashboard_load_beta_list]

AS

BEGIN

SET NOCOUNT ON;

    SELECT id, short_name, long_name 
    FROM CHAMP.dbo.champ_dw_index_master 
    WHERE is_market_beta='Y' 
    ORDER BY short_name

END    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x01001300ced65b00f0d9ce750200000000000000000000000000000000000000000000000000000000000000">
EXEC CHAMP.[dbo].[SP_dashboard_load_beta_list]    </frame>
   </executionStack>
   <inputbuf>
EXEC CHAMP.[dbo].[SP_dashboard_load_beta_list]   </inputbuf>
  </process>
  <process id="process87f025c38" taskpriority="0" logused="1260" waitresource="METADATA: database_id = 19 DATABASE_PRINCIPAL(principal_id = 6)" waittime="4625" ownerId="1576983991" transactionname="user_transaction" lasttranstarted="2019-10-14T16:41:33.437" XDES="0x39e1cf818" lockMode="Sch-M" schedulerid="3" kpid="37524" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-10-14T16:41:33.500" lastbatchcompleted="2019-10-14T16:41:33.487" lastattention="2019-10-07T15:25:11.327" clientapp="go-mssqldb" hostname="647f96b1d42e" hostpid="0" loginname="gwsampso" isolationlevel="read committed (2)" xactid="1576983991" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" sqlhandle="0x01001300eb293f1990ff88480500000000000000000000000000000000000000000000000000000000000000">
ALTER ROLE [TableSelector] ADD MEMBER [v-approle-champ_readaccess_PRO-1RrrKqWYm9STdbN95l36-1571031692]    </frame>
   </executionStack>
   <inputbuf>
ALTER ROLE [TableSelector] ADD MEMBER [v-approle-champ_readaccess_PRO-1RrrKqWYm9STdbN95l36-1571031692]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <metadatalock subresource="PERMISSIONS" classid="class = 100, major_id = 0" dbid="1" id="lock4a21b4e00" mode="Sch-M">
   <owner-list>
    <owner id="process87f025c38" mode="Sch-M" />
   </owner-list>
   <waiter-list>
    <waiter id="process8741e1c38" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </metadatalock>
  <metadatalock subresource="DATABASE_PRINCIPAL" classid="principal_id = 6" dbid="19" id="lock4a7c17f00" mode="Sch-S">
   <owner-list>
    <owner id="process8741e1c38" mode="Sch-S" />
   </owner-list>
   <waiter-list>
    <waiter id="process87f025c38" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </metadatalock>
 </resource-list>
</deadlock>

GRAPH

[george-paxos]

+1 any updates on this?

[george-paxos]

FWIW we found a slight workaround by avoiding ALTER ROLE .. ADD MEMBER in our creation statements, and instead writing out the expected GRANT statements.

We changed ours from:

CREATE LOGIN [{{name}}] with password = '{{password}}';
USE db_main;
CREATE USER [{{name}}] WITH default_schema="dbo";
ALTER ROLE db_datareader ADD MEMBER [{{name}}];
ALTER ROLE db_datawriter ADD MEMBER [{{name}}];
GRANT EXECUTE TO [{{name}}];

To:

CREATE LOGIN [{{name}}] with password = '{{password}}';
USE db_main;
CREATE USER [{{name}}] FOR LOGIN [{{name}}];
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::dbo TO [{{name}}];

Additionally, and maybe tangentially, we had issues with our DROP USER revocation statement, despite DROP LOGIN working, due to some permission issue with MSSQL in AWS RDS - our workaround was to capture the the set difference between sys.database_principals and sys.sql_logins with this query:

select top 500 CONCAT('DROP USER [', O.name, '];WAITFOR DELAY ''00:00:01'';') from 
(
    select name from sys.database_principals L
    where L.Name LIKE 'v-%'
    Except
    select R.name from sys.sql_logins R
) O

This produced a set of DROP statements for all "orphaned" users - that is USER objects without a corresponding LOGIN object.

I believe the pair of these fixes, updating creation statement, and dropping orphaned users is what solved our locking issue.