Deadlocks when concurrent editing a closure tree hierarchy

Question

How can I avoid database deadlocks when using closure_tree to concurrently manipulate a set of models with common attributes on a hierarchical structure?

They present in the following flavors:

When issuing an #append/prepend_sibling

Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
  UPDATE `elements` SET `sort_order` = `sort_order` + 1 WHERE (`parent_id` = 28035 AND `sort_order` >= 1)

Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
  UPDATE `elements` SET `sort_order` = `sort_order` - 1 WHERE (`parent_id` = 21168 AND `sort_order` <= -1)

When rebuilding the closure table

Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
  DELETE FROM `element_hierarchies`
   WHERE descendant_id IN (
     SELECT DISTINCT descendant_id
     FROM ( SELECT descendant_id
       FROM `element_hierarchies`
       WHERE ancestor_id = 16332
     ) AS x )
     OR descendant_id = 16332

Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: 
  INSERT INTO `element_hierarchies` (`ancestor_id`, `descendant_id`, `generations`) VALUES (30910, 30910, 0)

with_advisory_lock looks promising. Any thoughts?

score 3 · Accepted Answer · answered Feb 10 '13 at 18:09

Author of closure_tree here:

Mr. Heal's advice is generally correct—you should acquire table locks in the same order to prevent deadlocks. In this case, though, the deadlock is due to row-level locks in the hierarchy table.

Interesting that you suggest using with_advisory_lock! I just wrote that library for closure_tree, and if you're using version >= 3.7.0, there are already advisory locks safeguarding the class-level #rebuild and #find_or_create_by_path methods.

The problem with advisory locks (at least with MySQL and PostgreSQL) is that they don't respect transaction boundaries—if the lock-holding caller doesn't commit their transaction before the lock is released, other connections will not see those changes when they try to acquire the advisory lock, so we need to be careful here. We may need to add a table lock on the hierarchy table for any writes, but that would be worst-case.

I've opened issue 41, and we can track it there. First thing to do is to reliably reproduce the deadlock in a parallel test. We already have tests that do that for #rebuild and #find_or_create_by_path.

Could you please take a look at my question at http://stackoverflow.com/questions/41388300/use-ruby-closure-tree-gem-without-rails ? — peter, Dec 29 '16 at 22:56

score 2 · Answer 2 · answered Feb 07 '13 at 15:19

You need to consider how one transaction works with another transaction. You best bet is to ensure that you do the read stuff first (select) and then write SQL afterwards. Also ensure that the write SQL use the tables in the same order - i.e. write to table A then B in both cases. This will prevent a lock being required whilst held by another transaction that requires a lock by the other transaction.

Alternatively you could detect deadlocks and take appropriate action. I recommend to avoid them in the first place.

Deadlocks when concurrent editing a closure tree hierarchy

2 Answers2