Dear team,

When we have multiple sessions try to delete issues in same times, we encounter this internal error:

ActiveRecord::StatementInvalid (Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: SELECT issues.* FROM issues WHERE issues.root_id = 145786 AND (issues.lft >= 1 AND issues.rgt <= 2) AND (issues.id != 145786) ORDER BY issues.lft):

The trace:

Started DELETE "/issues/17669" for 127.0.0.1 at 2014-01-23 16:34:09 +0700
Processing by IssuesController#destroy as HTML
Parameters: {"authenticity_token"=>"tlayCB7PIEzTrauenLr7g4HoeWkjz6S43YG6tZBRQE0=", "id"=>"17669"}
(3.0ms) SELECT MAX(settings.updated_on) AS max_id FROM settings
User Load (3.0ms) SELECT users.* FROM users WHERE users.type IN ('User', 'AnonymousUser') AND users.status = 1 AND users.id = 1454 LIMIT 1
Current user: ttanthanh (id=1454)
Issue Load (3.0ms) SELECT issues.* FROM issues WHERE issues.id = 17669
Project Load (2.0ms) SELECT projects.* FROM projects WHERE projects.id = 10 LIMIT 1
EnabledModule Load (3.0ms) SELECT name FROM enabled_modules WHERE enabled_modules.project_id = 10
(2.0ms) SELECT SUM(time_entries.hours) AS sum_id FROM time_entries WHERE (issue_id IN (17669))
Issue Load (4.0ms) SELECT issues.* FROM issues WHERE issues.id = 17669 LIMIT 1
(2.0ms) BEGIN
Journal Load (3.0ms) SELECT journals.* FROM journals WHERE journals.journalized_id = 17669 AND journals.journalized_type = 'Issue'
SQL (2.0ms) DELETE FROM journal_details WHERE journal_details.journal_id = 343792
SQL (3.0ms) DELETE FROM journals WHERE journals.id = 343792
SQL (2.0ms) DELETE FROM time_entries WHERE time_entries.issue_id = 17669
SQL (2.0ms) DELETE FROM issue_relations WHERE issue_relations.issue_from_id = 17669
SQL (3.0ms) DELETE FROM issue_relations WHERE issue_relations.issue_to_id = 17669
Issue Load (2.0ms) SELECT lft, rgt, parent_id FROM issues WHERE issues.id = 17669 LIMIT 1 FOR UPDATE
Issue Load (725.0ms) SELECT id FROM issues WHERE (lft >= 1) FOR UPDATE
Issue Load (3.0ms) SELECT issues.* FROM issues WHERE issues.root_id = 17669 AND (issues.lft >= 1 AND issues.rgt <= 2) AND (issues.id != 17669) ORDER BY issues.lft
SQL (3.0ms) UPDATE issues SET lft = (lft - 2) WHERE issues.root_id = 17669 AND (lft > 2) ORDER BY issues.lft
SQL (2.0ms) UPDATE issues SET rgt = (rgt - 2) WHERE issues.root_id = 17669 AND (rgt > 2) ORDER BY issues.lft
Issue Load (4.0ms) SELECT issues.* FROM issues WHERE issues.id = 17669 LIMIT 1
Attachment Load (3.0ms) SELECT attachments.* FROM attachments WHERE attachments.container_id = 17669 AND attachments.container_type = 'Issue' ORDER BY attachments.created_on ASC, attachments.id ASC
SQL (5.0ms) DELETE FROM custom_values WHERE custom_values.customized_id = 17669 AND custom_values.customized_type = 'Issue'
SQL (2.0ms) DELETE FROM watchers WHERE watchers.watchable_id = 17669 AND watchers.watchable_type = 'Issue'
Changeset Load (10.0ms) SELECT changesets.* FROM changesets INNER JOIN changesets_issues ON changesets.id = changesets_issues.changeset_id WHERE changesets_issues.issue_id = 17669 ORDER BY changesets.committed_on ASC, changesets.id ASC
SQL (3.0ms) DELETE FROM issues WHERE (issues.id = 17669 AND issues.lock_version = 1)
(9.0ms) COMMIT
Redirected to http://localhost:3001/projects/demoproject/issues
Completed 302 Found in 36813ms (ActiveRecord: 808.0ms)

We have two lines may be cause deadlock (I guess that)

Issue Load (725.0ms) SELECT id FROM issues WHERE (lft >= 1) FOR UPDATE
This query lock table and cost too much time.

Issue Load (3.0ms) SELECT issues.* FROM issues WHERE issues.root_id = 17669 AND (issues.lft >= 1 AND issues.rgt <= 2) AND (issues.id != 17669) ORDER BY issues.lft
And this query always return empty result.

Thanks for your support.