Managing Concurrency

I want to centralize my previous standalone application. Previous application was using VB.NET and Access XP. Now I want to keep a centralized database (SQL Server 2005) and VB.NET 2005. At this point of time thousands of concurrent users will connect to the database at the same time.

In my application, when a ticket is being issued to a tourist, an SQL query finds the max(ticketno) for the current month from the main table and reserves this number for the current ticket. When the operator has finished entering information and clicks SAVE button, the record is saved to the main table with this ticket no. Previously there was no issue since the database was standalone.

I want to know how to block the new ticket no so that other concurrent users are not using the same number for saving a record. How to better tune the database for thousands of concurrent users at the same time? I also want that the other user must not get an error message when he attempts to save a record. It should be automatically handled by the database.

I would NOT try to lock a process that first gets a ticket number, then waits until someone clicks the 'SAVE' button, and then updates the master table and then unlocks the process.

This will not scale and will be a MAJOR headache.

I recommend that you use an IDENTITY field for the 'ticketno', and let the 'system' take care of the updating/incrementation.

|||Can you please illustrate?|||

Arnie's suggestion is a good one...there doesn't seem to be a strong justification to pull in the complexity of some kind of key management system and there doesn't appear to be a need for the client application to have any knowledge of the ticketno value prior to saving the information. If you need to maintain referential integrity between tables, you can use the @.@.IDENTITY system function to get the last identity value that was entered in the master table. For example:

CREATE TABLE Tickets (TicketID INT IDENTITY, PurchaseDate datetime, CustomerID int) <NOTE: I'm leaving out the key relationship to the identity column on a customer table>

DECLARE @.TicketID AS int

INSERT INTO Tickets (PurchaseDate, CustomerID) VALUES (GETDATE(), @.SomeValue)

SET @.TicketID = @.@.IDENTITY

INSERT INTO Table2 ( @.SomeInfo, @.SomeInfo2, @.TicketID)

|||

Which part, how to define a 'TicketNo' column in a table as INDENTITY?

Look in Books Online, Topics: IDENTITY, CREATE TABLE

OR, the problems with scaleability?

It if is the later, consider that you have a choke point that every user must wait in line to access. And if some user takes a bit more time before clicking the [SAVE] button, well everyone else just has to wait because the number incrementing process is locked in the scenario you described.

|||

I recommend using SCOPE_IDENTITY instead of @.@.IDENTITY.

Under some situations, @.@.IDENTITY may provide inaccurate data.

|||Ticket No in our case is not a sequence number but in this format:

000123-0507-0101

The first part is the max(TicketNo) of current month. The second part contains month and year and the third part contains the code of the place. After each month end, the ticket no starts with 1 again.

Here I also want to know whether taking a string (like above) a primary key affects performance as opposed to a numeric primary key.