How to handle data concurrency in ASP.NET?

Question

Hi! all

I have an application, that is accessing by number of users at the same time. Those users, who are accessing the application getting the same id.

Here what i am doing in the code is, when they are creating new user i am getting a max id from DB and increasing the value to 1. So that they are getting same ID. so that i am facing concurrency in this situation.

How to solve this problem.

I need to display different numbers when the users click on NewUser.

I am using SQL server 2008 and .NET 3.5 and C#.NET

Thanks in advance.

Answer 1

You can use SCOPE_IDENTITY This will solve your problem!

UPDATE:

If your ID is not an IDENTITY column you can still fetch it from the database upon insert - with a single database call.

For example:

Solution 1:

DECLARE @MyID varchar -- could be an uniqueidentifier (GUID) as well
SET @MyID = GetNextID() -- a function that returns IDs

INSERT INTO [myTable] ([myData], [MyID])
VALUES(@myData, @MyID)

SELECT @MyID

Solution 2: (for SQL Server 2008)

INSERT INTO [myTable] ([myData], [MyID])
VALUES(@myData, GetNextID())
OUTPUT INSERTED.*

(the OUTPUT statement will return the newly inserted record - you could also return only the ID column with OUTPUT INSERTED.MyID)

-HTH

Answer 2

This is a common problem but is easy to fix..

1. Change your user table so that the userid is an identity column
2. When you insert your new user details into this table a new unique userid will automatically created for you by SQLServer
3. To obtain the userid that has been created for you, use the SCOPE_IDENTITY() function

For more information on identity columns and how to retrieve your new userid, have a look at this tutorial..

SQLTeam - Understanding Identity Columns

Edit (based on comment below)

The reason you are currently getting duplicate user ids is because you have a time gap between requesting the max(userid) and updating it/inserting your new user record.

It doesn't matter if this delay (between request and update) is 10 minutes or 10 milliseconds, if your site is being hit hard enough the problem will still occur.

By using identity columns, SQLServer effectively removes the delay for you by creating the id for you. No other process/user can be given the same userid.

Try with the following code...

    /*Create Table*/
    CREATE TABLE TestTableSO (UserID INT IDENTITY(1,1), Username NVARCHAR(50))

    /*Insert some data, note that I'm not providing a UserID on my Insert*/
    INSERT INTO TestTableSO (Username) VALUES ('Joe')
    INSERT INTO TestTableSO (Username) VALUES ('Dan')
    INSERT INTO TestTableSO (Username) VALUES ('Fred')
    INSERT INTO TestTableSO (Username) VALUES ('Sam')

    /*Look at the data I've inserted*/
    SELECT * FROM TestTableSO

    /*Insert one more record*/
    INSERT INTO TestTableSO (Username) VALUES ('Roger')

    /*Look at the new UserID I have been given by SQL Server*/
    SELECT SCOPE_IDENTITY() as NewUserID

    /*Drop our table, only needed for testing*/       
    DROP TABLE TestTableSO

From this code you will get the following output...

UserID  Username
------------------
1       Joe
2       Dan
3       Fred
4       Sam

NewUserID
-----------
5

Hope this makes sense! Again, to repeat. You will not solve this problem using Max(UserID) in the way you are.

Edit #2

Creating UserIDs without storing any information to go with the UserID is a BAD idea. You risk running out of available IDs much sooner because of users that will access your site, get given a userid but then not fill in any details. Only provide them with a userid if they are a true user.