thr3ads.net - Rails - Pessimistic locking doesn''t seem to work with mysql? [Nov 2010]

If this information is useful, please help other people find it:
Share via:

Grant Olson

2010-Nov-01 20:08 UTC

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>> exitjohnmudhead: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 }
>> end1
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">>>

Marnen Laibow-Koser

2010-Nov-02 20:17 UTC

head link

Re: Pessimistic locking doesn''t seem to work with mysql?

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Grant Olson

2010-Nov-02 20:47 UTC

head link

Re: Re: Pessimistic locking doesn''t seem to work with mysql?

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

Rails - Nov 2010 - Pessimistic locking doesn't seem to work with mysql?

Pessimistic locking doesn''t seem to work with mysql?

Re: Pessimistic locking doesn''t seem to work with mysql?

Re: Re: Pessimistic locking doesn''t seem to work with mysql?