Posted by Erland Sommarskog on 06/28/05 10:35

(leodippolito@gmail.com) writes:
> It might be a basic question, but there it goes:
>
> I have a store procedure that first checks if the record exists and, if
> it doesn't, inserts the new record.
>
> Is the scenario below possible?
>
> (thread1) USER1 -> check if record "abc" exists
> (thread2) USER2 -> check if record "abc" exists
> (thread1) USER1 -> "abc" doesn't exist
> (thread2) USER2 -> "abc" doesn't exist
> (thread1) USER1 -> add "abc" as new record
> (thread2) USER2 -> add "abc as new record (OPS, this is an error,
> because "abc" already exists, it was just inserted by USER1)

Yes.

> I am wondering if this kind of concurrent, multi-threaded access
> happens with stored procedures.
>
> If yes, can I execute a procedure from start to finish without any
> other simultaneous procedure interrupting?

You would need to enclose the IF EXISTS + SELECT in a transaction.
Furthermore, you must make sure that the isolation level is serializable.
The defuault isolation level in SQL Server is READ COMMITTED, which
means that once the EXISTS check has passed, locks are released.

The best solution is to add the table hint "WITH (UPDLOCK)" in the
EXISTS query. This would make USER2 in this example to be blocked
already at this point. If you just use SET TRANSACTION ISOLATION
LEVEL, the two processes will deadlock.

One way to test issues like this, is to insert a WAITFOR in the code,
and then run from separate windows in Query Analyzer.

--
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]