Hi Lukas, you are right, I did not think about solving the problem described by Alexey by using jOOQ's transactions. It's a good hint for a workaround for this bug.
Regards, Witold Szczerba On Thu, May 7, 2015 at 9:43 AM, Lukas Eder <[email protected]> wrote: > Thanks Witold :-) > > Well, using a jOOQ transaction would also have the same effect, in fact, > as the DefaultTransactionProvider semantics would also retain the > Connection in a new ConnectionProvider, for the duration of the transaction: > http://www.jooq.org/doc/latest/manual/sql-execution/transaction-management > > Cheers, > Lukas > > 2015-05-07 2:25 GMT+02:00 Witold Szczerba <[email protected]>: > >> 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. >> > > -- > 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.
