Lock - transactionid
transactionid event occurs when a transaction is waiting for a row-level lock.
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
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:
SELECT … FOR UPDATE
SELECT … FOR KEY SHARE
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
DELETE commands on other tables that reference the row.
When this event appears more than normal, the cause is typically
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.
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
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
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
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
SELECT … FOR UPDATEstatements. You can also decrease the number of foreign keys accessed by
SELECT … FOR KEY SHAREstatements.
idle in transaction, use the following strategies:
- Turn on autocommit wherever possible. This approach prevents transactions from blocking other transactions while waiting for a
- Search for code paths that are missing
- 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
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.