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.

[tallpsmith]

I can provide some others some other options to consider. We found the problem stems from Vault initiating & managing the transaction boundaries.

In our configuration, we had Vault calling a custom Stored Procedure to manage the dynamic credentials. This Stored Procedure was already managing it's own transaction, and so Vault's initiated transaction effectively makes this process a nested transaction.

We modified our Stored procedure to commit the existing (but empty) transaction that Vault has started if it exists, then start a new transaction within the stored proceduce, commit the changes, and then re-open an (empty) transaction so that Vault's commit is effectively a no-op

This seems to have drastically cut down the total lock time and now, thankfully, is performing very well under heavy load.

One option for the Vault team to consider is providing to the Vault DB Plugin a configuration to disable the Transaction if the called SQL is managing that itself.