Joe Celko's SQL for Smarties, 3rd Edition

32.4. Pessimistic Concurrency Control

Pessimistic concurrency control is based on the idea that transactions are expected to conflict with each other, so we need to design a system to avoid the problems before they start.

All pessimistic concurrency control schemes use locks. A lock is a flag placed in the database that gives exclusive access to a schema object to one user. Imagine an airplane toilet door, with its “occupied” sign.

The differences are the level of locking they use; setting those flags on and off costs time and resources. If you lock the whole database, then you will have, in effect, a serial batch processing system, since only one transaction at a time is active. In practice, you would do this only for system maintenance work ...