Hello,

After a while (of successes and failures) I reach a point where inserts break the unique index on the database table.

I use MySQL, with a Long id for each class and hikari as connection pool.

The ids value are generated @GeneratedValue(strategy = GenerationType.TABLE, generator = <name>).
The sequence @TableGenerator(name = FINANCIAL, table = SEQUENCE_GENERATOR, pkColumnName = SEQUENCE_NAME, pkColumnValue = FINANCIAL, valueColumnName = SEQUENCE_VALUE).
Each class has its own sequence.

The transaction inserts 2 objects (A and B).

When the issue occurs:
- the sequence table has sequence value for A lower than the max id of A table. This explain why the next inserts fail.
- the value used for the table A seems the one applicable to table B (in the allocation range).

I checked :
- the sequences names are all differents.
- no specific settings on the connection allocating sequences. So sequences are allocated in a dedicated connection (except if hikari hide something).
 


Any suggestion on the reason of things to look at ?

thanks
--
Stephane

Debug InsertRequest with reference to the LOG. Statements for getting "increment" generator values will be shown in the log ...

Thanks for your help. I looked at these logs. 

To clearly identify the id value, I made ids really different in the tables (multiply per 10, 100, ...) and reindexed the sequence table and add 100 to each value.

This seems to solve the situation but I don't know why that table was desynchronized.
I quickly looked at the code. It looks very good, obtain sequence values in synchronized block (I guess the locked ValueGenerator is a singleton) and the block reserved in db put a lock.

Reserving a value generator value can be done in its own connection (and is by default, but debugging would show that). As per

datanucleus.valuegeneration.transactionAttribute.

I don't have any "hang". I; I'd do a thread dump if I ever got such a thing.

Thanks Andy.
You're right about reserving a value generator. It can safely be done in the same connection than the Insert.

1. I'm simply pointing the db lock remain until the transaction ends. Then a concurrent transaction reserving another sequence value is blocked by this db lock.
2. As you also know db locking has database related side effect. Some database implement row lock as page lock. While page can contain several record (depending of the length of the record), locking a record locks several rows.

If my memory is still good, db2 do this and have by default pages of 4K. MySQL have a real row lock (others I don't know)... Considering the small size of our sequence table the chance to face this situation is high.

eg. in my app, I have 22 sequences for a total of 194 characters (the entire table is stored in one single page)

--
Stephane