"Marty" <smarty@community.nospam> wrote in message
news:33549C69-8632-489A-B431-767A53871768@microsoft.com...
>I am writing a holiday booking database. I have an absence table and a
>rules
> table.
> For any one day only X days holiday can be booked, where X is stored in
> the
> rules table.
> So when booking holiday we lookup X in the rules table, then get the count
> of records in the absence table for the date we want to book. If the count
> is
> less than X, we book the time.
> I want to prevent 2 people from reading X (say X=100), when there are 99
> records for today already and both entering a new record.
> Each step in the process is done in a separate Stored procedure ( looking
> up
> x, getting the count etc) and these are called from a main Stored
> procedure,
> which has a start transaction at the top and end transaction at the
> bottom.
> Once this transaction begins I need to lock both tables, at a minimum
> making
> the rules unreadable and the absence table unwritable.
> I currently do this:
> Begin transaction T1
> select * from rules with (TABLOCKX)
> select * from absence with (TABLOCKX)
>
> which seems to do the trick but I wonder if this is overkill.
>
> Would I get away with this and would it be better ?
>
> Begin transaction T1
> select * from rules with (TABLOCKX)
>
> I assume that if a second person fires off the same Stored procedure, the
> first thing it will do is try to select * from rules and have to wait for
> the
> first person's Stored procedure to finish completley before continuing. Is
> this correct ?
> --
> Marty