PostgreSQL provides various
lock modes to control concurrent access to data in tables. These
modes can be used for application-controlled locking in
situations where MVCC does not
give the desired behavior. Also, most PostgreSQL commands automatically acquire
locks of appropriate modes to ensure that referenced tables are
not dropped or modified in incompatible ways while the command
executes. (For example, ALTER TABLE
cannot safely be executed concurrently with other operations on
the same table, so it obtains an exclusive lock on the table to
enforce that.)

To examine a list of the currently outstanding locks in a
database server, use the pg_locks system view. For more information
on monitoring the status of the lock manager subsystem, refer to
Chapter 27.

The list below shows the available lock modes and the
contexts in which they are used automatically by PostgreSQL. You can also acquire any of
these locks explicitly with the command LOCK. Remember that all of these lock modes
are table-level locks, even if the name contains the word
"row"; the names of the lock modes
are historical. To some extent the names reflect the typical
usage of each lock mode — but the semantics are all the same.
The only real difference between one lock mode and another is
the set of lock modes with which each conflicts (see Table
13-2). Two transactions cannot hold locks of conflicting
modes on the same table at the same time. (However, a
transaction never conflicts with itself. For example, it might
acquire ACCESS EXCLUSIVE lock and
later acquire ACCESS SHARE lock on the
same table.) Non-conflicting lock modes can be held
concurrently by many transactions. Notice in particular that
some lock modes are self-conflicting (for example, an
ACCESS EXCLUSIVE lock cannot be held
by more than one transaction at a time) while others are not
self-conflicting (for example, an ACCESS
SHARE lock can be held by multiple transactions).

Table-level lock modes

ACCESS SHARE

Conflicts with the ACCESS
EXCLUSIVE lock mode only.

The SELECT command acquires a
lock of this mode on referenced tables. In general, any
query that only reads a table and does not
modify it will acquire this lock mode.

ROW SHARE

Conflicts with the EXCLUSIVE
and ACCESS EXCLUSIVE lock
modes.

The SELECT FOR UPDATE and
SELECT FOR SHARE commands
acquire a lock of this mode on the target table(s) (in
addition to ACCESS SHARE locks
on any other tables that are referenced but not selected
FOR UPDATE/FOR SHARE).

The commands UPDATE,
DELETE, and INSERT acquire this lock mode on the
target table (in addition to ACCESS
SHARE locks on any other referenced tables). In
general, this lock mode will be acquired by any command
that modifies
data in a table.

This lock mode is not automatically acquired on user
tables by any PostgreSQL
command. However it is acquired on certain system
catalogs in some operations.

ACCESS EXCLUSIVE

Conflicts with locks of all modes (ACCESS SHARE, ROW
SHARE, ROW EXCLUSIVE,
SHARE UPDATE EXCLUSIVE,
SHARE, SHARE
ROW EXCLUSIVE, EXCLUSIVE,
and ACCESS EXCLUSIVE). This mode
guarantees that the holder is the only transaction
accessing the table in any way.

Acquired by the ALTER TABLE,
DROP TABLE, TRUNCATE, REINDEX, CLUSTER,
and VACUUM FULL commands. This
is also the default lock mode for LOCK TABLE statements that do not specify
a mode explicitly.

Tip: Only an ACCESS
EXCLUSIVE lock blocks a SELECT (without FOR
UPDATE/SHARE) statement.

Once acquired, a lock is normally held till end of
transaction. But if a lock is acquired after establishing a
savepoint, the lock is released immediately if the savepoint is
rolled back to. This is consistent with the principle that
ROLLBACK cancels all effects of the
commands since the savepoint. The same holds for locks acquired
within a PL/pgSQL exception
block: an error escape from the block releases locks acquired
within it.

In addition to table-level locks, there are row-level locks,
which can be exclusive or shared locks. An exclusive row-level
lock on a specific row is automatically acquired when the row
is updated or deleted. The lock is held until the transaction
commits or rolls back, just like table-level locks. Row-level
locks do not affect data querying; they block only writers to the same row.

To acquire an exclusive row-level lock on a row without
actually modifying the row, select the row with SELECT FOR UPDATE. Note that once the row-level
lock is acquired, the transaction can update the row multiple
times without fear of conflicts.

To acquire a shared row-level lock on a row, select the row
with SELECT FOR SHARE. A shared lock
does not prevent other transactions from acquiring the same
shared lock. However, no transaction is allowed to update,
delete, or exclusively lock a row on which any other
transaction holds a shared lock. Any attempt to do so will
block until the shared lock(s) have been released.

