title: DELETE AND INSERT in same transaction throws Duplicate entry exception in JDBC
body: <p>I have a table inbox_participants that has inbox_id as a foreign key. When the inbox is updated, participants may be added or deleted. I am trying to delete all the inbox participants for the given inbox_id and reinsert updated participants with the same participant_id. It runs normally on my local machine. But in the server, it gives a Duplicate Entry exception for inbox_participant_id that should have been deleted.</p>
  <h3>Update Inbox</h3>
  <pre><code>logger.info(&quot;Update inbox. Inbox id : {}&quot;, id);
  final String query = &quot;UPDATE inbox SET subject=?, updated_at=?, type=? WHERE inbox_id=?&quot;;
  Connection conn = null;
  PreparedStatement pst = null;
  try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);
      pst = dataSource.prepareStatement(query, conn);
      logger.debug(debug, QUERY, query);
      pst.setString(1, inbox.getSubject());
      pst.setLong(2, TreeleafDate.timestamp());
      pst.setInt(3, inbox.getTypeValue());
      pst.setString(4, id);
      if (pst.executeUpdate() != 1) {
          rollback(conn);
          return false;
      }
      if (!removeParticipants(conn, id, debug)) {
          rollback(conn);
          return false;
      }
      if (!updateParticipants(conn, id, accountIdParticipantMap, debug)) {
          rollback(conn);
          return false;
      }
      commit(conn);
      return true;
  } catch (SQLException | JDBCException e) {
      rollback(conn);
      logger.error(debug, &quot;Error while updating inbox&quot;, e);
      return false;
  } finally {
      close(pst);
      close(conn);
  }
  </code></pre>
  <h3>Remove Participants</h3>
  <pre><code>private boolean removeParticipants(final Connection conn,
                                     final String id,
                                     final TreeleafProto.Debug debug) {
      logger.info(debug, &quot;Deleting inbox participants. Inbox id : {}&quot;, id);
      final String query = &quot;DELETE FROM inbox_participant WHERE inbox_id=?&quot;;
  
      try (PreparedStatement pst = dataSource.prepareStatement(query, conn)) {
          logger.debug(debug, QUERY,
                  query);
          pst.setString(1, id);
          final var i = pst.executeUpdate();
          logger.debug(debug, &quot;Delete query rows updated : {}&quot;, i);
          return i &gt;= 0;
      } catch (JDBCException | SQLException e) {
          logger.error(debug, &quot;Error while removing participants.&quot;, e);
          return false;
      }
  }
  </code></pre>
  <h3>Insert updated participants</h3>
  <pre><code>private boolean updateParticipants(final Connection conn,
                                     final String id,
                                     final Map&lt;String, InboxProto.InboxParticipant&gt; participants,
                                     final TreeleafProto.Debug debug) {
      logger.info(debug, &quot;Updating inbox participants&quot;);
      final String query = &quot;INSERT INTO inbox_participant (inbox_participant_id, inbox_id, account_id, `role`, created_at, updated_at, notification_type, `left`) VALUES(?, ?, ?, ?, ?, ?, ?, ?)&quot;;
  
      try (PreparedStatement pst = dataSource.prepareStatement(query, conn)) {
          logger.debug(debug, QUERY,
                  query);
          for (Map.Entry&lt;String, InboxProto.InboxParticipant&gt; entry : participants.entrySet()) {
              final var participant = entry.getValue();
              pst.setString(1, getId(participant));
              pst.setString(2, id);
              pst.setString(3, entry.getKey());
              pst.setInt(4, participant.getRoleValue());
              pst.setLong(5, TreeleafDate.timestamp());
              pst.setLong(6, TreeleafDate.timestamp());
              pst.setInt(7, participant.getNotificationTypeValue());
              pst.setInt(8, participant.getParticipantStatusValue());
              pst.addBatch();
          }
          int[] ints = pst.executeBatch();
          return ints.length == participants.size() &amp;&amp;
                  Arrays.stream(ints).allMatch(value -&gt; value == 1);
      } catch (JDBCException | SQLException e) {
          logger.error(debug, &quot;Error while updating participants.&quot;, e);
          return false;
      }
  }
  </code></pre>