Concurrency Control

From: database replication - Optimistic vs Multi Version Concurrency Control - Differences? - Stack Overflow

There are 2 main concurrency control approaches:

• Pessimistic Concurrency Control: this approach assumes that conflicting operations happen more frequently (that's why it's called pessimistic). Since the conflicts are common, this approach makes use of locks to prevent conflicting operations from executing, assuming that there is no significant overhead from their usage.
• Optimistic Concurrency Control: this approach assumes that conflicting operations are rare and they do not happen so frequently. Under this assumptions, the locks would impose significant & not needed overhead to the performance. For this reason, this approach generally avoids locking and attempts to execute the operations, checking (at the commit of each transaction), whether there has been a conflict with another transaction during its operations. If there was any conflict, this approach proceeds with aborting the transactions that had conflicting operations.

One widely known algorithm of pessimistic concurrency control is the 2-phase locking.

Two widely known algorithms of optimistic concurrency control are:

• The timestamp-based concurrency control
• The multi-version concurrency control

Optimistic concurrency control (OCC), also known as optimistic locking, is a non-locking concurrency control method. (From: Optimistic concurrency control - Wikipedia)