What types of locks exist in PostgreSQL?

PostgreSQL provides the following types of locks for managing concurrent access:

• Shared Lock (SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE): Allows other transactions to perform parallel read operations but blocks certain types of write or other locks.
• Exclusive Lock (ACCESS EXCLUSIVE): Prevents access to the object by any other transactions, including read.
• Row-level Lock (FOR UPDATE, FOR SHARE): Locks access to specific table rows. Allows other transactions to read these rows (except FOR UPDATE), but blocks their modification.
• Page-level Lock: Low-level lock used when modifying data pages in the buffer cache. Usually not visible directly to users.
• Table-level Lock: Lock applied to the entire table. Can be explicitly set (LOCK TABLE) or implicitly during DDL operations.
• Advisory Lock: Application-level locks managed by the user. Not automatically imposed, used for synchronization in application logic. Can be session or transaction-based.

Examples of lock usage:

// Row-level lock for subsequent update
SELECT * FROM users WHERE status = 'pending' FOR UPDATE;

// Explicit table lock in SHARE mode
LOCK TABLE orders IN SHARE MODE;

The type of lock automatically applied during DML operations (INSERT, UPDATE, DELETE) depends on the specific operation and transaction isolation settings.

Operation Type	Automatically Imposed Locks
SELECT	Does not impose exclusive locks; depends on isolation level
INSERT	RowExclusiveLock
UPDATE	RowExclusiveLock (on modified rows)
DELETE	RowExclusiveLock (on deleted rows)