| Goal | This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries or Common Table Expressions (WITH clauses) without an explicit FOR SHARE or FOR UPDATE locking clause. Failure to acquire a lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer data modification, potentially leading to race conditions and data inconsistencies. Routines utilizing the xmin system column or incrementing an explicit row-version column (e.g., version, rea_versioon) are excluded, as they implement Optimistic Concurrency Control (OCC). Additionally, routines that explicitly lock target rows in a preceding SELECT ... FOR UPDATE/SHARE statement immediately prior to a WITH clause are excluded. |
|---|---|
| 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 ( ) tags, and highlights the SELECT and FOR SHARE keywords in bold (). Because multiple routines in the same schema can share a name (function overloading), routine parameters are included alongside the schema and routine name for unique identification. The query detects INSERT, UPDATE, and DELETE statements combined with SELECT ... FROM ... WHERE subqueries or CTEs that lack FOR SHARE or FOR UPDATE locking within the same scope, while respecting subquery parenthesis boundaries to avoid false positives across multiple CTEs. It excludes routines that implement optimistic locking (via xmin or version column incrementing) or acquire locks in a standalone SELECT ... FOR UPDATE/SHARE statement right before a WITH block (which is required in PostgreSQL when modifying the locked table in the outer query). |
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
Collections
This query belongs to the following collections:
Find problems automatically
Queries, 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 .
| Name | Description |
|---|---|
| Find problems automatically | Queries, 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 . |
Categories
This query is classified under the following categories:
Concurrency control
Queries of this category provide information about concurrency control.
Does not work in some earlier PostgreSQL version
Queries of this category provide information that was not available in some earlier PostgreSQL version
Result quality depends on names
Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
Transactions
Queries of this catergory provide information about the use of transactions.
User-defined routines
Queries of this category provide information about the user-defined routines
| Name | Description |
|---|---|
| Concurrency control | Queries of this category provide information about concurrency control. |
| Does not work in some earlier PostgreSQL version | Queries of this category provide information that was not available in some earlier PostgreSQL version |
| Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |
| Transactions | Queries of this catergory provide information about the use of transactions. |
| User-defined routines | Queries of this category provide information about the user-defined routines |
Further reading and related materials: