January 18, 2010 at 9:25 am
kingscriber (1/18/2010)
I know that cursors can run slow. I am thinking that when you open a cursor (still trying to find an exact answer) it sets a different isolation level for anything containing the cursor. I don't know if anyone can verify this.
If you're talking about a SQL cursor, then not by itself it doesn't.
Either there's a different isolation level set by the client or there's an explicit SET TRANSACTION ISOLATION LEVEL somewhere. SQL doesn't just change the isolation level as it feels.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2010 at 2:43 pm
GilaMonster (1/18/2010)
If you're talking about a SQL cursor, then not by itself it doesn't.Either there's a different isolation level set by the client or there's an explicit SET TRANSACTION ISOLATION LEVEL somewhere. SQL doesn't just change the isolation level as it feels.
Yea, you mentioned that before. After doing some reading all day on cursors and triggers, There isn't anything there that would cause two different processes to have two different isolation levels.
I think I was skeptical because if the cursor was changing the lock type, then it could change how it reported the isolation level. However, this isn't the case.
I am currently resarching the client side. Right now it doesn't make any sense that both Nodes (processes) are originating from the same stored procedure happen to be of two different isolation levels. I have noticed that I am mixing LINQ to Sql and ADO.NET for some of my calls. I am currently wondering if both have default values for the isolation level I am certain that ADO.Net if you don't specify a Transaction class, it uses the isolation level of the Database. I am not certain of the TransactionScope class.
I do need to track down why the isolation levels are different. If you have suggestions on which forum I can query that in, that would be much appreciated while I am doing my research.
January 18, 2010 at 3:26 pm
I just got some breaking news about the TransactionScope Class and what is going on with my .Net client.
Currently the operation that I am calling uses a mix of ADO.NET and LINQ to SQL.
This is an ok practice however, there are different default isolation levels involved.
ADO.Net will use the default isolation level of the database without the use the of the transaction class.
Using LINQ to SQL TransactionScope class will use a default of serializable isolation level.
This explains further the differentiation between the isolation levels we were seeing with the two different processes that were deadlocking.
January 19, 2010 at 2:27 am
That's good to know. Can you change the isolation level in the linq? If you do, does it fix the deadlocks?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2010 at 7:13 am
GilaMonster (1/19/2010)
That's good to know. Can you change the isolation level in the linq? If you do, does it fix the deadlocks?
I can change it, and it does seem to fix the deadlock issue ! I ran it twice for good measure. The only thing now is to find out why LINQ switched from ReadCommitted to Serializable. So, I am going to consider this one solved. I can't thank you enough Gila !
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply