Concurrent updates handling in ASP.NET or SQL Server 2008

Question

There is a table called Accounts that looks like this:

AccountID   AccountName   AccountTotalMoney
-------------------------------------------
  1           Steven           600
  3           Scott            800

But users can updates the row record at the same time like below now:

User A:UPDATE Accounts SET AccountTotalMoney=700 WHERE AccountID=1;
User B:UPDATE Accounts SET AccountTotalMoney=900 WHERE AccountID=1;
User C:UPDATE Accounts SET AccountTotalMoney=1000 WHERE AccountID=1;
.
.
.

So I would like to prevent multiple users updating the same record at the same time . Just one after another.

I am a newbie in this aspect. Sorry for my bad english. Thanks in advance!

Answer 1

There are a lot of possibilities.

[opinion: i think most web application do optimistic concurrency handling]:

A good tutorial on optimistic concurrency handling is found here: http://msdn.microsoft.com/en-us/library/bb404102.aspx

Small abstract:

Similarly, when two users are visiting a page, one user might be in the midst of updating a record when it is deleted by another user. Or, between when a user loads a page and when they click the Delete button, another user might have modified the contents of that record.

There are three concurrency-control strategies available:

• Do nothing—If concurrent users are modifying the same record, let the last commit win (the default behavior). •
• Optimistic concurrency—Assume that while there might be concurrency conflicts every now and then, the vast majority of the time such conflicts won't arise; therefore, if a conflict does arise, just inform the user that their changes can't be saved, because another user has modified the same data.
• Pessimistic concurrency—Assume that concurrency conflicts are commonplace and that users won't tolerate being told their changes weren't saved because of another user's concurrent activity; therefore, when one user starts updating a record, lock it, thereby preventing any other users from editing or deleting that record until the user commits their modifications.