Learning PostgreSQL 11 - Third Edition

Summary

PostgreSQL provides several locking mechanisms to increase concurrency and performance, including implicit locking via MVCC, and explicit locking via table-level locks, row-level locks, and advisory locks.

The MVCC model is one of the biggest selling factors of PostgreSQL, since high performance can be achieved. In general, the MVCC model is suitable for most common database access patterns, and it is better to use the MVCC model instead of explicit locking where possible.

Using explicit locking via table-level or row-level locks enables the user to solve several data inconsistency issues. However, explicit locking, if not planned carefully, might increase the chances of having deadlocks. Finally, PostgreSQL provides advisory locks, ...