Bug #22165
closedPriority update possibly locking container requests by accident
Description
This is only intended to apply to the container table:
def row_lock_for_priority_update container_uuid # Locks all the containers under this container, and also any # immediate parent containers. This ensures we have locked # everything that gets touched by either a priority update or state # update. ActiveRecord::Base.connection.exec_query %{ select 1 from containers where containers.uuid in ( select pri_container_uuid from container_tree($1) UNION select container_requests.requesting_container_uuid from container_requests where container_requests.container_uuid = $1 and container_requests.state = 'Committed' and container_requests.requesting_container_uuid is not NULL ) order by containers.uuid for update }, 'select_for_update_priorities', [container_uuid] end
However the postgres documentation says this:
If specific tables are named in a locking clause, then only rows coming from those tables are locked; any other tables used in the SELECT are simply read as usual. A locking clause without a table list affects all tables used in the statement. If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query. However, these clauses do not apply to WITH queries referenced by the primary query. If you want row locking to occur within a WITH query, specify a locking clause within the WITH query.
On the other hand it also says this:
When a locking clause appears at the top level of a SELECT query, the rows that are locked are exactly those that are returned by the query; in the case of a join query, the rows locked are those that contribute to returned join rows. In addition, rows that satisfied the query conditions as of the query snapshot will be locked, although they will not be returned if they were updated after the snapshot and no longer satisfy the query conditions. If a LIMIT is used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over by OFFSET will get locked). Similarly, if a locking clause is used in a cursor's query, only rows actually fetched or stepped past by the cursor will be locked.
So maybe it is fine? Adjusting it to "FOR UPDATE OF containers" is also harmless.
Related issues
Updated by Peter Amstutz about 1 month ago
- Target version changed from Development 2024-10-09 sprint to Future
Updated by Peter Amstutz 10 days ago
- Target version changed from Future to Development 2024-11-06 sprint
- Assigned To set to Peter Amstutz
Updated by Peter Amstutz 10 days ago
- Related to Bug #21540: occasional container_requests deadlock added