Pessimistic locking doesn't seem to work with mysql?

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”>

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]

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:

http://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html

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.”

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs