Lock - transactionid

The transactionid event occurs when a transaction is waiting for a row-level lock.

The event transactionid occurs when a transaction is trying to acquire a row-level lock that has already been granted to a transaction that is running at the same time. The session that shows the transactionid wait event is blocked because of this lock. After the blocking transaction ends in either a COMMIT or ROLLBACK statement, the blocked transaction can proceed.

The multiversion concurrency control semantics of PostgreSQL guarantee that readers don’t block writers and writers don’t block readers. For row-level conflicts to occur, blocking and blocked transactions must issue conflicting statements of the following types:

• UPDATE
• SELECT … FOR UPDATE
• SELECT … FOR KEY SHARE

The statement SELECT … FOR KEY SHARE is a special case. The database uses the clause FOR KEY SHARE to optimize the performance of referential integrity. A row-level lock on a row can block INSERT, UPDATE, and DELETE commands on other tables that reference the row.

When this event appears more than normal, the cause is typically UPDATE, SELECT … FOR UPDATE, or SELECT … FOR KEY SHARE statements combined with the following conditions.

PostgreSQL can use granular row-level locking semantics. The probability of row-level conflicts increases when the following conditions are met:

• A highly concurrent workload contends for the same rows.
• Concurrency increases.

Sometimes the pg_stat_activity.state column shows the value idle in transaction. This value appears for sessions that have started a transaction, but haven’t yet issued a COMMIT or ROLLBACK. If the pg_stat_activity.state value isn’t active, the query shown in pg_stat_activity is the most recent one to finish running. The blocking session isn’t actively processing a query because an open transaction is holding a lock.

If an idle transaction acquired a row-level lock, it might be preventing other sessions from acquiring it. This condition leads to frequent occurrence of the wait event transactionid. To diagnose the issue, examine the output from pg_stat_activity and pg_locks.

Transactions that run for a long time get locks for a long time. These long-held locks can block other transactions from running.

Row-locking is a conflict among UPDATE, SELECT … FOR UPDATE, or SELECT … FOR KEY SHARE statements. Before attempting a solution, find out when these statements are running on the same row. Use this information to choose a strategy described in the following sections.

If concurrency is the issue, try one of the following techniques:

• Lower the concurrency in the application. For example, decrease the number of active sessions.
• Implement a connection pool.
• Design the application or data model to avoid contending UPDATE and SELECT … FOR UPDATE statements. You can also decrease the number of foreign keys accessed by SELECT … FOR KEY SHARE statements.

If pg_stat_activity.state shows idle in transaction, use the following strategies:

• Turn on autocommit wherever possible. This approach prevents transactions from blocking other transactions while waiting for a COMMIT or ROLLBACK.
• Search for code paths that are missing COMMIT, ROLLBACK, or END.
• Make sure that the exception handling logic in your application always has a path to a valid end of transaction.
• Make sure that your application processes query results after ending the transaction with COMMIT or ROLLBACK.

If long-running transactions are causing the frequent occurrence of transactionid, try the following strategies:

• Keep row locks out of long-running transactions.
• Limit the length of queries by implementing autocommit whenever possible.