Why don't I see "FOR UPDATE" in the SQL generated by a locked ActiveRecord query?

Question

I'm reading "The Rails 5 Way", and on page 191 I see the following:

Pessimistic locking takes place at the database level. The SELECT statement generated by Active Record will have a FOR UPDATE (or similar) clause added to it...

The Rails docs appear to contain the same information:

Locking::Pessimistic provides support for row-level locking using SELECT … FOR UPDATE and other lock types.

Chain ActiveRecord::Base#find to ActiveRecord::QueryMethods#lock to obtain an exclusive lock on the selected rows:

Account.lock.find(1) # SELECT * FROM accounts WHERE id=1 FOR UPDATE

As an experiment, I wanted to reproduce this FOR UPDATE statement on my local machine. I know that the way to initiate a transaction with pessimistic locking is to call the .lock class method (the book gives the example t = Timesheet.lock.first). So I ran the following code in the REPL of a toy Rails app (v 5.1.6) which contains an Order class:

irb(main):015:0> Order.transaction do       
irb(main):016:1* o1 = Order.lock.first
irb(main):017:1> o1.update_attributes(name: 'Foo Bar')
irb(main):018:1> end

This produced the following output:

(0.3ms)  begin transaction
Order Load (0.2ms)  SELECT  "orders".* FROM "orders" ORDER BY "orders"."id" ASC LIMIT ?   [["LIMIT", 1]]
SQL (1.1ms)  UPDATE "orders" SET "name" = ?, "updated_at" = ? WHERE "orders"."id" = ?  [["name", "Foo Bar"], ["updated_at", "2018-11-04 03:01:35.593868"], ["id", 1]]
(0.4ms)  commit transaction
=> true

I don't see FOR UPDATE in either the SELECT or UPDATE statements. Am I doing something wrong when attempting to specify pessimistic locking? Or do I have an incorrect expectation of what SQL should be output?

score 1 · Accepted Answer · answered Nov 05 '18 at 01:33

I figured out that my toy app was using the default Rails sqlite database. I created a new toy app (rails new newbie --database=postgresql), create a new User model with several instances, and ran User.lock.first, and I saw the following:

irb(main):004:0> User.lock.first
  User Load (1.7ms)  SELECT  "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1 FOR UPDATE  [["LIMIT", 1]]
=> #<User id: 1, name: nil, phone: nil, created_at: "2018-11-05 01:28:23", updated_at: "2018-11-05 01:28:23">

As you can see, FOR UPDATE appears in the SQL query. From this Stack Overflow answer, I see that by default SQLite doesn't support pessimistic locking:

SELECT ... FOR UPDATE OF ... is not supported. This is understandable considering the mechanics of SQLite in that row locking is redundant as the entire database is locked when updating any bit of it.

Why don't I see "FOR UPDATE" in the SQL generated by a locked ActiveRecord query?

1 Answers1