Lock - tuple
tuple event occurs when a backend process is waiting to acquire a lock on a tuple.
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
|Time||Session 1||Session 2||Session 3|
|t1||Starts a transaction.|
|t2||Updates row 1.|
|t3||Updates row 1. The session acquires an exclusive lock on the tuple and then waits for session 1 to release the lock by committing or rolling back.|
|t4||Updates row 1. The session waits for session 2 to release the exclusive lock on the tuple.|
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.
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
- Highly concurrent sessions are running a
SELECTstatement using the
FOR NO KEY UPDATElock 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
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_timeoutparameter. 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.
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
NOWAITclause with retry operations.
- Consider using optimistic and hybrid-locking logic concurrency control.
- Consider changing the database isolation level.