Collaborative update processing 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 update the row entry at the same time, as shown below:

 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; . . . 

Therefore, I would like to prevent multiple users from updating the same record at the same time. Only one after another.

I am new to this aspect. Sorry for my bad english. Thanks in advance!

Answer 1

There are many possibilities.

[opinion: I think most web applications do optimistic concurrency handling]:

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

Small annotation:

Similarly, when two users visit a page, one user may be in the middle of updating a record when it is deleted by another user. Or, meanwhile, when the user loads the page, and when they click the "Delete" button, another user can change the contents of this entry.

There are three concurrency -control strategies available:

• Nothing to do. If concurrent users modify the same record, let the last win fix (default behavior). •
• Optimistic concurrency - Suppose that although concurrency conflicts may exist from time to time, in most cases such conflicts do not occur; therefore, if a conflict arises, simply inform the user that their changes cannot be saved because another user has changed the same data.
• Pessimistic concurrency - Assume that concurrency conflicts are common and that users will not tolerate reporting that their changes have not been saved due to other simultaneous user activity; therefore, when one user starts to update the record, blocks it, thereby preventing other users from editing or deleting this record until the user makes his changes.

Answer 2

I think the Pleuns answer is good. To be more specific:

• Optimistic coincidence. Make sure the line has not changed and rolls back if this happens. Example: Add a version column that increases after each update, and verify that the version number matches the update.

• Pessimistic concurrency: harder. Use IsolationLevel.Serializable isolation level to lock the row, but it is not a good idea when a transaction spans multiple web requests.