Handling MySQL Deadlocks with Counter Cache

Rails counter_cache is quite helpful to find associated objects count in the belonging object efficiently.

But, it leads to Deadlocks while creating lots of associated records concurrently especially with MySQL. Unfortunately, deadlocks happen even for small numbers like 300–500 records.

As mentioned in this MySQL bug report this happens due to the Foreign Key Constraint, when an insert is executed on the associated table, a shared lock is requested on belonging table that leads to deadlocks when large number of inserts are triggered

How do we solve this? There are 2 approaches

1) Replace ActiveRecord counter_cache callback with our own. This makes the counter statement to get executed before insert without deadlocks

class User < ApplicationRecord
  belongs_to :company
  before_create do
    company.increment!(:users_count)
  end
  after_destroy do
    company.decrement!(:users_count)
  end  
end

2) Migrate to PostgreSQL. It is solved by introducing Lock Type and detects deadlocks automatically and aborts one of the transaction

For large projects 1st approach seems lot more feasible as migrating to a new database isn’t an easy choice and takes huge effort.