When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect another. This is called concurrency control.
Concurrency control theory has two classifications for the methods of instituting concurrency control:
A system of locks prevents users from modifying data in a way that affects other users. After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until it is released by the owner. This is called pessimistic control because it mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.
In optimistic concurrency control, users do not lock data when they read it. When an update is performed, the system checks to see if the data was changed by another user after it was read. If the data was updated by another user, an error is raised. Typically, the user receiving the error rolls back the transaction and starts over. This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction outweighs the costs of locking data when it is read.
Microsoft® SQL Server™ supports a wide range of optimistic and pessimistic concurrency control mechanisms. Users specify the type of concurrency control by specifying:
These attributes can be defined using either Transact-SQL statements or through the properties and attributes of the database APIs such as ADO, OLE DB, and ODBC.
| Four Concurrency Problems | Cursor Concurrency |