Geek Answers Handbook

Deadlocks while editing a closing tree hierarchy

How can I avoid database deadlocks by using closure_tree to simultaneously manage a set of models with common attributes in a hierarchical structure?

They are present in the following options:

With the release of #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 restoring a closing 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?

+4
source share
2 answers

Posted by closure_tree here:

d. Healing recommendations are usually correct - you should purchase table locks in the same order to prevent deadlocks. In this case, however, the deadlock is due to row locks in the hierarchy table.

Interestingly, you suggest using with_advisory_lock ! I just wrote this library for clos_tree, and if you are using version> = 3.7.0, there are already advisory locks protecting class methods #rebuild and #find_or_create_by_path at the class level.

The problem with advisory locks (at least with MySQL and PostgreSQL) is that they do not respect transaction boundaries - if the caller does not transfer its transaction before the lock is released, other connections will not see these when they try to get advisory lock, so we need to be careful. We may need to add a table lock to the hierarchy table for any records, but this will be in the worst case.

I opened question 41 , and we can track it there. The first thing to do is to reliably reproduce the dead end in the parallel test. We already have tests that do this for #rebuild and #find_or_create_by_path .

+3
source

You need to think about how one transaction works with another transaction. It is best to ensure that you read the material first (select) and then write SQL after that. Also make sure that write SQL uses the tables in the same order, that is, write to table A and then B in both cases. This will prevent the lock required by another transaction that requires locking by another transaction.

Alternatively, you can detect deadlocks and take appropriate action. I recommend avoiding them first.

+2
source

More articles:


All Articles