https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292015-02-06T01:52:00ZRedmineRedmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613212015-02-06T01:52:00ZToshi MARUYAMA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/18860">Feature #18860</a>: Replace awesome_nested_set gem with a custom implementation of nested sets</i> added</li></ul> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613232015-02-06T01:52:18ZToshi MARUYAMA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-5 priority-4 priority-default closed" href="/issues/6579">Defect #6579</a>: Tree hierachy being currupted on multiple submissions of an issue</i> added</li></ul> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613252015-02-06T01:56:11ZToshi MARUYAMA
<ul><li><strong>Subject</strong> changed from <i>DB deadlocks on concurrent user requests</i> to <i>MS SQL Server deadlocks on concurrent user requests</i></li></ul> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613262015-02-06T01:58:11ZToshi MARUYAMA
<ul></ul><p>It seems awesome_nested_set does not catch MS SQL server dead lock.<br /><a class="external" href="https://github.com/collectiveidea/awesome_nested_set/blob/v2.1.5/lib/awesome_nested_set/awesome_nested_set.rb#L553">https://github.com/collectiveidea/awesome_nested_set/blob/v2.1.5/lib/awesome_nested_set/awesome_nested_set.rb#L553</a></p> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613322015-02-06T15:25:02ZSerghei Zagorinyak
<ul></ul><p>As I'm not really familiar with RoR, I started a clone of Redmine, went to /lib/plugins/awesome_nested_set/lib/awesome_nested_set/model/transactable.rb and edited the def in_tenacious_transaction(&block) method like this (added logging and rescue Exception instead of what was there):</p>
<pre><code class="ruby syntaxhl"> <span class="k">def</span> <span class="nf">in_tenacious_transaction</span><span class="p">(</span><span class="o">&</span><span class="n">block</span><span class="p">)</span>
<span class="n">logger</span> <span class="o">=</span> <span class="no">Logger</span><span class="p">.</span><span class="nf">new</span><span class="p">(</span><span class="no">File</span><span class="p">.</span><span class="nf">open</span><span class="p">(</span><span class="no">Rails</span><span class="p">.</span><span class="nf">root</span><span class="p">.</span><span class="nf">join</span><span class="p">(</span><span class="s1">'log/deadlock_msg.log'</span><span class="p">),</span> <span class="s1">'a'</span><span class="p">,</span> <span class="ss">sync: </span><span class="kp">true</span><span class="p">))</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">formatter</span> <span class="o">=</span> <span class="no">Logger</span><span class="o">::</span><span class="no">Formatter</span><span class="p">.</span><span class="nf">new</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">info</span> <span class="s2">"in transaction"</span>
<span class="n">retry_count</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">begin</span>
<span class="n">transaction</span><span class="p">(</span><span class="o">&</span><span class="n">block</span><span class="p">)</span>
<span class="k">rescue</span> <span class="no">Exception</span> <span class="o">=></span> <span class="n">error</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">error</span> <span class="s2">"exception happened"</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">error</span> <span class="s2">"error: </span><span class="si">#{</span><span class="n">error</span><span class="p">.</span><span class="nf">message</span><span class="si">}</span><span class="s2">"</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">error</span> <span class="n">error</span><span class="p">.</span><span class="nf">backtrace</span>
<span class="k">raise</span> <span class="k">unless</span> <span class="n">connection</span><span class="p">.</span><span class="nf">open_transactions</span><span class="p">.</span><span class="nf">zero?</span>
<span class="k">raise</span> <span class="k">unless</span> <span class="n">error</span><span class="p">.</span><span class="nf">message</span> <span class="o">=~</span> <span class="sr">/Deadlock found when trying to get lock|Lock wait timeout exceeded|has been chosen as the deadlock victim/</span>
<span class="k">raise</span> <span class="k">unless</span> <span class="n">retry_count</span> <span class="o"><</span> <span class="mi">10</span>
<span class="n">retry_count</span> <span class="o">+=</span> <span class="mi">1</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">info</span> <span class="s2">"Deadlock detected on retry </span><span class="si">#{</span><span class="n">retry_count</span><span class="si">}</span><span class="s2">, restarting transaction"</span>
<span class="nb">sleep</span><span class="p">(</span><span class="nb">rand</span><span class="p">(</span><span class="n">retry_count</span><span class="p">)</span><span class="o">*</span><span class="mf">0.1</span><span class="p">)</span> <span class="c1"># Aloha protocol</span>
<span class="k">retry</span>
<span class="k">end</span>
<span class="k">end</span>
</code></pre>
<p>What confuses me is that the only records I see in this log file are the "in transaction" ones, but no error messages which I expected to see. Still I see deadlock errors in production.log.</p> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613782015-02-08T15:03:20ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Subject</strong> changed from <i>MS SQL Server deadlocks on concurrent user requests</i> to <i>Potential server deadlocks on concurrent issue creation</i></li><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Assignee</strong> set to <i>Jean-Philippe Lang</i></li><li><strong>Target version</strong> set to <i>3.0.0</i></li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul><p>This problem is not SQLServer specific. With PostgreSQL set to french locale, dead locks also occur.</p>
<p>This is fixed in 3.0 where awesome_nested_set is replaced with a custom implementation of nested sets that should properly set locks to prevent dead locks. A test with concurrent issue creation/deletion was added to demonstrate and was failing with awesome_nested_set: <a class="source" href="https://www.redmine.org/projects/redmine/repository/svn/entry/trunk/test/unit/issue_nested_set_concurrency_test.rb">source:/trunk/test/unit/issue_nested_set_concurrency_test.rb</a></p> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=613792015-02-08T15:03:30ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Subject</strong> changed from <i>Potential server deadlocks on concurrent issue creation</i> to <i>Potential DB deadlocks on concurrent issue creation</i></li></ul> Redmine - Defect #19040: Potential DB deadlocks on concurrent issue creationhttps://www.redmine.org/issues/19040?journal_id=616412015-02-20T04:30:07ZToshi MARUYAMA
<ul></ul><p>I created issue.<br /><a class="external" href="https://github.com/collectiveidea/awesome_nested_set/issues/298">https://github.com/collectiveidea/awesome_nested_set/issues/298</a></p>