Lock - relation

The 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 EXCLUSIVE can block all concurrent access. Data definition language (DDL) operations such as DROP TABLE, TRUNCATE, and CLUSTER acquire ACCESS EXCLUSIVE locks implicitly. ACCESS EXCLUSIVE is also the default lock mode for LOCK TABLE statements that don’t specify a mode explicitly.
• Using CREATE INDEX (without CONCURRENT) on a table conflicts with data manipulation language (DML) statements UPDATE, DELETE, and INSERT, which acquire ROW EXCLUSIVE locks.

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 ROLLBACK or COMMIT statement. 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.

When the 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.

• Maintenance operations

  Health maintenance operations such as VACUUM and ANALYZE can significantly increase the number of conflicting locks. ANALYZE acquires a SHARE UPDATE EXCLUSIVE lock. This type of lock can cause a relation wait 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 EXCLUSIVE relation locks are replicated to reader instances. However, the ACCESS EXCLUSIVE relation lock will conflict with any ACCESS SHARE relation 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:

• Use the NOWAIT option – Some SQL commands, such as SELECT and LOCK statements, support this option. The NOWAIT directive 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 NOWAIT when it requests the lock on C, it can fail fast and ensure that transaction A doesn’t have to wait indefinitely.

• Use SET lock_timeout – Set a lock_timeout value 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 VACUUM and 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 relation waits 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.