FYI, I was able to change the statement used in @lock_nested_set@

from:

<pre>
self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
</pre>

to:

<pre>
self.class.reorder(:id).joins("INNER JOIN #{self.class.table_name} t2 ON #{self.class.table_name}.root_id = t2.root_id").where("t2.id IN (?)", sets_to_lock).distinct.lock.ids
</pre>

so far the later returns instantly, with the same results as the former. I don't know though how it affects the locking mechanism.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Passed all tests.

The following is a diff of changes made by Stephane Evr.

<pre><code class="diff">
Index: lib/redmine/nested_set/issue_nested_set.rb
===================================================================
--- lib/redmine/nested_set/issue_nested_set.rb (revision 15663)
+++ lib/redmine/nested_set/issue_nested_set.rb (working copy)
@@ -158,7 +158,7 @@
self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
else
sets_to_lock = [id, parent_id].compact
- self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
+ self.class.reorder(:id).joins("INNER JOIN #{self.class.table_name} t2 ON #{self.class.table_name}.root_id = t2.root_id").where("t2.id IN (?)", sets_to_lock).distinct.lock.ids
end
end

</code></pre>
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Go MAEDA wrote:
> Passed all tests.

Did you run the tests with mysql? Because I get an error with Postgresql with the patch applied (FOR UPDATE not allowed with DISTINCT).
Tests pass without @.distinct@
--------------------------------------------------------------------------------
Jean-Philippe Lang wrote:
> Did you run the tests with mysql? Because I get an error with Postgresql with the patch applied (FOR UPDATE not allowed with DISTINCT).
> Tests pass without @.distinct@

Sorry, I run the tests only with sqlite.
--------------------------------------------------------------------------------
Patch committed without the @.distinct@ call, thanks.
--------------------------------------------------------------------------------
MySQL "IssueNestedSetConcurrencyTest#test_concurrency" and "IssueNestedSetConcurrencyTest#test_concurrent_subtasks_creation" fail.
http://www.redmine.org/builds/logs/build_trunk_mysql_ruby-2.3_3063.html

It may be related with #19344.

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
r15891 reverted.
--------------------------------------------------------------------------------
I found an alternative solution which passes the tests, although not as fast as with a JOIN. Here, I simply group by issue id in the original subquery:

SELECT `issues`.* FROM `issues` WHERE (root_id IN (SELECT root_id from issues WHERE id IN (96457,96455) *GROUP BY id*)) ORDER BY `issues`.`id` ASC;

So the statement would be rewritten as :

<pre>
self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?) GROUP BY id)", sets_to_lock).lock.ids
</pre>

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
I finally found how to refactor the JOIN Statement and keep the initial performance improvements. Please find attached a patch against the master branch. This passed the issue_nested_set_concurrency_test on MySQL.
--------------------------------------------------------------------------------
I ran the test 10 times and all passed
--------------------------------------------------------------------------------
Reverted r16053 because of SQL error with PostgreSQL.
--------------------------------------------------------------------------------
PostgreSQL does not accept the DISTINCT in the subquery:
http://www.redmine.org/builds/logs/build_trunk_postgresql_ruby-2.3_3124.html

One option would be to have a statement specfic to MySQL just like we already have one for SQL Server.
--------------------------------------------------------------------------------
Jean-Philippe Lang wrote:
> PostgreSQL does not accept the DISTINCT in the subquery:
> http://www.redmine.org/builds/logs/build_trunk_postgresql_ruby-2.3_3124.html
>
> One option would be to have a statement specfic to MySQL just like we already have one for SQL Server.

Perhaps this is the solution. I don't know if PostgreSQL is affected by this problem.
--------------------------------------------------------------------------------
Based #note-13 patch.
This patch reduces #19344 test failure times on my CentOS7 mariadb-5.5.52-1.el7.x86_64.
(Not always test pass. About 50% time test passes.)

--------------------------------------------------------------------------------

relates,New,19344,MySQL 5.6: IssueNestedSetConcurrencyTest#test_concurrency : always fails