transaction problem (seems not committing)

[abdurrahman abdsahin]

Hi all;
I'm new to this forum. I have a problem about mysql transactions in my java & spring application.

i have a function like below, in the function a query a table, retrieve a number , increase the number then insert a new row with increased number, the column where number is written is unique, so i don't expect to see 2 rows with the same generated number. I use spring to achieve single instances of my beans, to synchronize multiple threads, i also set mysql default transaction level to read-committed, and following function block is transactional (when function call is finished transaction is committed by spring, and a new mysql connection is retrieved for the next thread)
if don't make function block transactional, i don't get duplicate key index error. (in that case a new mysql connection is not retrieved from the connection pool.)

public synchronized void suitableConfirmNo(){
System.out.println("Thread entered " + Thread.getCurrentThread().getName());

.. query the max number


... increase number by 1, then insert a new row with increased value...

System.out.println("Thread leaved" + Thread.getCurrentThread().getName());

}


i get duplicate key index error under many active threads which are calling that function.

when i review my logs i see that threads are inserted into the function in order, two thread are not found in the function block.

Thread entered thread-1
Thread leaved thread-1

Thread entered thread-5
Thread leaved thread-5

Thread entered thread-8
Thread leaved thread-8

Thread entered thread-2
Thread leaved thread-2

Thread entered thread-190
Thread leaved thread-190

Thread entered thread-110
Thread leaved thread-110

...