PostgreSQL doesn't remember
any information about modified rows in memory, so there is no
limit on the number of rows locked at one time. However,
locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark
them locked, and so will result in disk writes.

In addition to table and row locks, page-level
share/exclusive locks are used to control read/write access to
table pages in the shared buffer pool. These locks are released
immediately after a row is fetched or updated. Application
developers normally need not be concerned with page-level
locks, but they are mentioned here for completeness.

The use of explicit locking can increase the likelihood of
deadlocks, wherein two (or more)
transactions each hold locks that the other wants. For example,
if transaction 1 acquires an exclusive lock on table A and then
tries to acquire an exclusive lock on table B, while
transaction 2 has already exclusive-locked table B and now
wants an exclusive lock on table A, then neither one can
proceed. PostgreSQL
automatically detects deadlock situations and resolves them by
aborting one of the transactions involved, allowing the
other(s) to complete. (Exactly which transaction will be
aborted is difficult to predict and should not be relied
upon.)

Note that deadlocks can also occur as the result of
row-level locks (and thus, they can occur even if explicit
locking is not used). Consider the case in which two concurrent
transactions modify a table. The first transaction
executes:

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

This acquires a row-level lock on the row with the specified
account number. Then, the second transaction executes:

The first UPDATE statement
successfully acquires a row-level lock on the specified row, so
it succeeds in updating that row. However, the second
UPDATE statement finds that the row it
is attempting to update has already been locked, so it waits
for the transaction that acquired the lock to complete.
Transaction two is now waiting on transaction one to complete
before it continues execution. Now, transaction one
executes:

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

Transaction one attempts to acquire a row-level lock on the
specified row, but it cannot: transaction two already holds
such a lock. So it waits for transaction two to complete. Thus,
transaction one is blocked on transaction two, and transaction
two is blocked on transaction one: a deadlock condition.
PostgreSQL will detect this
situation and abort one of the transactions.

The best defense against deadlocks is generally to avoid
them by being certain that all applications using a database
acquire locks on multiple objects in a consistent order. In the
example above, if both transactions had updated the rows in the
same order, no deadlock would have occurred. One should also
ensure that the first lock acquired on an object in a
transaction is the most restrictive mode that will be needed
for that object. If it is not feasible to verify this in
advance, then deadlocks can be handled on-the-fly by retrying
transactions that abort due to deadlocks.

So long as no deadlock situation is detected, a transaction
seeking either a table-level or row-level lock will wait
indefinitely for conflicting locks to be released. This means
it is a bad idea for applications to hold transactions open for
long periods of time (e.g., while waiting for user input).

PostgreSQL provides a means
for creating locks that have application-defined meanings.
These are called advisory locks,
because the system does not enforce their use — it is up to the
application to use them correctly. Advisory locks can be useful
for locking strategies that are an awkward fit for the MVCC
model. Once acquired, an advisory lock is held until explicitly
released or the session ends. Unlike standard locks, advisory
locks do not honor transaction semantics: a lock acquired
during a transaction that is later rolled back will still be
held following the rollback, and likewise an unlock is
effective even if the calling transaction fails later. The same
lock can be acquired multiple times by its owning process: for
each lock request there must be a corresponding unlock request
before the lock is actually released. (If a session already
holds a given lock, additional requests will always succeed,
even if other sessions are awaiting the lock.) Like all locks
in PostgreSQL, a complete list
of advisory locks currently held by any session can be found in
the pg_locks system view.

Advisory locks are allocated out of a shared memory pool
whose size is defined by the configuration variables max_locks_per_transaction
and max_connections.
Care must be taken not to exhaust this memory or the server
will be unable to grant any locks at all. This imposes an upper
limit on the number of advisory locks grantable by the server,
typically in the tens to hundreds of thousands depending on how
the server is configured.

A common use of advisory locks is to emulate pessimistic
locking strategies typical of so called "flat file" data management systems. While a
flag stored in a table could be used for the same purpose,
advisory locks are faster, avoid MVCC bloat, and are
automatically cleaned up by the server at the end of the
session. In certain cases using this advisory locking method,
especially in queries involving explicit ordering and
LIMIT clauses, care must be taken to
control the locks acquired because of the order in which SQL
expressions are evaluated. For example:

In the above queries, the second form is dangerous because
the LIMIT is not guaranteed to be
applied before the locking function is executed. This might
cause some locks to be acquired that the application was not
expecting, and hence would fail to release (until it ends the
session). From the point of view of the application, such locks
would be dangling, although still viewable in pg_locks.

The functions provided to manipulate advisory locks are
described in Table
9-62.