Lock - tuple

The tuple event occurs when a backend process is waiting to acquire a lock on a tuple.

The event tuple indicates that a backend is waiting to acquire a lock on a tuple while another backend holds a conflicting lock on the same tuple. The following table illustrates a scenario in which sessions generate the tuple event.

Or you can simulate this wait event by using the benchmarking tool pgbench. Configure a high number of concurrent sessions to update the same row in a table with a custom SQL file.

To learn more about conflicting lock modes, see Explicit Locking in the PostgreSQL documentation. To learn more about pgbench, see pgbench in the PostgreSQL documentation.

When this event appears more than normal, possibly indicating a performance problem, typical causes include the following:

• A high number of concurrent sessions are trying to acquire a conflicting lock for the same tuple by running UPDATE or DELETE statements.
• Highly concurrent sessions are running a SELECT statement using the FOR UPDATE or FOR NO KEY UPDATE lock modes.
• Various factors drive application or connection pools to open more sessions to execute the same operations. As new sessions are trying to modify the same rows, DB load can spike, and tuple can appear.

For more information, see Row-Level Locks in the PostgreSQL documentation.

We recommend different actions depending on the causes of your wait event.

Find out whether a blocker session has been in the idle in transaction state for long time. If so, consider ending the blocker session as a short-term solution. You can use the pg_terminate_backend function. For more information about this function, see Server Signaling Functions in the PostgreSQL documentation.

For a long-term solution, do the following:

• Adjust the application logic.
• Use the idle_in_transaction_session_timeout parameter. This parameter ends any session with an open transaction that has been idle for longer than the specified amount of time. For more information, see Client Connection Defaults in the PostgreSQL documentation.
• Use autocommit as much as possible. For more information, see SET AUTOCOMMIT in the PostgreSQL documentation.

The tuple event might occur constantly, especially in a busy workload time. In this situation, consider reducing the high concurrency for very busy rows. Often, just a few rows control a queue or the Boolean logic, which makes these rows very busy.

You can reduce concurrency by using different approaches based in the business requirement, application logic, and workload type. For example, you can do the following:

• Redesign your table and data logic to reduce high concurrency.
• Change the application logic to reduce high concurrency at the row level.
• Leverage and redesign queries with row-level locks.
• Use the NOWAIT clause with retry operations.
• Consider using optimistic and hybrid-locking logic concurrency control.
• Consider changing the database isolation level.