The Sql Server 2008 R2 instance in question is a heavy load OLTP production server. The deadlock issue came up a few days back and is still unresolved. We received the Xml deadlock report that listed the stored procedures involved in the deadlock and some other details. I'll try to list down the facts from this xml first:
Two stored procedures are involved in the deadlock, say SP1 and SP2. According to the report SP1 was running in Isolation level "Serializable" and SP2 was running in "ReadCommitted".
We have investigated the following:
Are we setting IsolationLevel of SP1 to "Serializable" inside SP or in Code? - No.
Is any other SP whose IsolationLevel is "Serializable" calling SP1? - No.
Are the table used by SP1 called by any other SP that has Isolation Level as "Serializable"? - Yes. There are SPs that have Isolation Level set to "Serializable" and access the same tables as SP1, but we don't know whether they were running at the time of deadlock or not as the deadlock
report only showed SP1 and SP2.
Lines of thought:
We have considered the following possible causes:
Deadlock is occurring because SP1 is running as "Serializable". - Why is this SP running in Serializable when I haven't set it? Is the Isolation level escalating (like locks do)? If we figure this out and make it run as ReadCommitted, will the issue be resolved?
Any other SP is running, locking the table used by SP1 and causes a deadlock between SP1 and SP2. - Wouldn't this SP be listed in the deadlock report? Can the deadlock report miss such a dependency? If yes then we might only be getting partial information. This still doesn't resolve how SP1 is running in Serializable, though.
Suggestions:
If this information is not sufficient in resolving the problem, how can I obtain more information from SQL Server for my purpose and what information should I try to collect?
Any other Line of Thought that you'd pursue in solving this issue?
Update:
This is the trace log information for the deadlock. I've changed the names of SPs etc. but have checked and verified that the changes don't miss out any relevant information. Check the notes succeeding the code for more info 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 performing a select that takes data from 5 different tables (Table1 to Table5) (uses inner query etc.) SP2 performs an update on Table1.
An interesting thing is one of the columns that SP2 updates is a foreign key field in Table1 and primary key of Table2 while both Table1 and Table2 are part of the select statement of SP1, not sure this is relevant but didn't want to miss out anything.
NOTE: indexname="Index1" (in deadlock graph above) -- Index1 is on the same column that is foreign key in Table1 and primary key of Table2.