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 ReadCom
View Complete Post