Mutually exclusive access to a stored proc.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Mutually exclusive access to a stored proc.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-06 : 11:59:35

John writes "Tried to find this in BOL and some searches on Google, I just can't seem to boil down my question to a simple search term. Perhaps some of you bringht people can assist.

This example isn't really what I am trying to do, but it is the simplest demonstration of my question/problem that I can come up with.

Lets assume we have a database to track seating reservations to an event. With a single table structured like this

SeatNumber(int) PK
SeatDesirability (int) (1-100 ranking of how good the seats are)
ReservedBy (varchar) Person who has the seat or Null if available.

I need to write a stored procedure that accepts a person's name and assigns them the most desirable seat available (lowest SeatDesirability with a null in the ReservedBy Column).

It looks like the simplest way to implement that is the following code:

Declare @SeatNo
Select TOP1 @SeatNo = SeatNumber From SeatingTable WHERE (ReservedBy is null) ORDER BY SeatDesirability
Update TABLE ReservedBy='persons name' WHERE SeatNumber=@SeatNo

This seems to cover the problem fine until you consider the possibility of multiple reservation agents calling the stored procedure simultaneously. In this situation is is theoretically possible for a second user to get the same Seat number as another user if they get the SELECT in before the other user runs the UPDATE.

My question is, is there any way to ensure that only one user can be executing a particular stored procedure at one time? If not, does wrapping this in a transaction perhaps help?"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-06 : 13:29:44

You can use locking hints in your sproc to ensure that this doesn't happen. Check BOL for the 'locking hints' section for more details.

-ec

Kristen
Test

22859 Posts

Posted - 2004-07-06 : 20:22:58

Wouldn't this do the trick (without hints)?


Update TABLE 
SET ReservedBy='persons name' 
WHERE SeatNumber=
(
    Select TOP1 SeatNumber 
    From SeatingTable 
    WHERE (ReservedBy is null) 
    ORDER BY SeatDesirability 
)

Kristen

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-07-07 : 07:49:23

Let me be a devils advocate for just a second.

Who orders just one seat for an event?
Would not the individual want all the seats together if they ordered say 5 seats and someone had canceled 2 seats 4 rows up from the current level?
Sorry to mess up theory with reality.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources