From a56652bdd65a9987231e890f46e85e482c8d2f7f Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Wed, 18 Jan 2023 12:43:42 +0300
Subject: Avoid nested transactions when reading messages from database

---
 src/main/java/com/juick/service/MessagesService.java   |  3 ---
 .../java/com/juick/service/MessagesServiceImpl.java    | 18 +++++++-----------
 2 files changed, 7 insertions(+), 14 deletions(-)

(limited to 'src')

diff --git a/src/main/java/com/juick/service/MessagesService.java b/src/main/java/com/juick/service/MessagesService.java
index b9ee612a..995e8e53 100644
--- a/src/main/java/com/juick/service/MessagesService.java
+++ b/src/main/java/com/juick/service/MessagesService.java
@@ -125,9 +125,6 @@ public interface MessagesService {
 
     @Cacheable(value = "messages", key="{ #visitor.uid, #mids.hashCode() }")
     List<Message> getMessages(User visitor, List<Integer> mids);
-
-    Map<Integer,Set<Reaction>> updateReactionsFor(final List<Integer> mid);
-
     @Cacheable(value = "replies", key="{ #user.uid, #mid }")
     List<Message> getReplies(User user, int mid);
 
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index bb50d953..add8db22 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -30,7 +30,6 @@ import org.apache.commons.lang3.tuple.Pair;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.beans.factory.annotation.Value;
-import org.springframework.dao.DataIntegrityViolationException;
 import org.springframework.dao.IncorrectResultSizeDataAccessException;
 import org.springframework.jdbc.core.BatchPreparedStatementSetter;
 import org.springframework.jdbc.core.ConnectionCallback;
@@ -871,17 +870,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         return Collections.emptyList();
     }
 
-    @Transactional(readOnly = true)
-    @Override
-    public Map<Integer, Set<Reaction>> updateReactionsFor(final List<Integer> mids) {
-
-        return getNamedParameterJdbcTemplate().query(
-                "select f.message_id as mid, f.like_id as lid," + " r.description as descr, count(f.like_id) as cnt"
-                        + " from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id "
-                        + " where f.message_id IN (:mids) " + " group by f.message_id, f.like_id, r.description",
+    private Map<Integer, Set<Reaction>> updateReactionsFor(final List<Integer> mids) {
+        // This method always called from the transactional block, so it should not be marked as transactional itself
+        return getNamedParameterJdbcTemplate().query("""
+                        select f.message_id as mid, f.like_id as lid,
+                        r.description as descr, count(f.like_id) as cnt
+                        from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id
+                        where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""",
                 new MapSqlParameterSource("mids", mids), (ResultSet rs) -> {
                     Map<Integer, Set<Reaction>> results = new HashMap<>();
-
                     while (rs.next()) {
                         int messageId = rs.getInt("mid");
                         int likeId = rs.getInt("lid");
@@ -893,7 +890,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         results.computeIfAbsent(messageId, HashSet::new);
                         results.get(messageId).add(reaction);
                     }
-
                     return results;
                 });
 
-- 
cgit v1.2.3