Concurrency issue with micronaut-jdbi

[marcinfigiel]

Expected Behavior

When running multiple concurrent DB operations with few transactions each, all of them should end succesfully eventually.

Actual Behaviour

Transactions fail randomly.

Steps To Reproduce

1. Setup micronaut project with JDBI (including SQL Objects) and Hikari CP,
2. Use PostgreSQL 15 DB (didn't test with another),
3. Set low CP size (e.g. 5) - this makes the failures more frequent, but even with big CP they still occur,
4. Prepare simple data structure with 2 tables - one table has an FK to the other,
5. Prepare necessary logic to handle those tables - standard CRUD operations with JDBI SQL Objects,
6. Prepare business logic which saves entity A containing reference to entity B - before saving entity A it should "save entity B if neccessary", i.e. fetch it from DB and if it doesn't exist save it,
7. Run "save entity A" operation concurrently (10 threads still failed, I didn't try with lower concurrency),
8. Observe some of the saves fail because of FK violation.

Environment Information

• Operating System: Pop!_OS 22.04 (kernel 6.8.0)
• JDK Version: openjdk 21.0.3
• Database: PostgreSQL 15

Example Application

https://github.com/marcinfigiel/demo-jdbi-error

Version

4.5.0