The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Managing concurrency in database

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.
Rohit Send private email
Tuesday, May 15, 2007
Tuesday, May 15, 2007
I don't know SQL Server ... does it have a Sequence Number concept like DB2? I use a compound key trick from a Scott Ambler article from years ago. Get a sequence number from the database, guaranteed unique by the db, as the base part of the key. Then increment your own number in memory, say from 000 to 999, and concatenate the two parts as strings. That way we only go to the database for a sequence number every 1,000 inserts. Also works nicely across a cluster ... unless you require keys to be inserted in order.
Stan James Send private email
Tuesday, May 15, 2007
> 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 about you just try it and see if it works before you go all nuts with micro optimization?
son of parnas
Tuesday, May 15, 2007
A simple trick - if your specifications permit it - is to simply avoid assigning a ticket number until the operator saves the ticket.

The question I usually ask is: what do we want to happen if the operator starts filling out a ticket, changes their mind and cancels it before they ever save it?

If we want the ticket to go poof and pretend it never existed, get the number last.

If we want to keep the "cancelled" and incomplete ticket and we want to preserve the sequence instead of giving it a separate, unrelated number, then yes, you'll have to use one of the tricks proposed. In my experience though, this is extremely rare.
Tuesday, May 15, 2007
If you're thinking of migrating a single-user Access database to a thousands-of-concurrent-users SQL Server database, and you think your big stumbling-block is going to be controlling the assigment of record IDs, you need to get more experienced technical help now.
Robert Rossney Send private email
Tuesday, May 15, 2007
Why not just create a new record with an identity field and an inprogress (or whatever) flag. then when the ticket is complete update record to completed. if the ticket is abandoned it just stays as in progress.

Tuesday, May 15, 2007
I'd look at adding a key seed table. Allocate and update a ticket number as soon as the operator opens the form. If the record is not saved and you need to make sure ticket numbers do not have gaps, recycle any unused numbers into another table and allocate the ticket number from that table first

Wednesday, May 16, 2007
>if the ticket is abandoned it just stays as in progress.

I would recommend marking it as abandoned, but otherwise I think this is the best solution.
Wednesday, May 16, 2007
+1 what Robert said.  Get help.
Thursday, May 17, 2007
For each different ticket issuing, you should have a unique sequence ID. Just concatenate it with the Ticket ID.
Thursday, May 24, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz