[Theading] concurrency issue again…..

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.)