What Is Concurrency Control?
Concurrency Control : ensures that modifications made by one person do not adversely affect modifications that others make.
There are two type of concurrency control in DBMS.
- Pessimistic : Pessimistic concurrency control locks data when data is read in preparation for an update. Other users cannot then perform actions that would alter the underlying data until the user who applied the lock is done with the data. Use pessimistic concurrency where high contention for data exists and the cost of protecting the data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.
- Optimistic : Optimistic concurrency control does not lock data when data is initially read. Rather, when an update is performed, SQL Server checks to determine whether the underlying data was changed since it initially read it. If so, the user receives an error, the transaction rolls back, and the user must start over. Use optimistic concurrency when low contention for data exists and the cost of occasionally rolling back a transaction is less than the cost of locking data when it is read.