Concurrency

Concurrency is an issue with databases that have high read and write traffic. If the same row within a database is being accessed by two or more users at a time, and that row is read-write capable, concurrency issues arise. Without concurrency controls, users can experience some of the following problems.

• Loss of updated information
  Multiple users accessing the same row update the rows independently. One user overwrites the updates of the other.
• Dirty read
  Two users are accessing the same row. One reads the data as the other is updating. The reader does not get all of the updated information.
• Nonrepeatable read
  Data being updated between multiple people causes the database to provide different information when performing analyses, resulting in an unrepeatable analysis.
• Phantom reads
  Results from multiple users deleting or adding data to a row. Users either loose data or they read new data that wasn’t there after the last time it was accessed.
• Missing or double reads
  This is caused by multiple users adding or deleting rows referenced by other tables. For missing rows, a user deletes a row that another user was referencing, causing that data to disappear. For a double read, new rows are being accessed that were not being accessed before that could cause redundant data being provided in a query.

Concurrency controls are used to prevent these issues from arising. There are two types of concurrency controls: pessimistic and optimistic. These terms dictate how a database compares changes to row data. SQL Server monitors changes to the database and responds according to the concurrency control settings.

Optimistic control assumes there is a low likelihood of data being conflicted. If data is conflicted, SQL can either create an error alert for the previous user that changed data, and allow them to roll-back the change, or it will compare the time stamps of when it was read and written to make a determination.

Pessimistic control assumes there is a high-likelihood of data being conflicted. Only one user at a time can read and write the data. This process is sometimes called “locking out” other users. Once the row data is released, another user can access the data for read-write. Users that are locked only have read capabilities.