Inconsistent order of database insertions in Spring boot application using JPA

Question

I'm experiencing an issue with the order of database insertions in my Spring application. I have a service method annotated with @Transactional to ensure atomicity and consistency. However, sometimes the order of insertions is inconsistent, and records that were supposed to be inserted earlier end up being inserted late, i'm using a websocket end-point to insert messages using this service method "saveUserMessage" this problem happens only if i invoked this end point very fast, i hope someone can help

@MessageMapping("/send")
public void send(
        @Payload MessageRequestDTO messageDTO,
        @Header("simpSessionId") String sessionId,
        SimpMessageHeaderAccessor headerAccessor
) {
    // access the header
    Principal principal = (Principal) headerAccessor.getSessionAttributes().get(sessionId);

    if (!principal.getName().equals(messageDTO.sender())) {
        String errorMessage = "You are not eligible to send this message";
        messagingTemplate.convertAndSendToUser(principal.getName(), "/queue/errors", errorMessage);
        return;
    }

    try {

        MessageResponseDTO messageResponseDTO = new MessageResponseDTO(
                randomUUID().toString(),
                messageDTO.conversationId(),
                messageDTO.sender(),
                messageDTO.receiver(),
                messageDTO.message(),
                "PENDING",
                new java.util.Date().toGMTString(),
                new java.util.Date().toGMTString()
        );
        messagingTemplate.convertAndSendToUser(messageDTO.receiver(), "/queue/private", messageResponseDTO);
        messagingTemplate.convertAndSendToUser(principal.getName(), "/queue/private", messageResponseDTO);
        messageService.saveUserMessage(messageDTO);

    } catch (Exception e) {
        messagingTemplate.convertAndSendToUser(principal.getName(), "/queue/errors", e.getMessage());
    }
}

The Transaction can only guarantee atomicity in the method call, not including the message broker (or other fire&forget interactions). If you write *n* (identical) messages *m* times fast, you will see inconsistencies in the order because m_11 might be followed with m_21 before m_12 arrived. — Valerij Dobler, Jul 02 '23 at 19:28
You could try to synchronize the method or better yet do the message sending (if possible in one) after the transaction succeeded. — Valerij Dobler, Jul 02 '23 at 19:30

score 0 · Answer 1 · answered Jul 03 '23 at 06:04

If you use multiple clients to send messages to the shown endpoint, how will the "correct order" be determined? Even if the timing of the senders is known, the network structure can lead to a reordering of packages on the network.

So for messages from one client, the client should add some order into the payload, but for different clients you have to synchronized them by whatever criterion on the server.

You cannot rely on the database or the network to keep things in order. If you however only have one client, you could try to synchronize on the server - but outside the database - and this will create a bottleneck so only one insert after another reaches the database. Your performance probably will drop, so I really would recommend some other form of ordering as described above.

Inconsistent order of database insertions in Spring boot application using JPA

1 Answers1