Search code examples
mysqlmultithreadingtransactionsconnection-poolingjooq

Does jOOQ use different connections to execute different queries?

I have a service which does some CRUD over a DB. Service uses jOOQ. This is what my transaction code looks like:

dslContext.transaction(conf -> {
   // Query1
   dslContext.update(Tables.TABLE1).set(Tables.TABLE1.COLUMN1, column1Value).where(Tables.TABLE1.COLUMN2.eq(column2Value)).execute();
   
   // Query2
   dslContext.insertInto(Tables.TABLE2).set(record1).execute();
});

Note that dslContext is global and we are using Apache DBCP2 BasicDataSource.

At high load, when the connection pool gets exhausted, all the threads gets stuck and leaves the DB in a state where only Query1 is executed.

Assuming that the connection pool size is N and is configured to wait indefinitely for a new thread, Hypothesis is that At t=t0, N simultaneous threads try to execute Query1 in Connection1 At t=t1, these N threads move on to Query2 but doesn't get a connection from the pool and waits indefinitely.

Since threads are stuck, transaction didn't end and commit is not called, due to which N threads get stuck forever and never frees up. Rollback also doesn't happen because only way to bring back the system is to restart it. This leaves the DB in an inconsistent state.

Is my hypothesis correct ?

Solution

  • You're not using the transactional conf reference, you're using the outer dslContext, which isn't transactional - meaning each statement is run in its own transaction, just as if you weren't calling the transaction() method.

    Do this instead:

    dslContext.transaction(conf -> {
   // Query1
   conf.dsl().update(Tables.TABLE1)
             .set(Tables.TABLE1.COLUMN1, column1Value)
             .where(Tables.TABLE1.COLUMN2.eq(column2Value)).execute();
   
   // Query2
   conf.dsl().insertInto(Tables.TABLE2).set(record1).execute();
});