question with a stored procedure

I have a stored procedure that inserts a new row if the user doesn't exist in the table...that part works. I then need it to check to see if the user has changed their program code and update the row if they have. the way I'm trying to accomplish this is by selecting their id and program code by what I'm passing it, and if it does not exist, I update the row. For some reason it's not updating the table. I'm new to stored procedures so I'm not sure if I'm doing this correctly or not. Can you please take a look and let me know if this looks ok? This is for MSSQL 2000:

CREATE PROCEDURE [dbo].[InsertUpdateProcedure] (@.LastNamevarchar(255),@.FirstNamevarchar(255),@.Emailvarchar(255),@.ColleagueIDvarchar(50),@.Programvarchar(50))AS IFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueID)BEGIN--The row doesn't exist. Insert code goes hereINSERT INTO ept_users (users_colleague_id,users_last_name,users_first_name,users_email_address,users_program)VALUES(@.ColleagueID,@.LastName,@.FirstName,@.Email,@.Program)ENDIFNOT EXISTS(SELECT users_colleague_idFROM ept_usersWHERE users_colleague_id = @.ColleagueIDAND users_program = @.Program)BEGINUPDATE ept_usersSETusers_program = @.Program,users_email_sent = 0,users_billed_current = 0,users_second_email_sent = 0WHEREusers_colleague_id = @.ColleagueIDENDGO

nevermind...it was choking on some null values. All is working now.sql

Question regarding stored procs

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.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|||(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/techin.../2000/books.asp|||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.|||(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/techin.../2000/books.asp

Question on OUTPUT feature of DML

I need to audit inserts/updates/deletes on active tables to audit tables on a set of tables that have foreign key constaints with the update cascade and delete cascade defined. I can explicitly code the delete/update on the parent table to perform an OUTPUT to an audit table, but how do I OUTPUT the cascaded delete/update that happens on the child table because of the FK constraint with delete cascade defined without having to resort to triggers.

Thanks,

-chiraj

You might want to check into the use of DML triggers for this kind of issue, but this is not something that I have used much.

Dave

|||

Thanks for the response.

Using DML triggers is a no-brainer. I have used them all my life. I was wondering if it could be accomplished with the OUTPUT clause. I believe it is a limitation of the OUTPUT clause unless someone can show me otherwise.

Thanks,

-chiraj.