Optimizing Spring Transaction Order to Reduce MySQL Lock Time

Environment: Spring 5.3.23.

1. Original Code

<code>static class PersonService {
  @Resource
  private JdbcTemplate jdbcTemplate;
  /**
   * The code first performs an update, then a query.
   * Business logic is very simple.
   */
  @Transactional
  public void operator() {
    // 1
    int res = this.jdbcTemplate.update("update p_user t set t.address='akf' where t.id = 9000000");
    System.out.printf("更新: %d 条数据%n", res);
    // 2
    List<Map<String, Object>> users = this.jdbcTemplate.queryForObject(
      "select * from p_user x where x.username='h4F7i4B4'",
      (rs, rowNum) -> {
        List<Map<String, Object>> datas = new ArrayList<>();
        while (rs.next()) {
          Map<String, Object> obj = new HashMap<>();
          obj.put("id", rs.getObject(1));
          obj.put("username", rs.getObject(2));
          obj.put("email", rs.getObject(3));
          obj.put("password", rs.getObject(4));
          obj.put("address", rs.getObject(5));
          obj.put("age", rs.getObject(6));
          datas.add(obj);
        }
        return datas;
      }
    );
    System.out.println(users);
  }
}
</code>

The table p_user contains about 20 million rows and has only a primary‑key index. No secondary indexes are defined to deliberately create a slow‑query scenario.

Running the SELECT statement takes roughly 5 seconds, which seems acceptable at first glance.

2. Analysis

The method is annotated with @Transactional , so both the UPDATE and the SELECT run inside a single transaction. MySQL’s UPDATE acquires a write lock on the affected row (id = 9000000). Because the transaction does not finish until the slow SELECT completes, the lock is held for the entire 5‑second query duration, potentially blocking other transactions and degrading overall system performance.

If the UPDATE were a range update or lacked an index, MySQL could promote the lock to a gap or even a table lock, worsening the impact.

3. Optimized Code

To minimise lock holding time, reorder the operations so that the SELECT runs before the UPDATE, or move the SELECT out of the transactional context entirely.

<code>@Transactional
public void operator() {
  // 1 – perform the query first (no lock)
  List<Map<String, Object>> users = this.jdbcTemplate.queryForObject(
    "select * from p_user x where x.username='h4F7i4B4'",
    (rs, rowNum) -> {
      List<Map<String, Object>> datas = new ArrayList<>();
      while (rs.next()) {
        Map<String, Object> obj = new HashMap<>();
        obj.put("id", rs.getObject(1));
        // ... other columns ...
        datas.add(obj);
      }
      return datas;
    }
  );
  System.out.println(users);
  // 2 – now perform the update, lock is held only for this short statement
  int res = this.jdbcTemplate.update("update p_user t set t.address='akf' where t.id = 9000000");
  System.out.printf("更新: %d 条数据%n", res);
}
</code>

Alternatively, extract the non‑transactional query into a separate method and invoke the transactional update method from there, ensuring the update runs in its own transaction and the lock duration is minimal.

<code>static class PersonService {
  @Resource
  private JdbcTemplate jdbcTemplate;
  @Resource
  private PersonService ps;

  public void query() {
    ps.update(); // transactional update
    List<Map<String, Object>> users = this.jdbcTemplate.queryForObject(
      "select * from p_user x where x.username='h4F7i4B4'",
      (rs, rowNum) -> {
        List<Map<String, Object>> datas = new ArrayList<>();
        while (rs.next()) {
          Map<String, Object> obj = new HashMap<>();
          obj.put("id", rs.getObject(1));
          // ...
          datas.add(obj);
        }
        return datas;
      }
    );
    System.out.println(users);
  }

  @Transactional
  public void update() {
    int res = this.jdbcTemplate.update("update p_user t set t.address='akf' where t.id = 9000000");
    System.out.printf("更新: %d 条数据%n", res);
  }
}
</code>

Injecting the service into itself (or using AOP context) prevents the common pitfall where a transactional method called from within the same class bypasses the proxy, causing the transaction to be ignored.

By applying these changes, the lock held by the UPDATE statement is released almost immediately after the update executes, reducing contention and improving the performance of concurrent operations.