Lock - relation
relation event occurs when a query is waiting to acquire a lock on a table or view (relation) that’s currently locked by another transaction.
Most PostgreSQL commands implicitly use locks to control concurrent access to data in tables. You can also use these locks explicitly in your application code with the
LOCK command. Many lock modes aren’t compatible with each other, and they can block transactions when they’re trying to access the same object. When this happens, PostgreSQL generates a
relation event. Some common examples are the following:
- Exclusive locks such as
ACCESS EXCLUSIVEcan block all concurrent access. Data definition language (DDL) operations such as
ACCESS EXCLUSIVElocks implicitly.
ACCESS EXCLUSIVEis also the default lock mode for
LOCK TABLEstatements that don’t specify a mode explicitly.
CREATE INDEX (without CONCURRENT)on a table conflicts with data manipulation language (DML) statements
INSERT, which acquire
For more information about table-level locks and conflicting lock modes, see Explicit Locking in the PostgreSQL documentation.
Blocking queries and transactions typically unblock in one of the following ways:
- Blocking query – The application can cancel the query or the user can end the process. The engine can also force the query to end because of a session’s statement-timeout or a deadlock detection mechanism.
- Blocking transaction – A transaction stops blocking when it runs a
COMMITstatement. Rollbacks also happen automatically when sessions are disconnected by a client or by network issues, or are ended. Sessions can be ended when the database engine is shut down, when the system is out of memory, and so forth.
relation event occurs more frequently than normal, it can indicate a performance issue. Typical causes include the following:
Increased concurrent sessions with conflicting table locks
There might be an increase in the number of concurrent sessions with queries that lock the same table with conflicting locking modes.
Health maintenance operations such as
ANALYZEcan significantly increase the number of conflicting locks.
SHARE UPDATE EXCLUSIVElock. This type of lock can cause a
relationwait event. Application data maintenance operations such as refreshing a materialized view can also increase blocked queries and transactions.
Locks on reader instances
There might be a conflict between the relation locks held by the writer and readers. Currently, only
ACCESS EXCLUSIVErelation locks are replicated to reader instances. However, the
ACCESS EXCLUSIVErelation lock will conflict with any
ACCESS SHARErelation locks held by the reader. This can cause an increase in lock relation wait events on the reader.
We recommend different actions depending on the causes of your wait event.
To reduce the impact of blocking SQL statements, modify your application code where possible. Following are two common techniques for reducing blocks:
NOWAIToption – Some SQL commands, such as
LOCKstatements, support this option. The
NOWAITdirective cancels the lock-requesting query if the lock can’t be acquired immediately. This technique can help prevent a blocking session from causing a pile-up of blocked sessions behind it.
For example: Assume that transaction A is waiting on a lock held by transaction B. Now, if B requests a lock on a table that’s locked by transaction C, transaction A might be blocked until transaction C completes. But if transaction B uses a
NOWAITwhen it requests the lock on C, it can fail fast and ensure that transaction A doesn’t have to wait indefinitely.
SET lock_timeout– Set a
lock_timeoutvalue to limit the time a SQL statement waits to acquire a lock on a relation. If the lock isn’t acquired within the timeout specified, the transaction requesting the lock is cancelled. Set this value at the session level.
Maintenance operations such as
ANALYZE are important. We recommend that you don’t turn them off because you find
relation wait events related to these maintenance operations. The following approaches can minimize the effect of these operations:
- Run maintenance operations manually during off-peak hours.
- To reduce
relationwaits caused by autoanalyze tasks, perform any needed autoanalyze tuning.
You can see how concurrent sessions on a writer and readers might be holding locks that block each other. One way to do this is by running queries that return the lock type and relation. In the table you can find a sequence of queries to two such concurrent sessions, a writer session (left-hand column) and a reader session (right-hand column).
The replay process waits for the duration of
max_standby_streaming_delay before cancelling the reader query. As shown in the example, the lock timeout of 100ms is well below the default
max_standby_streaming_delay of 30 seconds. The lock times out before it’s an issue.
|master=> CREATE TABLE t1(b integer);||The writer session creates table
|standby=> SET lock_timeout=100;||The reader session sets a lock timeout interval of 100 milliseconds.|
|standby=> SELECT * FROM t1;
|The reader session tries to read data from table
master=> DROP TABLE t1;
|The writer session drops
|standby=> SELECT * FROM t1;
ERROR: canceling statement due to lock timeout
LINE 1: SELECT * FROM t1;
|The query times out and is canceled on the reader.|
|standby=> SELECT locktype, relation, mode, backend_type
FROM pg_locks l, pg_stat_activity t1
WHERE l.pid=t1.pid AND relation = ’t1’::regclass::oid;
locktype |relation |mode |backend_type
relation |68628525 |AccessExclusiveLock |startup
|The reader session queries