Question regarding stored procs

[leodippolito@gmail.com]

Hello,

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)

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?

I appreciate any information about this.

Leonardo.

[Stu]

Re: Question regarding stored procs

Hi Leonardo,

The answer to your first question is yes; if you do not establish a
transaction (with the appropriate locking mechanism), then you can have
concurrency issues. However, you can establish a transaction and set
the isolation level to a higher restriction to avoid this scenario.

Check the SQL Server Books OnLine for transactions and transaction
isolation levels; that should get you started.

HTH,
Stu

[Erland Sommarskog]

Re: Question regarding stored procs

(leodippolito@g mail.com) writes:[color=blue]
> 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)[/color]

Yes.
[color=blue]
> 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?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[leodippolito@gmail.com]

Re: Question regarding stored procs

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?

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?

Thanks for all.

Leonardo.

[Erland Sommarskog]

Re: Question regarding stored procs

(leodippolito@g mail.com) writes:[color=blue]
> 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?[/color]

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.
[color=blue]
> 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?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp