Concurrency Conflict Facts
Saturday, February 9th, 2008Concurrency is the process of ensuring that changes to a database take place in an orderly manner so that changes made by one user do not conflict with changes made by another user. A concurrency conflict occurs when multiple users attempt to modify data at the same time.
There are two methods you can use to manage database concurrency: pessimistic concurrency and optimistic concurrency.
| Concurrency Method | Description |
| Pessimistic | With pessimistic concurrency, the data source is locked, allowing only one user access at a time. Pessimistic concurrency eliminates concurrency errors because the data source remains locked for all read and write operations. Other users cannot make changes until the lock is released.Pessimistic concurrency uses the connected data access model. For this reason, it is not very scalable and does not allow simultaneous data access. Use pessimistic concurrency when:
|
| Optimistic | With optimistic concurrency, locks are only placed on the data source during update operations. Using optimistic concurrency conserves server connections and allows multiple user access to the data source. However, it also requires that checks are performed to detect concurrency conflicts.Concurrency errors happen in a disconnected data access model because the client system works with a local copy of the data and because multiple users can modify data at the same time. The following example describes when the concurrency conflict would occur:
|
When you configure a DataAdapter using the Wizard, selecting the Use optimistic concurrency option will turn on concurrency conflict detection. With optimistic concurrency enabled:
- Update commands in the DataAdapter are modified to check for conflicts before the update is performed.
- If no conflicts exist, changes are made to the data source.
- If a conflict occurs, the command will throw the System.Data.DBConcurrencyException exception and changes will not be made to the data source.
- By catching this exception, you can add code to respond to the concurrency conflict.
Note: You can also write your own code to manually detect conflicts before updating the data source. However, using the DataAdaper configuration Wizard automates the process.
There are four general approaches you can take to handle the concurrency conflicts.
| Resolution Method | Description | Implementation |
| Last in Wins | Changes are made to the data source regardless of any other changes that might have been made. |
|
| Dead Heat or Stalemate | Concurrency conflicts are detected, and the data source is not modified when a conflict occurs. Keep your changes in the DataSet. |
|
| Don’t Force and Revert | Concurrency conflicts are detected, and the data source is not modified when a conflict occurs. Revert the DataSet to the original (reject your changes, but do not get the changes from the data source). |
|
| Don’t Force and Refresh | Concurrency conflicts are detected, and the data source is not modified when a conflict occurs. Refresh the DataSet (get the changes from the data source). |
|
Note: In every case when a conflict is detected, you should add code to let the user know what error occurred. You can also add code that analyzes the conflict and performs additional resolution steps. Read the Row property of the exception arguments to identify the exact location of the conflict and respond accordingly.