Sql-server – Deadlock in SQL SERVER 2014

sql serversql server 2014

I am experiencing sporadic deadlock behaviour when my application tries to execute any DROP TABLE statement against my database. I tried capturing the deadlock graph and this is the output from deadlock xml

<deadlock>
     <victim-list>
      <victimProcess id="process2bddc28" />
     </victim-list>
     <process-list>
      <process id="process2bddc28" taskpriority="0" logused="0" waitresource="KEY: 6:281474978938880 (85262e079cd2)" waittime="1900" ownerId="76493296" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:15.867" XDES="0x621cb38e0" lockMode="S" schedulerid="6" kpid="10748" status="suspended" spid="119" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-02-22T05:58:16.080" lastbatchcompleted="2016-02-22T05:58:16.080" lastattention="1900-01-01T00:00:00.080" hostname="DEV01" hostpid="13420" isolationlevel="read committed (2)" xactid="76493296" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
       <executionStack>
        <frame procname="mssqlsystemresource.sys.sp_columns_100" line="138" stmtstart="12020" stmtend="17532" sqlhandle="0x0300ff7f78a964e13656e600e7a5000001000000000000000000000000000000000000000000000000000000">
      </frame>
       </executionStack>
       <inputbuf>Proc [Database Id = 32767 Object Id = -513496712]</inputbuf>
      </process>
      <process id="process45c8ca8" taskpriority="0" logused="6748" waitresource="KEY: 6:562949956108288 (5db581fe7a0b)" waittime="1900" ownerId="76493663" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:16.087" XDES="0x16aeded90" lockMode="X" schedulerid="1" kpid="18268" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-02-22T05:58:16.090" lastbatchcompleted="2016-02-22T05:58:16.090" lastattention="1900-01-01T00:00:00.090" hostname="DEV01" hostpid="20128"  isolationlevel="read committed (2)" xactid="76493663" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
       <executionStack>
        <frame procname="adhoc" line="1" sqlhandle="0x01000600702bfd0bd083047f0600000000000000000000000000000000000000000000000000000000000000">
    DROP TABLE "dbo"."USER_TABLE_1"    </frame>
       </executionStack>
       <inputbuf>
    DROP TABLE "dbo"."USER_TABLE_1"   </inputbuf>
      </process>
     </process-list>
     <resource-list>
      <keylock hobtid="281474978938880" dbid="6" objectname="DEV_01.sys.sysschobjs" indexname="clst" id="lock35af27b80" mode="X" associatedObjectId="281474978938880">
       <owner-list><owner id="process45c8ca8" mode="X" /></owner-list>          
       <waiter-list><waiter id="process2bddc28" mode="S" requestType="wait" /></waiter-list>     
       </keylock>
      <keylock hobtid="562949956108288" dbid="6" objectname="DEV_01.sys.syscolpars" indexname="nc" id="lock453c34600" mode="U" associatedObjectId="562949956108288">
       <owner-list><owner id="process2bddc28" mode="S" /></owner-list>
       <waiter-list><waiter id="process45c8ca8" mode="X" requestType="convert" /></waiter-list>
      </keylock>
     </resource-list>
    </deadlock>

This behaviour was not seen in earlier versions of SQL Server. Any thoughts on this?

Best Answer

Just my 5 cents contribution to the topic (not a complete answer)

I had a similar issue and I've caught the blocking query with sp_whoisactive - so it is

create procedure sys.sp_columns_100
(
    @table_name         nvarchar(384),
    @table_owner        nvarchar(384) = null,
    @table_qualifier    sysname = null,
    @column_name        nvarchar(384) = null,
    @NameScope          int = 0,
    @ODBCVer            int = 2,
    @fUsePattern        bit = 1 -- To allow users to explicitly disable all pattern matching.
)
...

and as @Cody_Konior suggested I do have "many active ODBC connections" from legacy SSIS packages in my ETL system, so...

perhaps it's time to finally change them to OLEDB or ADO.NET versions as I wanted for some time already but posponed

Related Question