Deadlocks on Drop procedure statements

Question

I have been working on improving the installation speed of our database (with thousands of objects) by multi threading the creation of the objects. This has caused the unfortunate behavior of causing deadlocks on the DROP PROCEDURE statements.

Single threaded the deployment was taking a very long time (Since we are talking about a lot of database objects. Turning around the schema is not something to do lightly as the schema is installed at a few hundred clients.). The slow deployment is holding back our development/release cycle.

The scripts contain the following code

IF OBJECT_ID(N'myProc') IS NOT NULL
  BEGIN
    DROP PROCEDURE myProc
  END
GO
CREATE PROC....

And the second script contains

IF OBJECT_ID(N'myProc2') IS NOT NULL
  BEGIN
    DROP PROCEDURE myProc2
  END
GO
CREATE PROC....

These procedures are completely unrelated. No dependencies what so every.

The deadlock graph can be seen below:

<deadlock-list>
 <deadlock victim="process6c3dc8">
  <process-list>
   <process id="process6c3dc8" taskpriority="0" logused="884" waitresource="OBJECT: 25:1949249999:0 " waittime="3834" ownerId="3008593" transactionname="DROPOBJ" lasttranstarted="2011-04-28T16:34:31.503" XDES="0xa882b950" lockMode="Sch-S" schedulerid="3" kpid="2588" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-28T16:34:31.503" lastbatchcompleted="2011-04-28T16:34:31.503" clientapp=".Net SqlClient Data Provider" hostname="myPc" hostpid="7296" loginname="myLogin" isolationlevel="read committed (2)" xactid="3008593" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="4" stmtstart="264" stmtend="352" sqlhandle="0x0200000092ebe0126e0f90268e2a5bf1eaba70a098515cd9">
DROP PROCEDURE myProc2     </frame>
    </executionStack>
    <inputbuf>
IF object_id(N&apos;myProc2&apos;) is not null
BEGIN
  PRINT N&apos;Dropping procedure myProc2 ...&apos;
  DROP PROCEDURE myProc2
END    </inputbuf>
   </process>
   <process id="processaa4242c8" taskpriority="0" logused="5800" waitresource="OBJECT: 25:1965250056:0 " waittime="3834" ownerId="3008596" transactionname="DROPOBJ" lasttranstarted="2011-04-28T16:34:31.503" XDES="0xab493950" lockMode="Sch-S" schedulerid="2" kpid="5768" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-04-28T16:34:31.503" lastbatchcompleted="2011-04-28T16:34:31.503" clientapp=".Net SqlClient Data Provider" hostname="myPC" hostpid="8296" loginname="myLogin" isolationlevel="read committed (2)" xactid="3008596" currentdb="25" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="4" stmtstart="276" stmtend="370" sqlhandle="0x02000000f019293427b8052cc3d5d18be886f958c4b750a1">
DROP PROCEDURE myProc     </frame>
    </executionStack>
    <inputbuf>
IF object_id(N&apos;myProc&apos;) is not null
BEGIN
  PRINT N&apos;Dropping procedure myProc ...&apos;
  DROP PROCEDURE myProc
END    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1949249999" subresource="FULL" dbid="25" objectname="1949249999" id="lock87308e00" mode="Sch-M" associatedObjectId="1949249999">
    <owner-list>
     <owner id="processaa4242c8" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6c3dc8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1965250056" subresource="FULL" dbid="25" objectname="myDatabase.dbo.myProc2" id="lock878d9e80" mode="Sch-M" associatedObjectId="1965250056">
    <owner-list>
     <owner id="process6c3dc8" mode="Sch-M"/>
    </owner-list>
    <waiter-list>
     <waiter id="processaa4242c8" mode="Sch-S" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Answer 1

From examining the locks taken out by different commands that DROP, CREATE, or ALTER procedures, I believe you could resolve your particular issue by changing the pattern you use to:

IF OBJECT_ID(N'myProc') IS NULL
    EXEC sp_executesql N'Create Proc myProc as RETURN 0'
GO
ALTER PROC myProc 
AS ...

When I look at the locks taken out by a DROP PROC, I see that:

• Sch-M locks are taken out on resource_type="METADATA.AUDIT_ACTIONS" and resource_type="METADATA.PERMISSIONS"
• Sch-S lock is taken out on a table the sproc refers to
• all other locks are X or IX on system objects

When I look at the locks taken a CREATE PROC, I see that:

• An Sch-M lock is taken out on the procedure itself
• An Sch-S lock is taken out briefly on a table the sproc refers to (and released <-- corrected)
• all other locks are X or IX on system objects

When I look at the locks taken by an ALTER PROC, I see that:

• An Sch-M lock is taken out on the procedure itself
• An Sch-S lock is taken out on a table the previously compiled version of the sproc referred to (and if only in the new version will be taken out briefly)
• all other locks are X or IX on system objects

So I believe your current deadlocks have to do with access to the MetaData resources, and that could be alleviated by switching to the ALTER pattern.

However, Sch-M and Sch-S locks will still be in play, just in a different way-- so different deadlocks could still be possible if you do have other sprocs which refer to each other.

Additional comment: I would be curious to know why object creation is taking so long in general. Aside from the deadlocks-- is it actually creating stored procedures that's taking the time? My guess would be that the problem has to do with table creation and population, and I'd want to make sure you have Instant File Initialization configured, data file growth settings configured properly, recovery model and/or log backups set up, and aren't just waiting for files to grow and zero out when your scripts are running.

Answer 2

Preventing deadlocks is a topic to fill a bookshelf, but as a starting point: create a multi-dictionary which relates objects to their dependencies. If you're using C# for your deployment app, it might start something like this:

var  dependencies = new Dictionary<string, HashSet<string>>();  // I recommend that you write a MultiDictionary class to cover situations like this, I've found it very useful

book OKToCreateSproc(string sprocName)
  {
  foreach (string dependency in dependencies[sprocName])
    if (createdObjects.Contains(dependency) == false)
      return false;
  return true;
  }

Note that you'd need a thread-safe collection, and I don't believe the vanilla generic Dictionary is safe. It looks like this has been well addressed here: What's the best way of implementing a thread-safe Dictionary?.

If you're feeling clever, you can programmatically populate dependencies by scraping your DDL scripts, but that's probably overkill unless you have a very complex database.

Oh yeah, you could also just catch the deadlocks, push the problem sproc to the end of the queue, and try it again later. Crude, but effective!

Answer 3

As sysobjects table is used to store the stored procedures (no pun intended), it seems the access to that table is pretty bad. I would suggest you to create the database structure on one thread and then carefully populate it with data on multiple threads.