Concurrency issue

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Hi Eric,

I have a question. I have a stored procedure to register new customer. Every customer will be assigned a new customer ID. The customer ID is a numerical data type. For example, the 1st customer will have a customer ID 1000000001 and the 2nd customer will be assigned the number 1000000002.

I have a table(systemData) to store the last customer ID. So the stored procedure will retrieve the last customer ID from this table and increment by 1 and assign this number to a new customer. Then it will update this number to the last Customer ID in systemData table.

My question is, since this customer ID should be unique to everyone, if I execute a few instances of the stored procedure concurrently, logically, the problem of duplication of Customer ID will happen.

Do you have any suggestion, so that I can avoid this problem. I need a program that can be protected, which means even if I execute multiple intances of the stored procedure, at any one time, it will allow only one instance of the stored procedure to retrieve the last Customer ID then update the last Customer ID, then only allow a second instance of the stored procedure to retrieve the last Customer ID. But this process must be fast enough because each instance of the stored procedure is registering a few thousands of new customer each time.

Thanks and Merry X'mas, Teoh

-- Anonymous, December 20, 2004

Answers

Wai Chen,

You need for the ID column to have the identity property. This property can have a seed value and an increment.

So your table creation T-SQL could be like this:

CREATE TABLE customer ( ID int IDENTITY (1000000001, 1), fname varchar (40), lname varchar (40), other_columns varchar(500) )

Hope this helps,

Eric

-- Anonymous, December 22, 2004


Moderation questions? read the FAQ