Pessimistic locking doesn't seem to work with mysql?

Rails
1

Hopefully I’m doing something stupid here. I was trying to use
pessimistic locking on a rails 2.3.4 project and it just doesn’t seem to
work. The issue was showing up on both OSX and Linux.

I’ve created a simplified reproducible for 2.3.10 for discussion
purposes. I’ll publish the full transcript of the steps I took to
create the project later, but for now, I’ve got a single model Foo with
a single Bar value that’s a string. I create a single Foo and saved it.

In one console window I run:

Foo.transaction do
f = Foo.find(1, :lock => true)
1.upto(10000000) { |i| puts i }
end

I was under the assumption I wouldn’t be able to access that row at all
until I finished printing out 10 million numbers. But while that is
running, I can go to another console window and get to it:

johnmudhead:locktest grant$ script/console
Loading development environment (Rails 2.3.10)

f = Foo.find(1)
=> #<Foo id: 1, bar: “AAAAAAAAAAAAAAA!”, created_at: “2010-11-01
19:50:58”, updated_at: “2010-11-01 19:50:58”>

That seems like I’m reading the row when I should be locked out of it.

I have confirmed that I’m using the InnoDB backend on MySQL, which
supports row level locking.

Anyone have any advice or tips? Do I have a gross misunderstanding of
pessimistic locks in ruby.

A full log of my reproducible case follows…

Thanks,

–
Grant

“I am gravely disappointed. Again you have made me unleash my dogs of
war.”

johnmudhead:~ grant$ cd src
johnmudhead:src grant$ rails 2.3.10 locktest
create
create app/controllers
create app/helpers
…
create log/development.log
create log/test.log
johnmudhead:src grant$ cd locktest/
johnmudhead:locktest grant$ script/generate model foo bar:string
exists app/models/
exists test/unit/
exists test/fixtures/
create app/models/foo.rb
create test/unit/foo_test.rb
create test/fixtures/foos.yml
create db/migrate
create db/migrate/20101101194756_create_foos.rb
johnmudhead:locktest grant$ cat config/database.yml

SQLite version 3.x

gem install sqlite3-ruby (not necessary on OS X Leopard)

development:
adapter: mysql
database: locktest
host: localhost
encoding: utf8
user: root
pool: 5
timeout: 5000
johnmudhead:locktest grant$ rake db:create db:migrate
(in /Users/grant/src/locktest)
== CreateFoos: migrating

– create_table(:foos)
-> 0.3696s
== CreateFoos: migrated (0.3698s)

johnmudhead:locktest grant$ script/console
Loading development environment (Rails 2.3.10)

f = Foo.create :bar => “AAAAAAAAAAAAAAA!”
=> #<Foo id: 1, bar: “AAAAAAAAAAAAAAA!”, created_at: “2010-11-01
19:50:58”, updated_at: “2010-11-01 19:50:58”>

f.save!
=> true

exit
johnmudhead:locktest grant$ script/console
Loading development environment (Rails 2.3.10)

f = Foo.find(1)
=> #<Foo id: 1, bar: “AAAAAAAAAAAAAAA!”, created_at: “2010-11-01
19:50:58”, updated_at: “2010-11-01 19:50:58”>

Foo.transaction do
?> f = Foo.find(1, :lock => true)

1.upto(10000000) { |i| puts i }
end
1
2
3
4
…

<<<<<< FROM TERMINAL TWO, WHILE TERMINAL ONE IS STILL COUNTING >>>>>>>>

johnmudhead:Pikimal grant$ cd …/locktest/
johnmudhead:locktest grant$ script/console
Loading development environment (Rails 2.3.10)

f = Foo.find(1)
=> #<Foo id: 1, bar: “AAAAAAAAAAAAAAA!”, created_at: “2010-11-01
19:50:58”, updated_at: “2010-11-01 19:50:58”>

2

Grant Olson wrote in post #958678:

Hopefully I’m doing something stupid here. I was trying to use
pessimistic locking on a rails 2.3.4 project and it just doesn’t seem to
work. The issue was showing up on both OSX and Linux.

I’ve created a simplified reproducible for 2.3.10 for discussion
purposes. I’ll publish the full transcript of the steps I took to
create the project later, but for now, I’ve got a single model Foo with
a single Bar value that’s a string. I create a single Foo and saved it.

In one console window I run:

Foo.transaction do
f = Foo.find(1, :lock => true)
1.upto(10000000) { |i| puts i }
end

I was under the assumption I wouldn’t be able to access that row at all
until I finished printing out 10 million numbers. But while that is
running, I can go to another console window and get to it:

johnmudhead:locktest grant$ script/console
Loading development environment (Rails 2.3.10)

f = Foo.find(1)
=> #<Foo id: 1, bar: “AAAAAAAAAAAAAAA!”, created_at: “2010-11-01
19:50:58”, updated_at: “2010-11-01 19:50:58”>

That seems like I’m reading the row when I should be locked out of it.

I have confirmed that I’m using the InnoDB backend on MySQL, which
supports row level locking.

Anyone have any advice or tips? Do I have a gross misunderstanding of
pessimistic locks in ruby.

I think you do. If I remember correctly, :lock doesn’t do any actual
DB-level locking – it just sets a lock field that ActiveRecord expects.
Transactions, OTOH, do do DB-level locking, but shouldn’t introduce
read locks for the sort of operations you’re doing. And you’re not
doing any DB writes here.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

3

On 11/2/10 4:17 PM, Marnen Laibow-Koser wrote:

I think you do. If I remember correctly, :lock doesn’t do any actual
DB-level locking – it just sets a lock field that ActiveRecord expects.
Transactions, OTOH, do do DB-level locking, but shouldn’t introduce
read locks for the sort of operations you’re doing. And you’re not
doing any DB writes here.

:lock is supposed to do row-level locking via the DB facilities:

I believe it’s the optimistic locking that uses the lock field in active
record, and throws a StaleObjectException if things have been modified.

Anyway, we discovered what the problem was. So for the sake of the
archives…

:lock won’t block on a standard SELECT. Only SELECT FOR UPDATE.

So if I run this code in terminal one:

Foo.transaction do
f = Foo.find(1, :lock => true)
1.upto(10000000) { |i| puts i }
end

And this in terminal two while terminal one is spitting out numbers:

Foo.transaction do
puts Foo.find(1, :lock => true).inspect
end

The second process will block, even for that read-only operation, until
the transaction in the first process ends.

–
Grant

“I am gravely disappointed. Again you have made me unleash my dogs of
war.”