3

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?

  • 1
    That sp_columns_100 might be used to return table column information for ODBC drivers. I wonder if you're getting so many active ODBC connections, all requesting column information, that it's locking out any schema changes. – Cody Konior Apr 28 '16 at 0:45
  • But why doesn't this happen in Sql Server 2012? – vkr Apr 28 '16 at 0:59
  • I don't have the answer. Just adding an idea for others in case it helps them come up with the answer. – Cody Konior Apr 28 '16 at 4:26
  • 1
    When was the last time you ran checkdb? How was this database migrated to 2014? What version did it come from? The resources involved are internal tables which makes me believe something to be amiss with the physical/logical metadata OR there is something else running against this database we don't know about. – Sean Gallardy - Retired User Apr 28 '16 at 5:48
  • 2
    Have you tried using Adam Machanic's sp_whoisactive to see what's going on while you're dropping? This will tell you who's really holding onto the table you're trying to drop. Use @find_block_leaders=1 and @sort_order='[blocked_session_count]DESC' to make the output easier to visualize – Craig Efrein Apr 28 '16 at 7:55
0

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

| improve this answer | |
0

The DROP TABLE might be in a transaction trancount="2" so it might own X locks from previous statements, which makes for more deadlock risk.

The other session is running sp_columns_100 which is a metadata discovery procedure that returns column metadata potentially for many tables. Also the other session was the victim, and was also connected from DEV01 so you ought to be able to track it down.

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

Deadlocks are timing and plan-dependent. Both can change in an upgrade. Also the other session might not have been querying the catalog before the upgrade.

One option is to use a lock timeout, low deadlock priority, and a retry loop on the session dropping the table.

eg:

    use master 
    go
    create or alter procedure sp_execute_with_lock_timeout_and_retry @sql nvarchar(max), @retries int
    as
    /*
--session 1
    if @@trancount > 0 rollback
    exec sp_execute_with_lock_timeout_and_retry 
    '
      drop table bar; 
      drop table foo;
    ', 3

--session 2
if @@trancount > 0 rollback
drop table if exists foo;
drop table if exists bar;
create table foo(id int);
create table bar(id int);
go
begin transaction
select * from foo with (tablockx)
--wait here
select * from bar with (tablockx)

    */
    begin
      set lock_timeout 1000;
      set deadlock_priority low;

      while @retries >= 0
      begin
        begin try
          begin transaction;
          exec (@sql);
          commit transaction;
          return 0;
        end try
        begin catch
          if @@trancount > 0 rollback;
          if error_number() not in ( 1205, 1222 ) or @retries < 1
             throw;

          set @retries -= 1
          raiserror('sleeping after lock timeout or deadlock' , 10, 1) WITH NOWAIT
          waitfor delay '00:00:02';
        end catch
      end
    end
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.