CODEWITHSUNDEEP
javamysqljdbc
Kshitij Dhakal
Kshitij Dhakal

Reputation: 844

DELETE AND INSERT in same transaction throws Duplicate entry exception in JDBC

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.

Update Inbox

logger.info("Update inbox. Inbox id : {}", id);
final String query = "UPDATE inbox SET subject=?, updated_at=?, type=? WHERE inbox_id=?";
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, "Error while updating inbox", e);
    return false;
} finally {
    close(pst);
    close(conn);
}

Remove Participants

private boolean removeParticipants(final Connection conn,
                                   final String id,
                                   final TreeleafProto.Debug debug) {
    logger.info(debug, "Deleting inbox participants. Inbox id : {}", id);
    final String query = "DELETE FROM inbox_participant WHERE inbox_id=?";

    try (PreparedStatement pst = dataSource.prepareStatement(query, conn)) {
        logger.debug(debug, QUERY,
                query);
        pst.setString(1, id);
        final var i = pst.executeUpdate();
        logger.debug(debug, "Delete query rows updated : {}", i);
        return i >= 0;
    } catch (JDBCException | SQLException e) {
        logger.error(debug, "Error while removing participants.", e);
        return false;
    }
}

Insert updated participants

private boolean updateParticipants(final Connection conn,
                                   final String id,
                                   final Map<String, InboxProto.InboxParticipant> participants,
                                   final TreeleafProto.Debug debug) {
    logger.info(debug, "Updating inbox participants");
    final String query = "INSERT INTO inbox_participant (inbox_participant_id, inbox_id, account_id, `role`, created_at, updated_at, notification_type, `left`) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

    try (PreparedStatement pst = dataSource.prepareStatement(query, conn)) {
        logger.debug(debug, QUERY,
                query);
        for (Map.Entry<String, InboxProto.InboxParticipant> 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() &&
                Arrays.stream(ints).allMatch(value -> value == 1);
    } catch (JDBCException | SQLException e) {
        logger.error(debug, "Error while updating participants.", e);
        return false;
    }
}

Upvotes: 0

Views: 826

Answers (1)

Atmas
Atmas

Reputation: 2393

If I understand the general logic here, it looks like the code above is attempting to either add (INSERT) OR UPDATE by-way-of-first-deleting-and-then-inserting based on whether the participants are already there or not.

If this is the gist of what you're trying to do, you should look into using an UPSERT syntax. This is a way to push this type of "INSERT or UPDATE if-it-exists-already" logic into the DML which is generally going to be easier to write in code (less code) and easier to test in your SQL console outside of the code, too.

Here's an example reference guide that talks about Upserts. https://blog.usejournal.com/update-insert-upsert-multiple-records-in-different-db-types-63aa44191884

So in your code, this strategy would allow you to remove "removeParticipants" as a method and then instead of INSERT_INBOX_PARTICIPANT being a straight INSERT, like INSERT INTO inbox_participant, you would have something like...

MERGE INTO INBOX_PARTICIPANT T USING 
(VALUES
    (?, ?, ?)
) S (INBOX_ID, PARTICIPANT_ID, SOME_DATA) 
ON T.INBOX_ID = S.INBOX_ID and T.PARTICIPANT_ID= S.PARTICIPANT_ID
WHEN MATCHED THEN UPDATE SET SOME_DATA = S.SOME_DATA
WHEN NOT MATCHED THEN INSERT (INBOX_ID, PARTICIPANT_ID, SOME_DATA) 
VALUES (S.INBOX_ID, S.PARTICIPANT_ID, S.SOME_DATA);

**NOTE: The exact syntax varies according to your underlying database! See page for details! **

This will likely solve your problem indirectly but also lead to easier to maintain code.

As to if you want to continue with your original problem, I would investigate whether your DELETE's are somehow getting rolled back inadvertently.

Or perhaps because you INSERT in batch mode but delete as a direct executeUpdate, there's a possibility that the "batch" mode INSERTs are getting a different transactional context than the DELETEs, since "batch mode" may be attempting to start a separate transaction to manage the batch. To test this theory, maybe try making it so the removes and adds run in the same batch context.

Also if removeParticipants has NO existing participants to delete, it looks like you call rollback because i==0. Is this what you wanted?

Upvotes: 1

Related Questions