This time is about database processing. I put three DB access queries in single transactional method as
//first query is to insert data, identiy of the table is a sequence, would //auto-increment as such Query saveTransmission = getEntityManager() .createNativeQuery( "insert into Transmission (transmitTypeCode, transmitStatusCode, source) values (?1, 'GENERATED','GCOM') "); saveTransmission.setParameter(1, transmitTypeCode); BigDecimal transmitId = (BigDecimal) saveTransmission.getSingleResult(); //second query, supposed to retrieve same identy, just now inserted, //which should be the largest value // Query getTransmissionId = getEntityManager() // .createNativeQuery( "select max(transmitId) from Transmission "); // // BigDecimal transmitId = (BigDecimal)getTransmissionId.getSingleResult(); // // log.debug("inserted new transmission: "+transmitId+": "+iter); //third query is to use this identy, for another table Query saveTransmissionFragment = getEntityManager() .createNativeQuery( "insert into TransmissionFragment (transactionId, revisionNumber, transmitId, fragmentTransactionStatusCode) " + "values (?1, ?2, ?3, ?4)");
then yesterady, one exceptions happens. the root cause is, before 2nd query retrieve the maximum value; processes on the 2nd server (“clustering”) runs and increment the identy of the Tranmission Table.
Fixes is very easy, trying to combine first and second query together.
Query saveTransmission = getEntityManager() .createNativeQuery( "insert into Transmission (transmitTypeCode, transmitStatusCode, source) values (?1, 'GENERATED','GCOM') select @@identity "); saveTransmission.setParameter(1, transmitTypeCode); BigDecimal transmitId = (BigDecimal) saveTransmission.getSingleResult(); // // Query getTransmissionId = getEntityManager() // .createNativeQuery( "select max(transmitId) from Transmission "); // // BigDecimal transmitId = (BigDecimal)getTransmissionId.getSingleResult(); // // log.debug("inserted new transmission: "+transmitId+": "+iter); Query saveTransmissionFragment = getEntityManager() .createNativeQuery( "insert into TransmissionFragment (transactionId, revisionNumber, transmitId, fragmentTransactionStatusCode) " + "values (?1, ?2, ?3, ?4)");
Note: I think there is also one thing I missed out, which is about the spring @Transactional annotation.
All above queries are put into one @transactional method, which means either complete all-or-fail. However, it doesnt mean LOCKING THE TABLE TILL ALL FINISHED.
Anyway, i guess need to always pay attention, and think about threading (concurrency) possiblity. (there is at least 2 threads in production clustering.)