Web application database concurrency

Question

Dear,

I have a web application (ASP.net 2.0) that has a database (SQL Server) at the background. I'm considering ways how to handle database concurrency if two users insert the same data to the same table at the same time. Are there any way to handle this case? Thanks in advance.

Jimmy

Answer 1

All you need is:

BEGIN TRANSACTION;
INSERT XXXXXX INTO TABLE1 ......
INSERT YYYYY  INTO TABLE2 ........
COMMIT;
if (commit_failed) { tell user to try again!; };

SQLserver will take care of the rest.

Answer 2

If you are trying to prevent duplicates, the simplest answer is to create a UNIQUE index on the column you want to be unique.

If you are trying to prevent more than one user from modifying the same record at the same time, your best bet is to add a last modified timestamp to the table. As you read the record to display on the screen you read the timestamp, and just before you write the changes you check the timestamp again. If it has changed, that means another user has modified the record and you should prevent the change from being written.

Answer 3

Without knowing how your C# code talks to the database (O/R, ADO.NET...), it's difficult to give you a useful answer...

EDIT: all the examples are great, but they won't help if he's using SubSonic for example.

Answer 4

To prevent the same data being INSERTed at the same time, use a unique index on the columns that you want to be unique. The first INSERT will succeed, handle the error for the losing INSERT appropriately.

To prevent 2+ users from modifying the same (existing) record at the same time, use optimistic concurrency http://en.wikipedia.org/wiki/Optimistic_concurrency_control. With SQL Server it's easy to implement optimistic concurrency using a TIMESTAMP column. Read the timestamp with your data. When updating the row, check that the timestamp value is the same. If the timestamps don't match, then the user had (was viewing) an outdated row. Handle that case appropriately.

An example using SqlClient:

command.CommandText = @"
    UPDATE tbl
    SET LastName = @LastName, FirstName = @FirstName
    WHERE ID = @ID AND Timestamp = @Timestamp
    ";

int rowCount = command.ExecuteNonQuery();
if (rowCount != 1)
    throw new DBConcurrencyException();