Is this a Single Resource Deadlock?

Question

Is this a Single Resource Deadlock?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Alex Webber (2/24/2011)

Just to add, I can't find anywhere that we're using SERIALIZABLE isolation level (we have no TransactionScope usage either) but after googling around there appear to be one or two posts that suggest key-range-locks can occur whilst in READ COMMITTED isolation level. Like the top answer here:

http://stackoverflow.com/questions/446729/sql-server-2005-key-range-locks-in-read-committed-transaction-isolation-level%5B/quote%5D

Yes, there are a few examples of this - the indexed view thing I mentioned earlier and cascading RI. Do you have an ON UPDATE/DELETE CASCADE foreign key constraint?

Wouldn't apply to your INSERT though 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 2

Code to find sessions/requests using SERIALIZABLE:

-- Sessions

SELECT *

FROM sys.dm_exec_sessions AS S

WHERE S.transaction_isolation_level = 4

;

-- Active requests

SELECT *

FROM sys.dm_exec_requests AS R

WHERE R.transaction_isolation_level = 4

;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Alex Webber SSCommitted Points: 1543 More actions · Answer 3

No FK constraints either. When using Reflector to peek inside the LINQ-To-SQL ORM, you can see that it uses an explicit transaction with Read Committed isolation level. Guess I'll go away and have a play to see what's really going on.