CODEWITHSUNDEEP
sql-serversql-server-2005
Mohammed Rabee
Mohammed Rabee

Reputation: 355

Recommendations to Handle MAX(NUM) for multi-user

I have one Tickets table that contain numbers and another details
When user create a new ticket the new number should be MAX(Num)+1
The problem is if there is about 25 users working simultaneously, how can I handle this number for each new ticket
(if max no. is 1000 then each new ticket will get 1001, because the transactions not committed yet)
Please note that the user should see the ticket number before commit.

Upvotes: 0

Views: 64

Answers (1)

Tanner
Tanner

Reputation: 22743

Based on your comment, I believe you should use an identity column that auto increments and then have an additional bit column with a default value to specify that the row is saved/committed:

CREATE TABLE Tickets  
(  
 id int IDENTITY(1,1),  
 -- other columns
 isSaved bit DEFAULT 0 
);

With this approach a row is created with a unique id each time a ticket is made, the default value for isSaved will be false \ 0. When you get to the stage the ticket is saved/committed, you just set the isSaved flag to true \ 1.

If required, you could have a clean up task that deletes tickets that weren't saved/committed by checking the value of the isSaved column.

Upvotes: 1

Related Questions