We are facing a weird transactional issue with Spring data JPA and Mysql
We have a table tableA in which we are storing the request execution status for an SDP (SDP is another entity and is referenced by a sdp_id in tableA)
Consider that request comes very first time for an SDP. The tableA does not have any entry for this SDP.
We add a new entry to tableA with status IN_PROGRESS in a new transaction. The purpose of doing it in a new transaction is if any other request comes for same SDP, the status IN_PROGRESS should be visible so that duplicate flow is not triggered for the same SDP
After the business logic is complete, we update the tableA entry by setting status=COMPLETED for the same row.
This is working fine for all but one environment. On this one environment, instead of updating the same row as COMPLETED, it is inserting a new row with status. So, at the end there are 2 rows for same SDP with statuses IN_PROGRESS and COMPLETED.
We have confirmed that all the environments have exact same version of JRE and Mysql. What can be the reason for this difference in transactional behaviour just on one environment?