In addition to MVCC locking, one can control locking explicitly when MVCC does not provide a desirable behavior. Generally speaking, PostgreSQL provides three locking mechanisms, which are:
- Table-level locks
- Row-level locks
- Advisory locks
Tables can be locked in several locking modes; the syntax of the LOCK
statement is as follows:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ] where lockmode is one of: ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
PostgreSQL locks the table implicitly when invoking an SQL command. It locks the table using the least restrictive mode to increase concurrency. When the developer desires a more restrictive lock, then the LOCK
statement can be used.