Posted by Erland Sommarskog on 06/29/05 10:41

(leodippolito@gmail.com) writes:
> Erland, why do you say " If you just use SET TRANSACTION ISOLATION
> LEVEL, the two processes will deadlock." ?
>
> Suppose T1 executes with SERIALIZABLE and T2 tries to execute the same
> proc at the same time ... Well, as far as I understand, T2 will wait
> for T1 to finish and then go on.. right? Why exactly is " WITH
> (UPDLOCK) " necessary?

Because with plain serializable this happens:

T1 performs NOT EXISTS check, and retains a shared lock
T2 performs NOT EXISTS check, and retains a shared lock
T1 tries to insert, but is blocked by T2
T2 tries to insert, but is blocked by T1
=> Deadlock

UPDLOCK is a shared lock, so it does not block other readers. However,
only one process have an UPDLOCK on a resource, so T2 would be blocked
until T1 has committed. And when T2 goes ahead, T2 finds that the rows
is already there, and does not try to insert.

> If it's not a problem for you, could you provide me a safe (deadlock
> free) example of insert procedure that would check the existance of the
> record before inserting?

Hey, that's what I leave as an exercise to the reader. :-) Seriously,
I encourage you to try these things by running from separate windows
in Query Analyzer, and try the various possibilities, to see what deadlocks,
what gives errors and what works smoothly. This is a good lab exercise
to get an understanding of things. What is problematic is to emulate
the concurrency, but some WAITFOR statements are usually good enough.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]