How to resolve? ActiveRecord::TransactionIsolationConflict

Question

I'm getting nothing but ActiveRecord::TransactionIsolationConflict errors when I try to update records for one of my models. Retrying does not help.

What should I do?

Rails 3.2.13
Ruby 1.9.3

Answer 1

Option A: Hunt down locks on table

In my case, there were a number of orphaned processes which held locks on the table in question. Somehow, the application that initiated them dropped the connections, but the locks remained. The following instructions are drawn from Unlocking tables if thread is lost

Check locks:

mysql> show open tables where in_use > 0;

If you have no idea of what session or process locks the table(s), view the list of processes, and identify likely candidates by their username or the database which they're accessing:

mysql> show processlist;

Kill processes which you know or suspect to have locks on the table:

mysql> kill <process id>;

Option B: Increase timeout

A popular suggestion is to increase the innodb_lock_wait_timeout. The following instructions are drawn from How to debug Lock wait timeout exceeded on MySQL?

Check your timeout:

mysql> show variables like 'innodb_lock_wait_timeout';

Change timeout dynamically (not persistent):

mysql> SET GLOBAL innodb_lock_wait_timeout = 120; 

Change timeout in config file (persistent):

[mysqld]
innodb_lock_wait_timeout=120