Setting Isolation level in ruby v 2.1.3

Question

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.

Answer 1

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