2

I have been struggling with putting a record level lock on my table, to avoid any dirty reads and race condition while updating the record. I am using ruby 2.1.3 and rails version 3.2.13. I tried using the command:

Account.connection.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")

and tested by putting sleep, but 2 processes trying to update the same record read the old value and the final data is wrong. For example: if my old value is 100, both processes read 100 and then update, 100+x and 100+y and thus,the data is getting corrupted.

I am using mySQL database. Any help is appreciated.

dimakura
  • 7,575
  • 17
  • 36
codevj
  • 103
  • 1
  • 14

2 Answers2

2

There are two locking mechanisms supported in Rails: optimistic and pessimistic.

Updating account balance, using pessimistic locking, should look like this:

acc = Account.find(id)
acc.with_lock do
  acc.balance += 100
  acc.save!
end
dimakura
  • 7,575
  • 17
  • 36
  • 1
    Thanks. It worked for 1 case. Still working on other cases. Will update once that is done. – codevj Sep 18 '15 at 10:28
  • I also need to know that if the variable acc is blank (i.e. no active record present, will acc.with_lock raise some exception or will it execute the block? Can you help out in that? Thanks in advance! – codevj Sep 23 '15 at 11:12
  • If `acc` is `nil` (blank) it will raise `method not defined` exception. You need to check this before starting block. – dimakura Sep 23 '15 at 11:13
0

The following solution worked for me (slight modification of dimakura's answer):

  1. When I used find() for active record query:

    acc = Account.lock.find(id)
acc.balance += 100
acc.save!

  2. When I used where() for active record query:

    acc = Account.where(:acc_code => acc_code).first
acc.with_lock do
 acc.balance += 100
 acc.save!
end
codevj
  • 103
  • 1
  • 14