From fb311dc654dfe2b0f5103d9ff08a1c790ee58e0e Mon Sep 17 00:00:00 2001
From: Simon Brandhof <simon.brandhof@sonarsource.com>
Date: Wed, 24 Jan 2018 08:26:49 +0100
Subject: [PATCH] SONAR-10117 minimize risk of deadlock on MySQL

---
 .../org/sonar/db/measure/LiveMeasureMapper.xml  | 17 ++++++++++++++++-
 .../step/PersistLiveMeasuresStep.java           | 16 ++++++++++++++--
 2 files changed, 30 insertions(+), 3 deletions(-)

diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
index 0c9db60b475..38c97331736 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
@@ -76,11 +76,26 @@
   </update>
 
   <delete id="deleteByProjectUuidExcludingMarker" parameterType="map">
+    <include refid="sql_deleteByProjectUuidExcludingMarker"/>
+  </delete>
+
+  <!--
+  best practice on MySQL : order the rows to be locked in order
+  to minimze risk of deadlock.
+  https://stackoverflow.com/a/2423921/229031
+  https://jira.sonarsource.com/browse/SONAR-10117?focusedCommentId=153555&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-153555
+  -->
+  <delete id="deleteByProjectUuidExcludingMarker" parameterType="map" databaseId="mysql">
+    <include refid="sql_deleteByProjectUuidExcludingMarker"/>
+    order by uuid
+  </delete>
+
+  <sql id="sql_deleteByProjectUuidExcludingMarker">
     delete from live_measures
     where
     project_uuid = #{projectUuid, jdbcType=VARCHAR} and
     (update_marker != #{marker, jdbcType=VARCHAR} or update_marker is null)
-  </delete>
+  </sql>
 
   <select id="selectTreeByQuery" parameterType="map" resultType="org.sonar.db.measure.LiveMeasureDto" fetchSize="${_scrollFetchSize}" resultSetType="FORWARD_ONLY">
     select <include refid="columns"/> from live_measures lm
diff --git a/server/sonar-server/src/main/java/org/sonar/server/computation/task/projectanalysis/step/PersistLiveMeasuresStep.java b/server/sonar-server/src/main/java/org/sonar/server/computation/task/projectanalysis/step/PersistLiveMeasuresStep.java
index 0280e9a2df5..aa84e147bb9 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/computation/task/projectanalysis/step/PersistLiveMeasuresStep.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/computation/task/projectanalysis/step/PersistLiveMeasuresStep.java
@@ -22,6 +22,7 @@ package org.sonar.server.computation.task.projectanalysis.step;
 import com.google.common.collect.Multimap;
 import java.util.Collection;
 import java.util.HashSet;
+import java.util.Iterator;
 import java.util.Map;
 import java.util.Set;
 import java.util.function.Predicate;
@@ -30,6 +31,7 @@ import org.sonar.core.util.Uuids;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.measure.LiveMeasureDao;
+import org.sonar.db.measure.LiveMeasureDto;
 import org.sonar.server.computation.task.projectanalysis.component.Component;
 import org.sonar.server.computation.task.projectanalysis.component.CrawlerDepthLimit;
 import org.sonar.server.computation.task.projectanalysis.component.DepthTraversalTypeAwareCrawler;
@@ -103,6 +105,7 @@ public class PersistLiveMeasuresStep implements ComputationStep {
 
     @Override
     public void visitAny(Component component) {
+      int count = 0;
       LiveMeasureDao dao = dbClient.liveMeasureDao();
       Multimap<String, Measure> measures = measureRepository.getRawMeasures(component);
       for (Map.Entry<String, Collection<Measure>> measuresByMetricKey : measures.asMap().entrySet()) {
@@ -112,11 +115,20 @@ public class PersistLiveMeasuresStep implements ComputationStep {
         }
         Metric metric = metricRepository.getByKey(metricKey);
         Predicate<Measure> notBestValueOptimized = BestValueOptimization.from(metric, component).negate();
-        measuresByMetricKey.getValue().stream()
+        Iterator<LiveMeasureDto> liveMeasures = measuresByMetricKey.getValue().stream()
           .filter(NonEmptyMeasure.INSTANCE)
           .filter(notBestValueOptimized)
           .map(measure -> measureToMeasureDto.toLiveMeasureDto(measure, metric, component))
-          .forEach(dto -> dao.insertOrUpdate(dbSession, dto, marker));
+          .iterator();
+        while (liveMeasures.hasNext()) {
+          dao.insertOrUpdate(dbSession, liveMeasures.next(), marker);
+          count++;
+          if (count % 100 == 0) {
+            // use short transactions to avoid potential deadlocks on MySQL
+            // https://jira.sonarsource.com/browse/SONAR-10117?focusedCommentId=153555&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-153555
+            dbSession.commit();
+          }
+        }
       }
     }
   }
-- 
2.39.5