I've got a web application (ASP.internet 2.) which has a database (SQL Server) in the background. I am thinking about ways how to deal with database concurrency if two customers place exactly the same data towards the same table simultaneously. What are the way additional situation? Thanks ahead of time.
To avoid exactly the same data being Placed simultaneously, make use of a unique index around the posts that you would like to become unique. The very first
INSERT will succeed, handle the mistake for that losing
To avoid 2+ customers from modifying exactly the same (existing) record simultaneously, use positive concurrency http://en.wikipedia.org/wiki/Optimistic_concurrency_control. With SQL Server it's not hard to implement positive concurrency utilizing a
TIMESTAMP column. Browse the timestamp together with your data. When upgrading the row, make sure that the timestamp value is identical. When the timestamps don't match, then your user had (was viewing) an outdated row. Handle that situation properly.
A good 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();
You just need:
BEGIN TRANSACTION Place XXXXXX INTO TABLE1 ...... Place YYYYY INTO TABLE2 ........ COMMIT if (commit_unsuccessful)
SQLserver will require proper care of the relaxation.
If you're attempting to prevent replicates, the easiest response is to produce a UNIQUE index around the column you need to be unique.
If you're attempting to prevent several user from modifying exactly the same record simultaneously, your best choice would be to give a last modified timestamp towards the table. While you browse the record to show on screen you browse the timestamp, and merely before you decide to write the alterations you look into the timestamp again. Whether it has transformed, which means another user has modified the record and you ought to avoid the vary from being written.
Not understanding the way your C# code foretells the database (O/R, ADO.Internet...), it's tough to provide you with a helpful answer...
EDIT: all of the good examples are wonderful, however they will not help if he's using SubSonic for instance.