Can you list the types of locks used in PostgreSQL and explain their features?

PostgreSQL uses various types of locks to manage concurrency and data integrity:

• Row-level locks — lock individual rows in a table. For example, FOR UPDATE locks selected rows to prevent concurrent modifications.
• Table-level locks — lock the entire table. There are different modes, such as:
  • ACCESS SHARE — allows reading, blocks schema modification operations.
  • ROW EXCLUSIVE — blocks other write operations.
  • EXCLUSIVE and ACCESS EXCLUSIVE — the most restrictive, blocking almost all operations.
• Advisory locks — user-defined locks that do not affect standard database locks, used for application-level synchronization.

Features:

• Locks in PostgreSQL are hierarchical and compatible across modes, allowing multiple transactions to work in parallel if their operations do not conflict.
• Row-level locks do not block reading of other rows.
• Table locks can be explicit or automatically applied by the system.

Example of explicit row locking:

BEGIN;
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- this row is now locked from modifications by other transactions
COMMIT;