Row-locking problem

  • Thread starter Thread starter Ben Fidge
  • Start date Start date
B

Ben Fidge

I've written a system using ADO.NET that comprises two ASP.NET applications
running on different servers but accessing the same Sql Server 2000
database.

The first application is an intranet based booking system for taking orders
over the phone, and the other is an internet based e-commerce application
placing bookings in the same tables as the first application.

When a booking is placed it is assigned a reference number which is a simple
sequence number stored in a table called Admin, as an integer field called
Last_Allocated_TicketNumber.

Very rarely two bookings are placed at precisely the same time and get
assigned the same reference number from the Admin table. The stored
procedure that updates the Last_Allocated_TicketNumber is defined as so:

CREATE PROCEDURE dbo.prcBookings_GetNewTicketNo

@aTicket_No int output

AS

select @aTicket_No = Last_Allocated_TicketNumber from Admin;

select @aTicket_No = @aTicket_No + 1;

update Admin set Last_Allocated_TicketNumber = @aTicket


How can I prevent this from happening, and temporarily lock the record while
it's being updated? I know ADO.NET doesn't support pessimistic locking. I'm
using SqlTransactions, as a few other tables get updated at the same time,
and I use the default IsolationLevel of ReadCommitted.


Kind regards

Ben
 
Hi Ben,

Since your ticket number is just incremented by 1 you could remove the
Last_Allocated_TickedNumber from the Admin table and use an identity
(auto-increment) field in your Tickets table. This way every ticket will be
guaranteed a new ticket number. To find that last ticket you could query
this table for Max(TicketNo), and since SQL Server is generating your
numbers for you, you probably won't even have to do this anymore. This will
mean re-working your architecture a little bit but it will pay off in the
end. Good luck! Ken.
 
Hi Ken,

I did think of this when designing the database but I needed to be able to
specify a starting number for the sequence (ie 0001000) and let the user
alter the Last_Allocated_TicketNumber from time to time. The customer buys a
block of tickets off a third party and resells them, so for each new block
of tickets, the sequence number get's set to first ticket in the block (-1).

Furthermore, there's several thousand records already in the bookings table
that have ticket numbers assigned to them using my current system.

But thanks for your help anyway,

Ben
 
Look into using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

From the BOL "Places a range lock on the data set, preventing other users
from updating or inserting rows into the data set until the transaction is
complete. This is the most restrictive of the four isolation levels. Because
concurrency is lower, use this option only when necessary. This option has
the same effect as setting HOLDLOCK on all tables in all SELECT statements
in a transaction."
 
Back
Top