#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Unhappy SELECT FOR UPDATE doesn't block on Rails 3.2.11 with PostgreSQL 9.1


    I am trying to use pessimistic lock to avoid race condition. I was expecting the after one thread has acquired a row via
    Code:
    SELECT FOR UPDATE
    , another thread looking for the same row will be blocked until the lock is released. However, upon testing, it seems that the lock doesn't hold and the second thread can just acquire the row and update to it, even when the first thread haven't saved (updated) that row yet.

    Here is the relevant codes:

    Database Schema
    Code:
    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
    My Rails 3 code essentially generate the following SQL commands and the system.out print statements:

    Code:
    "waiting for lock"
      Mytables Load (4.6ms)  SELECT "mytables".* FROM "mytables" WHERE ("myID" = 1935701094) LIMIT 1 FOR UPDATE
    "acquired lock"
    (Rails thread sleeps for X sec)
    "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"
    All is well for single threaded server, but when I try to run a multi-threaded server, or connect to the same database table from two servers, and issue first command to update one row's attribute1 = 3004 with sleep value X = 15 and then 5 seconds later issue a second command to update the same row's attribute1 = 3003 with sleep value X = 1, then it seems to me that from the log, the second of command managed to get the lock of the row and update its value before the first command release the lock.

    The following is approximately how the logs looks like:

    Code:
    command 1 : "waiting for lock"
    command 1 :  Mytables Load (4.6ms)  SELECT "mytables".* FROM "mytables" WHERE ("myID" = 1935701094) LIMIT 1 FOR UPDATE
    command 1 : "acquired lock"
    command 1 sleeps for 15 sec, during which command 2 reached the server/database
    command 2: "waiting for lock"
    command 2 :  Mytables Load (4.6ms)  SELECT "mytables".* FROM "mytables" WHERE ("myID" = 1935701094) LIMIT 1 FOR UPDATE
    command 2 : "acquired lock"
    command 2 sleeps for 1 sec
    command 2: "woke up from sleep"
    command 2:    (0.3ms)  BEGIN
    command 2:    (1.5ms)  UPDATE "mytables" SET "attribute1" = 3303, "updated_at" = '2013-02-02 13:37:04.425577' WHERE "mytables"."id" = 40
    command 2:    (0.4ms)  COMMIT
    command 2: "done saving"
    command 1: "woke up from sleep"
    command 1:    (0.3ms)  BEGIN
    command 1:    (1.5ms)  UPDATE "mytables" SET "attribute1" = 3304, "updated_at" = '2013-02-02 13:38:14.878577' WHERE "mytables"."id" = 40
    command 1:    (0.4ms)  COMMIT
    command 1: "done saving"

    I was expecting command 2 would be blocked on this line:
    command 2 : Mytables Load (4.6ms) SELECT "mytables".* FROM "mytables" WHERE ("myID" = 1935701094) LIMIT 1 FOR UPDATE
    and won't be able to continue until this line:
    command 1: (0.4ms) COMMIT


    I also tried to use SELECT FOR UPDATE NOWAIT, but the behaviour is the same...

    I am totally baffled...
    Have I misunderstood of the LOCK in PostgreSQL???
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    I don't know Ruby, but for your strategy to work you need to turn autocommit off. Otherwise each statement is automatically committed and thus the locks are released.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by shammat
    I don't know Ruby, but for your strategy to work you need to turn autocommit off. Otherwise each statement is automatically committed and thus the locks are released.
    em... after command 1 (thread 1) executed the command:
    Code:
    Mytables Load (4.6ms)  SELECT "mytables".* FROM "mytables" WHERE ("myID" = 1935701094) LIMIT 1 FOR UPDATE
    it waits for 15 seconds before issuing the rest of the block

    Code:
       (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
    So I think autocommit is not an issue here?

    Am I correct to think that during that 15 seconds, thread 1 should be holding the lock of that row?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by DeityUK
    So I think autocommit is not an issue here?
    Of course it is. A LOCK is released as soon as you commit. With autocommit this happens when the statement has finished.

    Am I correct to think that during that 15 seconds, thread 1 should be holding the lock of that row?
    Not if autocommit is enabled.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Smile


    Thanks Shammat!
    I thought the Rails activerecord command I used is going to open a transaction for me, but evidentially it doesn't. Now my new code produces SQL code that has BEGIN... COMMIT wrapped around the SELECT FOR UPDATE too and the lock works as expected.

    Originally Posted by shammat
    Of course it is. A LOCK is released as soon as you commit. With autocommit this happens when the statement has finished.

    Not if autocommit is enabled.

IMN logo majestic logo threadwatch logo seochat tools logo