Lukas is right. Most of the time people use either connections (to control transactions on them) or managed datasources to control transactions using theirs API. Using raw datasource with jOOQ is kind of limited as you have to choose auto commit.
If you don't feel like going into Spring or JavaEE, you can (here comes self-promotion :) use ULTM which is the most simple transaction manager I could possibly create... and it works on production with jOOQ. https://github.com/witoldsz/ultm Regards, Witold Szczerba 6 maj 2015 19:30 "Lukas Eder" <[email protected]> napisał(a): > Hello Alexey, > > Thank you very much for reporting this. This might indeed be a bug, which > we'll be tracking as: > https://github.com/jOOQ/jOOQ/issues/4277 > > Some background: Few databases have native support for INSERT .. > RETURNING, among which: > > - PostgreSQL > - Firebird > - DB2 (via SELECT * FROM FINAL TABLE (INSERT ...) ) > > Others support fetching arbitrary result columns via JDBC > getGeneratedKeys(), among which: > > - HSQLDB > - Oracle > > Unfortunately, MySQL only allows for returning ID values via > getGeneratedKeys(), which is why jOOQ has to issue an additional select > statement to fetch also the remaining columns. > > Most users are not affected by this issue because they either use a > standalone JDBC Connection in their jOOQ Configuration, or a managed > DataSource, which returns the same Connection within a transaction every > time jOOQ acquires it. This would be the workaround I suggest to you for > the time being (e.g. via a Spring or Java EE DataSource). > > Thanks again for reporting this, > Lukas > > 2015-05-06 1:30 GMT+02:00 Alexey Belostotsky <[email protected]>: > >> INSERT...RETURNING acquires two connections from DataSource with MYSQL >> dialect and not returning first connection until whole query executed. >> >> When INSERT...RETURNING is executed with configuration configured to use >> some DataSource I have encountered a bug where JOOQ acquires connection >> for INSERT part and then tries to acquire another connection for >> "returning" part, but after the first part of query was executed connection >> wasn't closed (and therefore not returned to connection pool) and wasn't >> reused to execute the other part of query. >> If connection instance is explicitly provided to query builder like >> DSL.using(connection) everything goes fine. >> The problem should be easily reproducible with pooled DataSource >> implementation that has maximum pool size of 1, the INSERT part of query >> will be executed successfully, but it will try to acquire connection for >> SELECT part and will fail to do so. I haven't tested problem with other >> dialects/databases or JOOQ versions. >> Configuration code: >> >> Configuration jooqConfiguration = new DefaultConfiguration() >> .derive(dataSource) >> .set(SQLDialect.MYSQL); >> >> Query code: >> >> Record record = DSL.using(configuration) >> .insertInto(SESSION, ...) >> .values(...) >> .returning().fetchOne(); >> >> Database i use is MySQL. >> JOOQ version: 3.6.1 >> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
