The Sql Server 2008 R2 instance under consideration is really a heavy load OLTP production server. The deadlock problem emerged a couple of days back and it is still conflicting. We received the Xml deadlock are convinced that listed the saved methods active in the deadlock plus some other particulars. I'll attempt to list lower the details out of this xml first:

Two saved methods take part in the deadlock, say SP1 and SP2. Based on the report SP1 was running in Isolation level "Serializable" and SP2 was running in "ReadCommitted".

We've looked into the next:

  • Shall we be setting IsolationLevel of SP1 to "Serializable" inside SP or perhaps in Code? - No.

  • Is every other SP whose IsolationLevel is "Serializable" calling SP1? - No.

  • Would be the table utilized by SP1 known as by every other SP which has Isolation Level as "Serializable"? - Yes. You will find SPs which have Isolation Level set to "Serializable" and access exactly the same tables as SP1, but we do not know whether or not they were running at that time of deadlock or otherwise because the deadlock
    report only demonstrated SP1 and SP2.

Lines of thought:
We've considered the next possible causes:

  • Deadlock is happening because SP1 is running as "Serializable". - Exactly why is this SP running in Serializable when I've not place it? May be the Isolation level increasing (like locks do)? If we figure this out making it run as ReadCommitted, will the problem be resolved?

  • Every other SP is running, securing the table utilized by SP1 and results in a deadlock between SP1 and SP2. - Wouldn't this SP be indexed by the deadlock report? Can the deadlock report miss this type of dependency? If so only then do we might simply be getting partial information. This still does not resolve how SP1 is running in Serializable, though.

Suggestions:

  • If this post is not sufficient in solving the issue, how do i obtain more details from SQL Server for my purpose and what information must i attempt to collect?

  • Every other Type of Believed that you'd pursue in fixing this problem?

Update:
This is actually the trace log information for that deadlock. I have transformed what they are called of SPs etc. but have checked and verified the changes don't lose out any relevant information. Look into the notes succeeding the code for more information on tables etc.

?<EVENT_INSTANCE>
  <EventType>DEADLOCK_GRAPH</EventType>
  <PostTime>2010-09-07T11:27:47.870</PostTime>
  <SPID>16</SPID>
  <TextData>
    <deadlock-list>
      <deadlock victim="process5827708">
        <process-list>
          <process id="process5827708" taskpriority="0" logused="0" waitresource="KEY: 7:72057594228441088 (8d008a861f4f)"
                   waittime="5190" ownerId="1661518243" transactionname="SELECT" lasttranstarted="2010-09-07T11:27:42.657"
                   XDES="0x80bf3b50" lockMode="RangeS-S" schedulerid="4" kpid="2228" status="suspended" spid="76" sbid="0"
                   ecid="0" priority="0" trancount="0" lastbatchstarted="2010-09-07T11:27:42.657"
                   lastbatchcompleted="2010-09-07T11:27:42.657" clientapp=".Net SqlClient Data Provider"
                   hostname="xxx" hostpid="5988" loginname="xxx" isolationlevel="serializable (4)"
                   xactid="1661518243" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="SP1" line="12" stmtstart="450" stmtend="6536"
                     sqlhandle="0x0300070090cbdc7742720c00e99d00000100000000000000">
                Select ... from Table1, Table2, Table4, Table5
              </frame>
            </executionStack>
            <inputbuf>
              Proc [Database Id = 7 Object Id = 2010958736]
            </inputbuf>
          </process>
          <process id="process5844bc8" taskpriority="0" logused="1873648" waitresource="KEY: 7:72057594228441088 (0e00ce038ed0)"
                   waittime="4514" ownerId="1661509575" transactionname="user_transaction" lasttranstarted="2010-09-07T11:27:40.423"
                   XDES="0x37979ae90" lockMode="X" schedulerid="7" kpid="3260" status="suspended" spid="104" sbid="0" ecid="0"
                   priority="0" trancount="2" lastbatchstarted="2010-09-07T11:27:43.350" lastbatchcompleted="2010-09-07T11:27:43.350"
                   clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="5988" loginname="xxx"
                   isolationlevel="read committed (2)" xactid="1661509575" currentdb="7" lockTimeout="4294967295"
                   clientoption1="673185824" clientoption2="128056">
            <executionStack>
              <frame procname="SP2" line="68" stmtstart="5272" stmtend="5598"
                     sqlhandle="0x030007003432350f109a0c00e99d00000100000000000000">
                UPDATE Table1 ...
              </frame>
            </executionStack>
            <inputbuf>
              Proc [Database Id = 7 Object Id = 255144500]
            </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594228441088" dbid="7" objectname="Table1" indexname="Index1"
                   id="lock448e2c580" mode="X" associatedObjectId="72057594228441088">
            <owner-list>
              <owner id="process5844bc8" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process5827708" mode="RangeS-S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594228441088" dbid="7" objectname="Table1" indexname="Index1"
                   id="lock2ba335880" mode="RangeS-S" associatedObjectId="72057594228441088">
            <owner-list>
              <owner id="process5827708" mode="RangeS-S" />
            </owner-list>
            <waiter-list>
              <waiter id="process5844bc8" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </deadlock-list>
  </TextData>
  <TransactionID />
  <LoginName>xx</LoginName>
  <StartTime>2010-09-07T11:27:47.867</StartTime>
  <ServerName>xxx</ServerName>
  <LoginSid>xxx</LoginSid>
  <EventSequence>116538375</EventSequence>
  <IsSystem>1</IsSystem>
  <SessionLoginName />
</EVENT_INSTANCE>

SP1 is carrying out a choose that can take data from 5 different tables (Table1 to Table5) (uses inner query etc.) SP2 works an update on Table1.
A fascinating factor is among the posts that SP2 updates is really a foreign key area in Table1 and primary key of Table2 while both Table1 and Table2 are members of the choose statement of SP1, unsure this really is relevant but did not wish to lose out anything.

NOTE: indexname="Index1" (in deadlock graph above) -- Index1 is on a single column that's foreign type in Table1 and primary key of Table2.