UpdateManager doesn't have a way to add FOR UPDATE (pessimistic lock) in update subquery #147

Open
dovgandy opened this Issue Nov 12, 2012 · 1 comment

Projects

None yet

2 participants

@dovgandy

Here's a sample UPDATE statement with FOR UPDATE subquery in PostgreSQL 9.2:

UPDATE tickets
SET purchased = TRUE
WHERE ID in (
SELECT id
FROM tickets
WHERE NOT purchased
LIMIT 10
FOR UPDATE
)

I would expect to have this UPDATE statement from the following rails statement:
Ticket.where(:purchased => false).limit(10).lock(true).update_all(:purchased => true)

but FOR UPDATE clause is not added because UpdateManager doesn't have a way to add FOR UPDATE (pessimistic lock) in update subquery. File: update_manager.rb.

This is a screenshot from rails source where UpdateManager.lock should probably be called: http://bit.ly/RPsQ31

Is this something you think about adding in later versions or it was omitted with a purpose? I know such a FOR UPDATE lock was not supported in PostgreSQL before 9.0.

@swistak

Just found this issue and it seems to work correctly in arel 4.0.2

Given

def jobs_to_lock
    select('id').
      where(locked_at: nil, locked_by: nil).where("run_job_at <= ?", Time.zone.now).
      order(id: :asc).
      limit(1).
      lock
end

jobs_to_lock.to_sql correctly procduces

SELECT id FROM "jobs" WHERE "jobs"."locked_at" IS NULL AND "jobs"."locked_by" IS NULL AND (run_job_at >= '2014-04-25 09:54:53.641201') ORDER BY "jobs"."id" ASC LIMIT 1 FOR UPDATE

this also works as a subquery.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment