KyaPoocha.com

There are three common techniques for managing what happens when users try to modify the same data at the same time: pessimistic, optimistic, and last-in wins. They each handle concurrency issues differently.
The pessimistic approach says: “Nobody can cause a concurrency violation with my data if I do not let them get at the data while I have it.” This tactic prevents concurrency in the first place but it limits scalability because it prevents all concurrent access. Pessimistic concurrency generally locks a row from the time it is retrieved until the time updates are flushed to the database. Since this requires a connection to remain open during the entire process, pessimistic concurrency cannot successfully be implemented in a disconnected model like the ADO.NET DataSet, which opens a connection only long enough to populate the DataSet then releases and closes, so a database lock cannot be held.
Another technique for dealing with concurrency is the last-in wins approach. This model is pretty straightforward and easy to implement-whatever data modification was made last is what gets written to the database. To implement this technique you only need to put the primary key fields of the row in the UPDATE statement’s WHERE clause. No matter what is changed, the UPDATE statement will overwrite the changes with its own changes since all it is looking for is the row that matches the primary key values. Unlike the pessimistic model, the last-in wins approach allows users to read the data while it is being edited on screen. However, problems can occur when users try to modify the same data at the same time because users can overwrite each other’s changes without being notified of the collision. The last-in wins approach does not detect or notify the user of violations because it does not care. However the optimistic technique does detect violations.
In optimistic concurrency models, a row is only locked during the update to the database. Therefore the data can be retrieved and updated by other users at any time other than during the actual row update operation. Optimistic concurrency allows the data to be read simultaneously by multiple users and blocks other users less often than its pessimistic counterpart, making it a good choice for ADO.NET. In optimistic models, it is important to implement some type of concurrency violation detection that will catch any additional attempt to modify records that have already been modified but not committed. You can write your code to handle the violation by always rejecting and canceling the change request or by overwriting the request based on some business rules. Another way to handle the concurrency violation is to let the user decide what to do. The sample application that is shown in Figure 1 illustrates some of the options that can be presented to the user in the event of a concurrency violation.