# Lock:Relation The `Lock: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. **Topics** + [Supported engine versions](#apg-waits.lockrelation.context.supported) + [Context](#apg-waits.lockrelation.context) + [Likely causes of increased waits](#apg-waits.lockrelation.causes) + [Actions](#apg-waits.lockrelation.actions) ## Supported engine versions This wait event information is supported for all versions of Aurora PostgreSQL. ## Context 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, Aurora PostgreSQL generates a `Lock: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`, `VACUUM FULL`, 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](https://www.postgresql.org/docs/13/explicit-locking.html) 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. ## Likely causes of increased waits When the `Lock: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. `VACUUM FULL` acquires an `ACCESS EXCLUSIVE` lock, and `ANALYZE` acquires a `SHARE UPDATE EXCLUSIVE` lock. Both types of locks can cause a `Lock: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. ## Actions We recommend different actions depending on the causes of your wait event. **Topics** + [Reduce the impact of blocking SQL statements](#apg-waits.lockrelation.actions.reduce-blocks) + [Minimize the effect of maintenance operations](#apg-waits.lockrelation.actions.maintenance) + [Check for reader locks](#apg-waits.lockrelation.actions.readerlocks) ### Reduce the impact of blocking SQL statements 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 canceled. Set this value at the session level. ### Minimize the effect of maintenance operations Maintenance operations such as `VACUUM` and `ANALYZE` are important. We recommend that you don't turn them off because you find `Lock: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 `Lock:Relation` waits caused by autovacuum tasks, perform any needed autovacuum tuning. For information about tuning autovacuum, see [ Working with PostgreSQL autovacuum on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum.html) in the * Amazon RDS User Guide*. ### Check for reader locks 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 and a reader session. The replay process waits for the duration of `max_standby_streaming_delay` before canceling 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. | Sequence event | Session | Command or Output | | --- | --- | --- | | Sets an environment variable called READER with the specified value and tries to connect to the DB instance with this endpoint. | Reader session | CLI command: 
export READER=aurorapg2.12345678910.us-west-1.rds.amazonaws.com

psql -h $READER
Output: ``` psql (15devel, server 10.14) Type "help" for help. ``` | | Sets an environment variable called WRITER and tries to connect to the DB instance with this endpoint . | Writer session | CLI command: 
export WRITER=aurorapg1.12345678910.us-west-1.rds.amazonaws.com
psql -h $WRITER
Output: ``` psql (15devel, server 10.14) Type "help" for help. ``` | | The writer session creates table t1 on the writer instance. | Writer session | PostgreSQL query: 
postgres=> CREATE TABLE t1(b integer);
CREATE TABLE
| | If there are no conflicting queries on the writer, the ACCESS EXCLUSIVE lock is acquired on the writer immediately. | Writer session | `ACCESS EXCLUSIVE` lock enabled | | The reader session sets a lock timeout interval of 100 milliseconds. | Reader session | PostgreSQL query: 
postgres=> SET lock_timeout=100;
SET
| | The reader session tries to read data from table t1 on the reader instance. | Reader session | PostgreSQL query: 
postgres=> SELECT * FROM t1;
Sample output: ``` b --- (0 rows) ``` | | The writer session drops t1. | Writer session | PostgreSQL query: 
postgres=> BEGIN;
BEGIN
postgres=> DROP TABLE t1;
DROP TABLE
postgres=>
| | The query times out and is canceled on the reader. | Reader session | PostgreSQL query: 
postgres=> SELECT * FROM t1;
Sample output: ``` ERROR: canceling statement due to lock timeout LINE 1: SELECT * FROM t1; ^ ``` | | To determine the cause of the error. the reader session queries `pg_locks` and `pg_stat_activity` | Reader session | PostgreSQL query: 
postgres=> SELECT locktype, relation, mode, backend_type
postgres=> FROM pg_locks l, pg_stat_activity t1
postgres=> WHERE l.pid=t1.pid AND relation = 't1'::regclass::oid;
| | The result indicates that the `aurora wal replay` process is holding an `ACCESS EXCLUSIVE` lock on table t1. | Reader session | Query result: 
 locktype | relation |        mode         |   backend_type
----------+----------+---------------------+-------------------
 relation | 68628525 | AccessExclusiveLock | aurora wal replay
(1 row)
|