| Goal | This query identifies row-level BEFORE triggers that validate data by querying other tables (SELECT ... FROM ... WHERE) before raising an exception, but fail to acquire a shared lock (FOR SHARE) on the referenced rows. Under PostgreSQL's default READ COMMITTED isolation level, reading a reference or classifier row (such as checking whether a status, category, or type is active) without FOR SHARE allows concurrent transactions to deactivate or modify that reference row before the triggering transaction commits, leading to write skew and data integrity violations. |
|---|---|
| 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
Acquire a FOR SHARE lock on the referenced row using a positive existence check (e.g., IF NOT EXISTS (SELECT 1 FROM ref_table WHERE id = NEW.id AND is_active FOR SHARE) THEN RAISE EXCEPTION ...) so that the valid reference row remains locked and cannot be deactivated by a concurrent transaction before commit.
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.
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
| Name | Description |
|---|---|
| Concurrency control | Queries of this category provide information about concurrency control. |
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |