Home Optimizing delete operations in Rails 4 / ActiveRecord / MySQL
Reply: 0

Optimizing delete operations in Rails 4 / ActiveRecord / MySQL

user2547
1#
user2547 Published in May 21, 2018, 10:48 pm

Essentially, we need to delete T1 records that have not had a t3 record associated with it for given @user. Though not required, it would be nice to delete T2 records as well that do not have a T3 join.

This was the code that got pushed to production. Obviously, it's great, because it passed a unit test(ha!)...except it's causing a lock across millions of rows in production, resulting in a Server 500 deadlock (Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction) when multiple users hit the DELETE query at the same time. Yes, indexes are in place:

T1.where(user_id: @user.id, enabled: true)
    .joins('LEFT JOIN t2 ON t2.t1_id = t1.id')
    .joins('LEFT JOIN t3 ON t3.id = t2.t3_id')
    .where('t3.id IS NULL').delete_all

Resulting SQL:

DELETE FROM `t1`
WHERE `t1`.`id` IN
(SELECT id FROM
     (SELECT `t1`.`id` FROM `t1`
      LEFT JOIN t2 ON t2.t1_id = t1.id
      LEFT JOIN t3 ON t3.id = t2.t3_id
      WHERE `t1`.`user_id` = 65987
      AND `t1`.`enabled` = 1
      AND (t2.id IS NULL)
     ) __active_record_temp
    );

The only reason I know that is the resulting SQL here, is due it being included with the Server 500 deadlock error. I can't seem to get delete_all queries to show in the console while testing. I was able to take the query output and convert it to a SELECT with explain, which showed the outermost select scanning millions of rows (which I believe translates to the same number of row locks for DELETE operation.) The innermost query scans only 27 rows.

Questions:

  1. What is the optimal approach to deleting records from one or multiple tables based on join values in Rails with ActiveRecord?
  2. What options do we have to review/test the SQL output in Rails to ensure I'm not at risk of poor performance & deadlock?

UPDATE: the plot thickens...adding current associations

class User < ActiveRecord::Base
has_many :T1s
has_many :T2s

class T1 < ActiveRecord::Base
belongs_to :user

class T2Custom < ActiveRecord::Base
self.table_name = "t2"
has_many :T3s, :foreign_key => :t2_id

class T3 < ActiveRecord::Base
belongs_to :T2, foreign_key: "t2_id"
belongs_to :T1
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.320459 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO