Hi
We are facing a unique problem which ends up causing deadlocks. We have found that
We have a very heavy load OLTP Sql server 2008.
In .net code we set the Transaction isolation level to Read commited while executing a SP.
We find deadlocks and in the dead lock details it is explictly called out that the SP in question was executing in transaction level serializable. Lets call this SP1
The only other thing we can say is that there are some other unrelated stored procedures which access the same tables as SP1 and are executing in isolation level serializable.
Is it possible for one SP to escalate isolation level like locks are escalated?
We found the following links which hint this, by saying that isolation level is just a hint.
http://msdn.microsoft.com/en-us/library/ms229978(v=VS.90).aspx
http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx?pr=blog
http://msdn.microsoft.com/en-us/library/ms189857.aspx
http://aartemiou.blogspot.com/2009/01/table-level-locking-hints-in-sql-server.html
Any help in this regard will be great.
View Complete Post