Multi-User Systems and Concurrency
In a single-user application information can be read, created, updated or deleted without the risk of interfering with other people's work. When more than one user has the ability to access shared information, concurrency control becomes essential.
Concurrency Issues
When developing multi-user systems it is important to consider the desired outcome when two or more users or services simultaneously try to access the same data. The most serious problems occur when two users try to modify an item at the same the same time or when one user is editing information whilst others read the original data.
As an example, consider a database that contains all of a company's customer information. If two users simultaneously open the same customer record, make changes to the information and then save their modifications, several data integrity issues are possible. We can see these by looking at the timeline of the two concurrent transactions:
| User A | User B | Problem | Stored Customer Data |
|---|
| Opens the customer record. | | | "Janet Smith"
"Silver Level" |
| Changes the customer's surname to recognise their marriage. The new surname is "White". | | | |
| Opens the customer record. | User B sees the name "Janet Smith", not "Janet White". | |
| Changes the customer level to "Gold". | User A is unaware of the change to Gold status. At this point, both users have the customer information on screen but both sets of visible details are incorrect. | |
| Saves the customer. | | User B is unaware that the stored data in the customer database has been changed. | "Janet White"
"Silver Level" |
| Saves the Customer. | User B's action has caused the revised surname to be overwritten. Both users are unaware of the lost information. | "Janet Smith"
"Gold Level" |
Concurrency Control
The problems described above are some of those that must be prevented to maintain the integrity of a system's data in a multi-user system. These issues are generated because it is necessary to make temporary, local copies of information from the official data store; in this case the customer database. These local copies are essential to permit data to be modified but cause more up-to-date information to exist outside of the official data than within.
A concurrency control system provides safe access to shared information, removing the risk of collisions that cause data integrity problems or data loss such as that described above. This is usually achieved by either preventative locking, where access to data being edited by another user is limited or removed, or by the detection of collisions, where problematic changes are disallowed or require correction.
Locking Strategies
In this article we will discuss two types of concurrency control system. These are optimistic locking and pessimistic locking. This will be the first article in a series of three. The two following articles will provide working examples of the locking strategies using SQL Server tables and stored procedures.
11 March 2010