Release of mysql lock outside transaction (rails)

Question

We have been seeing a lot of errors recently:

ActiveRecord::TransactionIsolationConflict: Transaction isolation conflict detected: Lock wait timeout exceeded; try restarting transaction

Not able to figure out the reasoning behind it. But noticed one thing in our code which is trying to lock a record outside transaction:

acc = Account.lock.find acc_id

Above code is not inside any transaction and is used just to check that the other transaction which also obtains the same lock is finished or not. Any thoughts on if this can be the culprit ?

Answer 1

The InnoDB use row level locking for better concurrency when huge write load. Engine takes some precautions to get rid of phantom reads, one of these is gap_lock, which may be causing this problem. Use

SHOW ENGINE INNODB STATUS

to analyze about gap_lock.

If this is proble, you can try following options

1. Change the ISOLATION level to READ COMMITTED.

2. set innodb_locks_unsafe_for_binlog = 1. This will disables the gap locks except for foreign-key constraint checking or duplicate-key checking.

3. Use show innodb status again to analyse what is going on. If required optimise your code to avoid locking

4. If above does not work, try to increase lock_wait_timeout globally

SET GLOBAL innodb_lock_wait_timeout = 120;

Or for a session

SET innodb_lock_wait_timeout = 120;

5. Check your config again, if still not updated then set again global variables

   show variables like '%wait_timeout%';
   
   show variables like '%tx_isolation%';
   
   SELECT @@GLOBAL.tx_isolation, @@tx_isolation;