| Goal | This query identifies performance and concurrency bottlenecks in user-defined routines caused by executing a standalone SELECT ... FOR [NO KEY] UPDATE or PERFORM ... FOR [NO KEY] UPDATE statement on a table prior to modifying the exact same table with an UPDATE or DELETE statement.In PostgreSQL, UPDATE and DELETE statements automatically acquire the appropriate row-level write lock (FOR NO KEY UPDATE for non-key updates, or FOR UPDATE for key-column updates and deletes) on target rows that satisfy the WHERE predicate. Executing a separate, preceding SELECT/PERFORM ... FOR [NO KEY] UPDATE on the same table (without an ORDER BY clause for deterministic multi-row deadlock prevention) is redundant—incurring an unnecessary extra scan and lock operation—and degrades concurrency when the initial lock predicate is less restrictive than the subsequent modification (for instance, locking a row solely by its primary key before verifying its status or optimistic lock version). If the subsequent UPDATE or DELETE affects zero rows due to a version mismatch or invalid state, the transaction still retains the write lock acquired by the preliminary query, unnecessarily blocking concurrent transactions. |
||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Notes | Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. For better readability in a web browser, the query escapes < characters, replaces newline characters with HTML line break (br) tags, and highlights FOR UPDATE between and tags in the returned routine body. Because multiple routines in the same schema can share a name (function overloading), routine parameters are presented alongside the schema and routine name for unique identification. The query uses a regular expression with a backreference (\1) to capture the target table name in a standalone SELECT or PERFORM ... FROM
Fixing Suggestion
Remove the redundant standalone SELECT ... FOR UPDATE or PERFORM ... FOR UPDATE statement preceding the UPDATE or DELETE operation, and let the UPDATE or DELETE statement atomically filter and lock the target rows (including any state and optimistic row-version checks in its WHERE clause). If pre-locking multiple rows is strictly required to prevent deadlocks across concurrent batch operations, include an explicit ORDER BY clause on the primary key in the SELECT/PERFORM ... FOR UPDATE statement.
CollectionsThis query belongs to the following collections: Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
CategoriesThis query is classified under the following categories: Concurrency controlQueries of this category provide information about concurrency control. User-defined routinesQueries of this category provide information about the user-defined routines
|