I'm sorry for the delay here. Yes indeed, as I've mentioned in a previous E-Mail, jOOQ currently has a limitation where the combination of the optimistic locking feature and batching is not possible: https://github.com/jOOQ/jOOQ/issues/1637
Hope this helps, Lukas 2017-03-28 20:38 GMT+02:00 <[email protected]>: > any insight on this would be greatly appreciated > > > On Thursday, March 23, 2017 at 10:22:54 AM UTC-7, Anuj Kumar wrote: >> >> Some what related to batchUpdate() and optimistic locking. When I try to >> do a batch update on a stale record it fails and does not throw any kind of >> exception. For example: >> >> RoleRecord roleRecord = new RoleRecord(); >> roleRecord.setName("role"); // name is the PK for this table >> >> >> roleRecord.attach(configuration); >> roleRecord.insert(); >> >> assertThat(roleRecord.getVersion()).isEqualTo(1); >> assertThat(roleRecord.getDescription()).isNull(); >> >> roleRecord.setDescription("desc1"); >> DSL.using(configuration).batchUpdate(roleRecord).execute(); // this works >> fine as we have the most current roleRecord >> >> RoleRecord fromDB = >> DSL.using(configuration).selectFrom(ROLE).where(ROLE.NAME.eq("role")).fetchOne(); >> >> assertThat(fromDB.getDescription()).isEqualTo("desc1"); >> assertThat(fromDB.getVersion()).isEqualTo(2); >> >> roleRecord.setDescription("desc2"); >> DSL.using(configuration).batchUpdate(roleRecord).execute(); // does not >> throw DataChangedException, we have a stale roleRecord here >> //roleRecord.update(); // throws a DataChangedException which is the correct >> behaviour >> >> fromDB = >> DSL.using(configuration).selectFrom(ROLE).where(ROLE.NAME.eq("role")).fetchOne(); >> >> assertThat(fromDB.getVersion()).isEqualTo(3); // fails >> assertThat(fromDB.getDescription()).isEqualTo("desc2"); // fails >> >> >> >> >> On Saturday, March 11, 2017 at 1:58:42 AM UTC-8, Lukas Eder wrote: >>> >>> Thank you very much for reporting. Yes indeed, currently, optimistic >>> locking doesn't work well with batchStore(), batchUpdate(), and >>> batchDelete(). There's an issue for this limitation: >>> https://github.com/jOOQ/jOOQ/issues/1637 >>> >>> Probably, however, you meant to do bulk deletion, not batch deletion. >>> That would be done much more simply by running an ordinary DELETE statement: >>> >>> DSL.using(configuration) >>> .deleteFrom(BOOK_AUTHOR) >>> .where(BOOK_ID.eq(1)) >>> .and(AUTHOR_ID.eq(1)) >>> .execute(); >>> >>> >>> Another option is to turn off Settings.executeWithOptimisticLocking for >>> this particular batch call, or >>> Settings.executeWithOptimisticLockingExcludeUnversioned >>> for all of your configurations (this turns off optimistic locking for >>> tables that don't have a version column), but again, I don't think you >>> actually want to batch this call. >>> >>> Hope this helps, >>> Lukas >>> >>> 2017-03-09 8:09 GMT+01:00 <[email protected]>: >>> >>>> I have a many-to-many relationship with book, book_author and author >>>> tables. The book and author table have version column and I have configured >>>> the optimistic locking option to true. The book_author table just has the >>>> book_id and author_id fields. On executing the below statement >>>> >>>> BookAuthorRecord bookAuthorRecord = new BookAuthorRecord(1,1); >>>> DSL.using(configuration).batchDelete(bookAuthorRecord).execute(); >>>> >>>> >>>> I get the below error: >>>> >>>> Exception in thread "main" org.jooq.exception.DataAccessException: SQL >>>> [select `book_author`.`book_id`, `book_author`.`author_id` from >>>> `book_author` where (`book_author`.`book_id` = ? and >>>> `book_author`.`author_id` = ?) for update]; Can not issue executeUpdate() >>>> or executeLargeUpdate() for SELECTs >>>> at org.jooq.impl.Tools.translate(Tools.java:1941) >>>> at >>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:659) >>>> at org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:259) >>>> at org.jooq.impl.BatchSingle.execute(BatchSingle.java:182) >>>> at org.jooq.impl.BatchCRUD.executePrepared(BatchCRUD.java:159) >>>> at org.jooq.impl.BatchCRUD.execute(BatchCRUD.java:100) >>>> at com.jooq.JooqSampleTest.main(JooqSampleTest.java:37) >>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >>>> at >>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >>>> at >>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >>>> at java.lang.reflect.Method.invoke(Method.java:498) >>>> at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147) >>>> Caused by: java.sql.BatchUpdateException: Can not issue executeUpdate() or >>>> executeLargeUpdate() for SELECTs >>>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) >>>> at >>>> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) >>>> at >>>> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) >>>> at java.lang.reflect.Constructor.newInstance(Constructor.java:423) >>>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) >>>> at com.mysql.jdbc.Util.getInstance(Util.java:387) >>>> at >>>> com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1161) >>>> at >>>> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773) >>>> at >>>> com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257) >>>> at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:958) >>>> at >>>> org.jooq.tools.jdbc.DefaultStatement.executeBatch(DefaultStatement.java:93) >>>> at org.jooq.impl.BatchSingle.executePrepared(BatchSingle.java:239) >>>> ... 9 more >>>> Caused by: java.sql.SQLException: Can not issue executeUpdate() or >>>> executeLargeUpdate() for SELECTs >>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963) >>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) >>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) >>>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) >>>> at >>>> com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2045) >>>> at >>>> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751) >>>> ... 13 more >>>> >>>> >>>> But if I try to delete on the record, it works fine, so the below works: >>>> >>>> BookAuthorRecord bookAuthorRecord = new BookAuthorRecord(1,1); >>>> bookAuthorRecord.attach(configuration); >>>> bookAuthorRecord.refresh(); >>>> bookAuthorRecord.delete(); >>>> >>>> >>>> I would like to know how I can do bulk deletes for junction table which >>>> does not have the version column and I have configured optimistic >>>> >>>> locking to true. >>>> >>>> >>>> -- >>>> 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.
