.NET Architecture & System Design Interview Question #75

What is pessimistic concurrency control?

Database Scaling, Partitioning, NoSQL & Data Consistency Mid-Level Intermediate

Quick Interview Answer

Pessimistic concurrency control prevents conflicting access by acquiring locks before or while data is being changed. Other transactions may wait until the lock is released.

Detailed Explanation

Pessimistic concurrency control prevents conflicting access by acquiring locks before or while data is being changed. Other transactions may wait until the lock is released.

It can be appropriate when conflicts are frequent and the protected operation is short, but long-held locks reduce concurrency and can contribute to blocking or deadlocks. The locking behavior depends on the database and transaction isolation level.

Code Example

Transaction A:
LOCK row
UPDATE row
COMMIT
UNLOCK

Transaction B:
waits while required lock is held