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] <javascript:>>:
>
>> 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] <javascript:>.
>> 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.
