Posted by Erland Sommarskog on 06/13/07 22:13

byahne (byahne@yahoo.com) writes:
> Actually, I spoke too soon. Even using the new stored procedure we
> are getting deadlock messages, but they are less periodic.
>
> Any other words of wisdom on why this might be happening and how to
> avoid it?

Did you also rewrite the procedure as Alex suggested? Or did you just
change the isolation level? In the latter case, you should add
"WITH (UPDLOCK)" to the SELECT query.

Else what happens is that two processes both get the read-lock on
the wrong, and then no one can procede with the UPDATE. Since only
one process at a time can hold an Update lock, one them will be held
up at this point - rather than both being held up later.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: How to Gnerate a Random ID Number
• Prev in forum: Re: how much bytes needed in sql server
• Thread view: Re: Deadlock within stored procedure - need help

[Reply to this message]