Patch #30465

Deadlock when assigning custom values

Added by Pavel Rosický 9 months ago. Updated 9 months ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Performance
Target version:4.0.1

Description

There's been some changes in the new Rails version. It looks like deleting records from association is now scope aware.

After upgrading to Redmine 4.0, I've encountered several deadlocks under heavy load. Note that my DB has 30M+ records in custom_values.

ActiveRecord::Deadlocked: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
 DELETE FROM `custom_values` WHERE `custom_values`.`id` IN (SELECT `id` FROM
 (SELECT `custom_values`.`id` FROM `custom_values` LEFT OUTER JOIN `custom_fields` ON `custom_fields`.`id` = `custom_values`.`custom_field_id`
 WHERE `custom_values`.`customized_id` = 134661 AND
 `custom_values`.`customized_type` = 'Issue' AND `custom_values`.`id` IN
 (34100955, 34100961, 34100989, 34100990, 34100992) ORDER BY custom_fields.position) __active_record_temp)

the association looks like this

has_many :custom_values, lambda {includes(:custom_field).order("#{CustomField.table_name}.position")},
         :as => :customized,
         :inverse_of => :customized,
         :dependent => :delete_all,
         :validate => false

and it's populated at
https://github.com/redmine/redmine/blob/f5daae4041daa5ad6385f7d4cb43655c87515153/lib/plugins/acts_as_customizable/lib/acts_as_customizable.rb#L145

That's pretty nasty Rails magic. If you take a closer look at the query, it's clear that both includes(:custom_field) and order aren't necessary for deleting records (at least in this case).

the problem is that DELETE command locks the database and creating / updating a record like an issue is wrapped in a transaction.
https://github.com/redmine/redmine/blob/f5daae4041daa5ad6385f7d4cb43655c87515153/app/controllers/issues_controller.rb#L552

Ordering records in a subquery causes that many rows have to be locked in order to execute the query.
In my case this query took ~30s to execute under very heavy load. That's bad.

If duration of the whole transaction exceeded innodb_lock_wait_timeout (default value is 50), it fails as 500 internal server error.

After removing the order statement, the same query with the same conditions took ~0.1s and deadlocks were gone.

IMO, order and maybe even include(:custom_field) shouldn't be part of the association, you should always use an additional scope like #sorted if you want to get a sorted result.

The patch is simple and fixes the problem, but I have no idea how to write a test for it. It may also behave differently on other databases and even on mysql it's quite hard to replicate all conditions.

I tested it on

  Server version: 5.7.20-19-log Percona Server (GPL), Release '19'

please consider refactoring this association. Thanks!

acts_as_customizable.rb.patch Magnifier (770 Bytes) Pavel Rosický, 2019-01-18 19:50

Associated revisions

Revision 17823
Added by Jean-Philippe Lang 9 months ago

Deadlock when assigning custom values (#30465).

Patch by Pavel Rosický.

Revision 17824
Added by Jean-Philippe Lang 9 months ago

Merged r17823 to 4.0-stable (#30465).

History

#1 Updated by Go MAEDA 9 months ago

  • Target version set to 4.0.1

#2 Updated by Jean-Philippe Lang 9 months ago

  • Status changed from New to Closed
  • Assignee set to Jean-Philippe Lang

Committed, thanks.

Also available in: Atom PDF