Checking if activerecord is locked in "updating" status

Question

Let's say I have following entities and relationships:

A<-B<-C<-D<-E

I have many E for the same A (E belongs to A through D, C and B)

I have a code snippet that looks like this:

eList=E.all
varA=eList.first.D.C.B.A

E.transaction do
  ...Operations that take some time...

  varA.update_attributes(::last_update=>Time.now)
end

Now think that this snippet runs with threads. One thread per E entity. That means that if I have 10 E for one A, 10 threads will run at the same time this snippet.

My problems comes with the line

varA.update_attributes(:last_update=>Time.now)

When I look at my code, I see that I have this:

[DEBUG] 2013/02/13 13:09:51 [66222] - abstract_adapter.rb:198 -   A Update (3107.1ms)   UPDATE "A" SET "updated_at" = '2013-02-13 13:09:47.932899', "last_update" = '2013-02-13 13:09:47.932209' WHERE "id" = 144
[DEBUG] 2013/02/13 13:09:54 [48600] - abstract_adapter.rb:198 -   A Update (6812.2ms)   UPDATE "A" SET "updated_at" = '2013-02-13 13:09:47.218032', "last_update" = '2013-02-13 13:09:47.217421' WHERE "id" = 144
[DEBUG] 2013/02/13 13:09:56 [66190] - abstract_adapter.rb:198 -   A Update (6717.5ms)   UPDATE "A" SET "updated_at" = '2013-02-13 13:09:49.328496', "last_update" = '2013-02-13 13:09:49.327777' WHERE "id" = 144
[DEBUG] 2013/02/13 13:09:59 [66219] - abstract_adapter.rb:198 -   A Update (10816.2ms)   UPDATE "A" SET "updated_at" = '2013-02-13 13:09:48.236539', "last_update" = '2013-02-13 13:09:48.235895' WHERE "id" = 144
[DEBUG] 2013/02/13 13:10:01 [66200] - abstract_adapter.rb:198 -   A Update (13450.9ms)   UPDATE "A" SET "updated_at" = '2013-02-13 13:09:47.584182', "last_update" = '2013-02-13 13:09:47.583467' WHERE "id" = 144

As you can see, the time to run each query increases for each thread. The first one takes 3 seconds while the last one takes 13. I assume that this is because I am updating the same record (A.id=144) so the record is locked and the other threads need to wait.

My question is, is there a way in Rails 2.3 that I can detect if a record is locked because it is being updated so I can just make the other threads skip the update?

Something like:

if not varA.locked then varA.update_attributes(:last_update=>Time.now) end

It is good enough for me to update it just once so I want the others threads to check if it is already updating and move on.

FWIW, my DB is postgres 9.0

Answer 1

Checking whether a row is locked for update is a relatively complex procedure in PostgreSQL and as far as I can tell only doable in procedural languages that can trap sql exceptions. I would be very surprised if ActiveRecord could do this.

If you need to do this you will probably need to write stored procedures which trap these exceptions and drop to SQL.