Concurrency Problem: Two separate dynamic SQL queries.

  • Thread starter Thread starter Jim Light
  • Start date Start date
J

Jim Light

How could I resolve the following concurreny problem, assuming that
this procedure is not written as one stored procedure in T-SQL, but as
two separate, dynamic SQL queries.


QUERY 1: Check if username is already taken, return value to signify
if taken or not.

If username if not already taken,

QUERY 2: Store username (and other user info).


Thank you.


Regards,

Jim Light
 
Ah, I can see any number of issues with writing this as two separate
queries. Suppose I ask you for change for a dollar. You look at your coins
and say "sure". Before I can hand you the dollar, your daughter comes by and
asks for a quarter for her lunch money (while your coins are out of your
pocket). When I hand over the dollar, you can't oblige. Unless you lock down
the whole database during the operation (the transaction), you can't
guarantee that the username does not already exist. That's why we write
INSERT statements that have conditional WHERE clauses that test for a
preexisting PK or simply put a unique index on the PK column(s). It does not
take a SP to do this.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Hi William,

Thank you for your thoughtful reply.
Indeed, I didn't consider using a WHERE clause in the INSERT
statement!

:)
 
Back
Top