Deadlock problem

[agar]

I've been stuck on this for over a day and I want to move on. Why do I keep getting deadlocks when the concurrent batches that are deadlocking should only be contending for one object to begin with (a single row in a single table)? I thought a deadlock happened only when process A has a lock on Obj.1 and wants a lock on Obj.2, and process B has a lock on Obj.2 and wants a lock on Obj.1? Does it have something to do with system tables or indexes? These deadlocks should not be happening.

When I analyze the problem in the Profiler tool, it says that all the "deadlock" events are in reference to the same object ID. That's how I know that the deadlock is happening on a single object.

 

[tlbroadbent]

SQL BOL says, "Deadlocking is often confused with normal blocking. When one transaction has a lock on a resource that another transaction wants, the second transaction waits for the lock to be released. By default, SQL Server transactions do not time out (unless LOCK_TIMEOUT is set). The second transaction is blocked, not deadlocked. For more information, see Customizing the Lock Time-out."

Are you sure deadlocks are occuring? If they are, SQL Server should terminate one of the process. If simple blocking is occuring, the blocked processes will wait until the blocking process completes.

Check out my FAQ (faq183-714) for links to resources about reducing blocking problems. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[agar]

Yes, I'm sure. I've been reading the links on your FAQ and they have been somewhat helpful but no answer yet.

The deadlock is happening in a stored procedure. I've got a somewhat complex system of nested stored procedures that all originates in a single entry-point SP. This entry-point SP is being called by multiple clients, and the calls from these clients need to be strictly serialized. I decided that the best way to do this was to implement a critical section, using a special row in a table as the "mutex", rather than trying to make the whole entire thing a giant transaction. So, this one table only has to be locked for a very short time.

declare @mutexID int
set @mutexID = 0

declare @incriticalsection bit
set transaction isolation level serializable
begin transaction
select @incriticalsection = incriticalsection
 from mutex
 where mutexID = @mutexID
update mutex
 set incriticalsection = 1
 where mutexID = @mutexID
commit transaction
-- set isolation level back to default
set transaction isolation level read committed

if (@incriticalsection = 0)
 begin
	exec TheRestOfMyStoredProcedures
	set transaction isolation level serializable
	update mutex
	 set incriticalsection = 0
	 where mutexID = @mutexID
	set transaction isolation level read committed
 end
else
 select 'sorry, try again later'
set nocount off
go

So what I get are these weird deadlocks where it looks like (from looking at the output from SQL Server profiler) 2 or more batches enter the transactions at the same time, and then they all deadlock on the mutex table. Looks like there are two entries in the profiler output for each batch that deadlocks. So maybe it's deadlocking because that table has a primary key, and so it has to lock the index and also lock the table, in order to do a scan? But why would a scan be necessary? Note that I am using the most restrictive isolation level.

 

[agar]

Okay I think I figured it out. I thought that setting the isolation level to "serializable" would mean that all locks are acquired at the beginning of the transaction, but looks like that's not the case. So, we still get the deadlock where process A gets a shared lock on the resource, then process B gets a shared lock on the resource, and they both want to get an exclusive lock but can't. I guess I will have to look into solving this by modifying the queries and adding table hints (?) to acquire either update or exclusive locks when I do the first "select".

In other words, the "serializable" transaction level guarantees serializability but doesn't guarantee freedom from this kind of deadlock.