SELECT FOR UPDATE does not block Rails 3.2.11 since PostgreSQL 9.1

I am trying to use a pessimistic lock to avoid a race condition. I expected that after one thread acquired a row using SELECT FOR UPDATE

, another thread looking for the same row would be blocked until the lock was released. However, upon testing, it seems that the lock is not being executed and the second thread can simply get the row and update it, even if the first thread has not yet saved (updated) that row.

Here are the relevant codes:

Database schema

class CreateMytables < ActiveRecord::Migration
  def change
    create_table :mytables do |t|
        t.integer  :myID
        t.integer  :attribute1
        t.timestamps
    end

    add_index :mytables, :myID, :unique => true

  end
end

      

        
        
        
      

    

mytables_controller.rb

class MytablessController < ApplicationController

    require 'timeout'

    def create
        myID = Integer(params[:myID])
        begin
            mytable = nil
            Timeout.timeout(25) do 
                p "waiting for lock"              
                mytable = Mytables.find(:first, :conditions => ['"myID" = ?', myID], :lock => true ) #'FOR UPDATE NOWAIT') #true) 
                #mytable.lock!
                p "acquired lock"                 
            end
            if mytable.nil?
                mytable = Mytables.new
                mytable.myID =  myID
            else
                if mytable.attribute1 > Integer(params[:attribute1])
                    respond_to do |format|
                        format.json{
                            render :json => "{\"Error\": \"Update failed, a higher attribute1 value already exist!\", 
\"Error Code\": \"C\"
}"
                            }
                    end
                    return
                end
            end
            mytable.attribute1 =  Integer(params[:attribute1])           
            sleep 15  #1 
            p "woke up from sleep"
            mytable.save! 
            p "done saving"             
            respond_to do |format|
                format.json{
                          render :json => "{\"Success\": \"Update successful!\",
\"Error Code\": \"A\"
}"
                            }
            end
        rescue ActiveRecord::RecordNotUnique #=> e     
            respond_to do |format|
                format.json{
                            render :json => "{\"Error\": \"Update Contention, please retry in a moment!\",
\"Error Code\": \"B\"
}"
                            }
            end
        rescue Timeout::Error
            p "Time out error!!!"
            respond_to do |format|
                format.json{
                            render :json => "{\"Error\": \"Update Contention, please retry in a moment!\",
\"Error Code\": \"B\"
}"
                            }
            end
        end   
    end
end

      

        
        
        
      

    

I tested it in two settings, one runs the unicorn app using worker_processes 4

on Heroku, the other runs locally on my machine using PostgreSQL 9.1, running two instances of the same app thread, one of them rails server -p 3001

, the other - thin start

(for some reason, if I just run only rails server

or thin start

, they will only handle incoming calls sequentially).

Setting 1: The initial value of attribute1 in the database for myID of interest is 3302. I made one update call to the Heroku app (to update attribute1 to 3303) and then wait for about 5 seconds and run another one to the Heroku app (to update attribute1 to values ​​3304). I expected the second call to take about 25 seconds, because the first one took 15 seconds to complete due to the command sleep 15

entered in the code before mytable.save!

, and the second call must be blocked on the line mytable = Mytables.find(:first, :conditions => ['"myID" = ?', myID], :lock => true )

for about 10 seconds before it acquired lock and then slept for 15 seconds. But it turns out that the second call ended just 5 seconds later than the first call.

And if I reverse the order of the request, that is, the first call is to update attribute 1 to 3304, and the second second delayed second call is to update attribute 1 to 3303, the final value is 3303. Looking at the log on Heroku, the second call was waiting for no time. to acquire the lock while, in theory, the first call was asleep and thus held the lock.

Setup 2: Running two Thin rails servers of the same application, one on port 3000 and one on port 3001. I understand they are connected to the same database, so if one server instance got a lock through SELECT FOR UPDATE

, the other instance should fail to acquire the lock and will be blocked. However, the blocking behavior is the same as that of Heroku (does not work as I expected). And since the servers are running locally, I was able to do an additional test of setup, so that when the first call slept for 15 seconds, I changed the code before starting the second call so that the second call of the second second would only sleep 1 second after acquiring the lock , and the second call ended much earlier than the first call ...

I also tried to use SELECT FOR UPDATE NOWAIT

and entered an extra line mytable.lock!

right after the line SELECT FOR UPDATE

, but the results are the same.

So, it seems to me that although the command SELECT FOR UPDATE

was successfully posted to the PostgreSQL table, another thread / process can still be SELECT FOR UPDATE

the same row and even UPDATE

the same row without blocking at all ...

I am totally puzzled and any suggestion would be welcome. Thank!

PS1. The reason for using blocking on a row is because my code needs to make sure that only calls to update the row to a higher value for attribute1 will succeed.

PS2 Sample SQL Output from Local Log

"waiting for lock"
  Mytables Load (4.6ms)  SELECT "mytables".* FROM "mytables" WHERE ("myID" = 1935701094) LIMIT 1 FOR UPDATE
"acquired lock"
"woke up from sleep"
   (0.3ms)  BEGIN
   (1.5ms)  UPDATE "mytables" SET "attribute1" = 3304, "updated_at" = '2013-02-02 13:37:04.425577' WHERE "mytables"."id" = 40
   (0.4ms)  COMMIT
"done saving"