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

[distinctdan]

So I'm not on the Micronaut team, but I was interested in this because I'm setting up the db driver for a project. I see you have autocommit turned off and you're not using @Transactional. I know postgres has a built-in "autocommit", but I'm wondering if either hikari autocommit or @Transactional needs to be used here? I'm assuming that @SqlQuery doesn't do anything with a transaction automatically.

[marcinfigiel]

There is @Transactional annotation in the DatabaseService, it's required for the managed Jdbi instance to work.