1

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 does utilize HashiCrop Vault, this plugin generates database credentials dynamically based on configured roles for the MSSQL database, the logins expire around 10 mins.

An example of an error is below

pandas.io.sql.DatabaseError: Execution failed on sql 'EXEC CHAMP.[dbo].[SP_dashboard_load_beta_list]': ('40001', '[40001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)')

This is the stored procedure

CHAMP.[dbo].[SP_dashboard_load_beta_list]

ALTER 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

Is there any thing obvious as to how such a simple stored procedure could throw an error?

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

enter image description here

Improve this question
2
  • I believe the running command ALTER ROLE [TableSelector] ADD MEMBER ... is the root cause. Sch-M is a lock mode of Schema Modification.
    – Dat Nguyen
    Oct 15, 2019 at 1:51
  • 1
    Great thanks for your comment @DatNguyen. So the dynamic creation of user credentials is potentially locking my schema?
    – Glenn Sampson
    Oct 15, 2019 at 4:04

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.