Why does activerecord optimistic locking work only once per row?

Question

Somehow, I always get these on Fridays.

My earlier question was regarding the same problem, but I can now narrow things down a bit:

I've been playing with this all day, trying to make sense of it. I have a table with a lock_version colum, specified thus:

add_column :jobs, :lock_version, :integer, :default=>0

And I do something like this:

foo = job.create!
first = Job.find(foo.id)
second = Job.find(foo.id)

I then verify that first and second refer to the same object - their ids are the same and I see that row in the database using the mysql command-line tool.

first.some_attribute_field = 'first'
second.some_attribute_field = 'second'
first.save
second.save

no problem so far. I correctly get an ActiveRecord::StaleObjectError exception. HOWEVER:

first = Job.find(foo.id)
second = Job.find(foo.id)
first.some_attribute_field = 'first'
second.some_attribute_field = 'second'
first.save
second.save

...and nothing happens. It turns out that the only time I get correct (thrown exception) behavior is when first and second have a lock_version of 0. After the first save, though, it is NEVER 0 again. What on earth is up with this?

I'm using ruby 1.8.6 and active record 2.2.2

Thanks...

Answer 1

I'm not a Ruby guy, but optimistic locking is familiar for me, so I'll try to help you to debug it.

I presume the second save actually updates the database. If both objects have different lock_version AND lock_version is used in UPDATE, it's simply not possible (UPDATE would update zero rows). So, we have only two alternatives:

• lock_version is not used in UPDATE statement or used incorrectly
• both objects got the same lock_version somehow

(actually, there is a third alternative: both save() are in their own transaction, but I feel you have AUTOCOMMIT=true)

Can you make actual SQL statements visible? The update statement should read something like

... WHERE JOB_ID=123 AND LOCK_VERSION=8

When you'll have the actually queries at hand, it would be much easier to make sense of what's going on.

P.S. and one more: in your example in another topic you have this object:

#<Job id: 323, lock: 8, worker_host: "second">

The save() call may be ignored by container if the properties are not changed comparing to load time. I don't know if ActiveRecord has this optimization or not though. But if it does, then second save() is ignored, and optimistic locking doesn't have a chance to kick in.

Answer 2

when you call first.save the second time the value of some_attribute_field is already equal to "first", activerecord knows this so it doesn't update in the db to lock_version doesn't get incremented. The second save works as the db was never changed with the "first".

Try changing the value in the second test to something other than "first" so that it is different to what is in the db.

Answer 3

As Vladimir said, your test/example code is a bit flawed. First doesn't get stored in the datebase during the second save!() because no attributes have changed. See the following example:

foo = Account.create!

first = Account.find(foo.id)
first.cash = 100
first.save!


first = Account.find(foo.id)
first.cash = 100

puts "First lock before " + first.lock_version.to_s
first.save!
puts "First lock after " + first.lock_version.to_s

this produces:

% script/runner another_tester.rb                               
First lock before 1
First lock after 1

Using your example in the 2.3.2 version of rails works as it should with a Stale object exception when second is saved (both times!)