##// END OF EJS Templates
jro_apps » redmine
RSS
#lock_nested_set very slow on mysql with thousands of subtasks (#23318)....
Jean-Philippe Lang -
r15671:10b3e3e32394
parent child
Show More
Side by Side Unified
Context file:
r15671:10b3e3e32394 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,200 +1,201
1 # Redmine - project management software
1 # Redmine - project management software
2 # Copyright (C) 2006-2016 Jean-Philippe Lang
2 # Copyright (C) 2006-2016 Jean-Philippe Lang
3 #
3 #
4 # This program is free software; you can redistribute it and/or
4 # This program is free software; you can redistribute it and/or
5 # modify it under the terms of the GNU General Public License
5 # modify it under the terms of the GNU General Public License
6 # as published by the Free Software Foundation; either version 2
6 # as published by the Free Software Foundation; either version 2
7 # of the License, or (at your option) any later version.
7 # of the License, or (at your option) any later version.
8 #
8 #
9 # This program is distributed in the hope that it will be useful,
9 # This program is distributed in the hope that it will be useful,
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 # GNU General Public License for more details.
12 # GNU General Public License for more details.
13 #
13 #
14 # You should have received a copy of the GNU General Public License
14 # You should have received a copy of the GNU General Public License
15 # along with this program; if not, write to the Free Software
15 # along with this program; if not, write to the Free Software
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17
17
18 module Redmine
18 module Redmine
19 module NestedSet
19 module NestedSet
20 module IssueNestedSet
20 module IssueNestedSet
21 def self.included(base)
21 def self.included(base)
22 base.class_eval do
22 base.class_eval do
23 belongs_to :parent, :class_name => self.name
23 belongs_to :parent, :class_name => self.name
24
24
25 before_create :add_to_nested_set, :if => lambda {|issue| issue.parent.present?}
25 before_create :add_to_nested_set, :if => lambda {|issue| issue.parent.present?}
26 after_create :add_as_root, :if => lambda {|issue| issue.parent.blank?}
26 after_create :add_as_root, :if => lambda {|issue| issue.parent.blank?}
27 before_update :handle_parent_change, :if => lambda {|issue| issue.parent_id_changed?}
27 before_update :handle_parent_change, :if => lambda {|issue| issue.parent_id_changed?}
28 before_destroy :destroy_children
28 before_destroy :destroy_children
29 end
29 end
30 base.extend ClassMethods
30 base.extend ClassMethods
31 base.send :include, Redmine::NestedSet::Traversing
31 base.send :include, Redmine::NestedSet::Traversing
32 end
32 end
33
33
34 private
34 private
35
35
36 def target_lft
36 def target_lft
37 scope_for_max_rgt = self.class.where(:root_id => root_id).where(:parent_id => parent_id)
37 scope_for_max_rgt = self.class.where(:root_id => root_id).where(:parent_id => parent_id)
38 if id
38 if id
39 scope_for_max_rgt = scope_for_max_rgt.where("id < ?", id)
39 scope_for_max_rgt = scope_for_max_rgt.where("id < ?", id)
40 end
40 end
41 max_rgt = scope_for_max_rgt.maximum(:rgt)
41 max_rgt = scope_for_max_rgt.maximum(:rgt)
42 if max_rgt
42 if max_rgt
43 max_rgt + 1
43 max_rgt + 1
44 elsif parent
44 elsif parent
45 parent.lft + 1
45 parent.lft + 1
46 else
46 else
47 1
47 1
48 end
48 end
49 end
49 end
50
50
51 def add_to_nested_set(lock=true)
51 def add_to_nested_set(lock=true)
52 lock_nested_set if lock
52 lock_nested_set if lock
53 parent.send :reload_nested_set_values
53 parent.send :reload_nested_set_values
54 self.root_id = parent.root_id
54 self.root_id = parent.root_id
55 self.lft = target_lft
55 self.lft = target_lft
56 self.rgt = lft + 1
56 self.rgt = lft + 1
57 self.class.where(:root_id => root_id).where("lft >= ? OR rgt >= ?", lft, lft).update_all([
57 self.class.where(:root_id => root_id).where("lft >= ? OR rgt >= ?", lft, lft).update_all([
58 "lft = CASE WHEN lft >= :lft THEN lft + 2 ELSE lft END, " +
58 "lft = CASE WHEN lft >= :lft THEN lft + 2 ELSE lft END, " +
59 "rgt = CASE WHEN rgt >= :lft THEN rgt + 2 ELSE rgt END",
59 "rgt = CASE WHEN rgt >= :lft THEN rgt + 2 ELSE rgt END",
60 {:lft => lft}
60 {:lft => lft}
61 ])
61 ])
62 end
62 end
63
63
64 def add_as_root
64 def add_as_root
65 self.root_id = id
65 self.root_id = id
66 self.lft = 1
66 self.lft = 1
67 self.rgt = 2
67 self.rgt = 2
68 self.class.where(:id => id).update_all(:root_id => root_id, :lft => lft, :rgt => rgt)
68 self.class.where(:id => id).update_all(:root_id => root_id, :lft => lft, :rgt => rgt)
69 end
69 end
70
70
71 def handle_parent_change
71 def handle_parent_change
72 lock_nested_set
72 lock_nested_set
73 reload_nested_set_values
73 reload_nested_set_values
74 if parent_id_was
74 if parent_id_was
75 remove_from_nested_set
75 remove_from_nested_set
76 end
76 end
77 if parent
77 if parent
78 move_to_nested_set
78 move_to_nested_set
79 end
79 end
80 reload_nested_set_values
80 reload_nested_set_values
81 end
81 end
82
82
83 def move_to_nested_set
83 def move_to_nested_set
84 if parent
84 if parent
85 previous_root_id = root_id
85 previous_root_id = root_id
86 self.root_id = parent.root_id
86 self.root_id = parent.root_id
87
87
88 lft_after_move = target_lft
88 lft_after_move = target_lft
89 self.class.where(:root_id => parent.root_id).update_all([
89 self.class.where(:root_id => parent.root_id).update_all([
90 "lft = CASE WHEN lft >= :lft THEN lft + :shift ELSE lft END, " +
90 "lft = CASE WHEN lft >= :lft THEN lft + :shift ELSE lft END, " +
91 "rgt = CASE WHEN rgt >= :lft THEN rgt + :shift ELSE rgt END",
91 "rgt = CASE WHEN rgt >= :lft THEN rgt + :shift ELSE rgt END",
92 {:lft => lft_after_move, :shift => (rgt - lft + 1)}
92 {:lft => lft_after_move, :shift => (rgt - lft + 1)}
93 ])
93 ])
94
94
95 self.class.where(:root_id => previous_root_id).update_all([
95 self.class.where(:root_id => previous_root_id).update_all([
96 "root_id = :root_id, lft = lft + :shift, rgt = rgt + :shift",
96 "root_id = :root_id, lft = lft + :shift, rgt = rgt + :shift",
97 {:root_id => parent.root_id, :shift => lft_after_move - lft}
97 {:root_id => parent.root_id, :shift => lft_after_move - lft}
98 ])
98 ])
99
99
100 self.lft, self.rgt = lft_after_move, (rgt - lft + lft_after_move)
100 self.lft, self.rgt = lft_after_move, (rgt - lft + lft_after_move)
101 parent.send :reload_nested_set_values
101 parent.send :reload_nested_set_values
102 end
102 end
103 end
103 end
104
104
105 def remove_from_nested_set
105 def remove_from_nested_set
106 self.class.where(:root_id => root_id).where("lft >= ? AND rgt <= ?", lft, rgt).
106 self.class.where(:root_id => root_id).where("lft >= ? AND rgt <= ?", lft, rgt).
107 update_all(["root_id = :id, lft = lft - :shift, rgt = rgt - :shift", {:id => id, :shift => lft - 1}])
107 update_all(["root_id = :id, lft = lft - :shift, rgt = rgt - :shift", {:id => id, :shift => lft - 1}])
108
108
109 self.class.where(:root_id => root_id).update_all([
109 self.class.where(:root_id => root_id).update_all([
110 "lft = CASE WHEN lft >= :lft THEN lft - :shift ELSE lft END, " +
110 "lft = CASE WHEN lft >= :lft THEN lft - :shift ELSE lft END, " +
111 "rgt = CASE WHEN rgt >= :lft THEN rgt - :shift ELSE rgt END",
111 "rgt = CASE WHEN rgt >= :lft THEN rgt - :shift ELSE rgt END",
112 {:lft => lft, :shift => rgt - lft + 1}
112 {:lft => lft, :shift => rgt - lft + 1}
113 ])
113 ])
114 self.root_id = id
114 self.root_id = id
115 self.lft, self.rgt = 1, (rgt - lft + 1)
115 self.lft, self.rgt = 1, (rgt - lft + 1)
116 end
116 end
117
117
118 def destroy_children
118 def destroy_children
119 unless @without_nested_set_update
119 unless @without_nested_set_update
120 lock_nested_set
120 lock_nested_set
121 reload_nested_set_values
121 reload_nested_set_values
122 end
122 end
123 children.each {|c| c.send :destroy_without_nested_set_update}
123 children.each {|c| c.send :destroy_without_nested_set_update}
124 reload
124 reload
125 unless @without_nested_set_update
125 unless @without_nested_set_update
126 self.class.where(:root_id => root_id).where("lft > ? OR rgt > ?", lft, lft).update_all([
126 self.class.where(:root_id => root_id).where("lft > ? OR rgt > ?", lft, lft).update_all([
127 "lft = CASE WHEN lft > :lft THEN lft - :shift ELSE lft END, " +
127 "lft = CASE WHEN lft > :lft THEN lft - :shift ELSE lft END, " +
128 "rgt = CASE WHEN rgt > :lft THEN rgt - :shift ELSE rgt END",
128 "rgt = CASE WHEN rgt > :lft THEN rgt - :shift ELSE rgt END",
129 {:lft => lft, :shift => rgt - lft + 1}
129 {:lft => lft, :shift => rgt - lft + 1}
130 ])
130 ])
131 end
131 end
132 end
132 end
133
133
134 def destroy_without_nested_set_update
134 def destroy_without_nested_set_update
135 @without_nested_set_update = true
135 @without_nested_set_update = true
136 destroy
136 destroy
137 end
137 end
138
138
139 def reload_nested_set_values
139 def reload_nested_set_values
140 self.root_id, self.lft, self.rgt = self.class.where(:id => id).pluck(:root_id, :lft, :rgt).first
140 self.root_id, self.lft, self.rgt = self.class.where(:id => id).pluck(:root_id, :lft, :rgt).first
141 end
141 end
142
142
143 def save_nested_set_values
143 def save_nested_set_values
144 self.class.where(:id => id).update_all(:root_id => root_id, :lft => lft, :rgt => rgt)
144 self.class.where(:id => id).update_all(:root_id => root_id, :lft => lft, :rgt => rgt)
145 end
145 end
146
146
147 def move_possible?(issue)
147 def move_possible?(issue)
148 new_record? || !is_or_is_ancestor_of?(issue)
148 new_record? || !is_or_is_ancestor_of?(issue)
149 end
149 end
150
150
151 def lock_nested_set
151 def lock_nested_set
152 if self.class.connection.adapter_name =~ /sqlserver/i
152 if self.class.connection.adapter_name =~ /sqlserver/i
153 lock = "WITH (ROWLOCK HOLDLOCK UPDLOCK)"
153 lock = "WITH (ROWLOCK HOLDLOCK UPDLOCK)"
154 # Custom lock for SQLServer
154 # Custom lock for SQLServer
155 # This can be problematic if root_id or parent root_id changes
155 # This can be problematic if root_id or parent root_id changes
156 # before locking
156 # before locking
157 sets_to_lock = [root_id, parent.try(:root_id)].compact.uniq
157 sets_to_lock = [root_id, parent.try(:root_id)].compact.uniq
158 self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
158 self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
159 else
159 else
160 sets_to_lock = [id, parent_id].compact
160 sets_to_lock = [id, parent_id].compact
161 self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
161 inner_join_statement = self.class.select(:root_id).where(id: sets_to_lock).distinct(:root_id).to_sql
162 self.class.reorder(:id).joins("INNER JOIN (#{inner_join_statement}) as i2 ON #{self.class.table_name}.root_id = i2.root_id").lock.ids
162 end
163 end
163 end
164 end
164
165
165 def nested_set_scope
166 def nested_set_scope
166 self.class.order(:lft).where(:root_id => root_id)
167 self.class.order(:lft).where(:root_id => root_id)
167 end
168 end
168
169
169 def same_nested_set_scope?(issue)
170 def same_nested_set_scope?(issue)
170 root_id == issue.root_id
171 root_id == issue.root_id
171 end
172 end
172
173
173 module ClassMethods
174 module ClassMethods
174 def rebuild_tree!
175 def rebuild_tree!
175 transaction do
176 transaction do
176 reorder(:id).lock.ids
177 reorder(:id).lock.ids
177 update_all(:root_id => nil, :lft => nil, :rgt => nil)
178 update_all(:root_id => nil, :lft => nil, :rgt => nil)
178 where(:parent_id => nil).update_all(["root_id = id, lft = ?, rgt = ?", 1, 2])
179 where(:parent_id => nil).update_all(["root_id = id, lft = ?, rgt = ?", 1, 2])
179 roots_with_children = joins("JOIN #{table_name} parent ON parent.id = #{table_name}.parent_id AND parent.id = parent.root_id").distinct.pluck("parent.id")
180 roots_with_children = joins("JOIN #{table_name} parent ON parent.id = #{table_name}.parent_id AND parent.id = parent.root_id").distinct.pluck("parent.id")
180 roots_with_children.each do |root_id|
181 roots_with_children.each do |root_id|
181 rebuild_nodes(root_id)
182 rebuild_nodes(root_id)
182 end
183 end
183 end
184 end
184 end
185 end
185
186
186 private
187 private
187
188
188 def rebuild_nodes(parent_id = nil)
189 def rebuild_nodes(parent_id = nil)
189 nodes = where(:parent_id => parent_id, :rgt => nil, :lft => nil).order(:id).to_a
190 nodes = where(:parent_id => parent_id, :rgt => nil, :lft => nil).order(:id).to_a
190
191
191 nodes.each do |node|
192 nodes.each do |node|
192 node.send :add_to_nested_set, false
193 node.send :add_to_nested_set, false
193 node.send :save_nested_set_values
194 node.send :save_nested_set_values
194 rebuild_nodes node.id
195 rebuild_nodes node.id
195 end
196 end
196 end
197 end
197 end
198 end
198 end
199 end
199 end
200 end
200 end
201 end
General Comments 0
You need to be logged in to leave comments. Login now