JOOQ - DataChangedException when calling Record.update()?

1
votes

I think I'm missing something major about how UpdatableRecord.update() works.

Example code that doesn't work:

MailKeywordRealAddressRecord linkRecord = dsl.select().
  from(MAIL_KEYWORD_REAL_ADDRESS).
  where(MAIL_KEYWORD_REAL_ADDRESS.MAIL_KEYWORD_ID.eq(mailKeyword.getId())).
  fetchOneInto(MailKeywordRealAddressRecord.class);

if( linkRecord == null ){
  log.debug("no link record found for our mailKeyword");
  linkRecord = dsl.newRecord(MAIL_KEYWORD_REAL_ADDRESS);
  linkRecord.setMailKeywordId(mailKeyword.getId());
  linkRecord.setRealAddressId(realAddress.getId());

  linkRecord.insert();
}
else {
  // 1 - will geta "DataChangedException" because it tries to do a
  // "select ... for udpate" with the *new* realAddress id
  log.debug("updating old linkRecord from: {}", linkRecord );
  linkRecord.setRealAddressId(realAddress.getId());
  linkRecord.update();

  // 2 - working
  //      log.debug("updating old linkRecord from: {}", linkRecord );
  //      linkRecord.delete();
  //      linkRecord.setRealAddressId(realAddress.getId());
  //      linkRecord.insert();
}

This will result in an exception:

DataChangedException: Database record no longer exists

And if I look at the SQL, JOOQ is issuing a select for update SQL statement using the new value of RealAddressId.

If I comment out the (1) block and use (2) - JOOQ appears to do what I want, it deletes the old record and updates the new one.

The table underneath MailKeywordRealAddressRecord is an ordinary many-to-many link table (two columns, with both being declared as the composite primary key).

Thinking about it... - is that the problem? That I'm updating a primary key column? I'm happy enough to stick with the delete/insert logic (or I could refactor to a direct SQL statement) - just want to figure out what's going on.

Database is Postgres, JOOQ version is 3.10.1.

1
javasqljooq
Are you using the Settings.executeWithOptimisticLockingIncludeUnversioned feature? What was your expectation when you turned that feature on? – Lukas Eder
@LukasEder The JOOQ codegen setup can be found here: bitbucket.org/snippets/shorn/64RnL5/jooq-subproject And looking in my Spring setup, the JOOQ settings are configured as "withExecuteWithOptimisticLocking(true)". – Shorn
So far as my expectations for the feature - I would not have assumed it would be relevant for a record without a version field. – Shorn
Well, the feature really says: "execute with optimistic locking, including those records for which there is no version field". I'll provide an answer. – Lukas Eder

1 Answers

0
votes

General recommendation: Use INSERT .. ON CONFLICT

Your current logic might run into race conditions and is generally a bit too tedious to write. jOOQ supports PostgreSQL's INSERT .. ON CONFLICT DO UPDATE syntax, which delegates handling of upserts to the database, which you should usually prefer, unless you really rely on optimistic locking, which you don't seem to do in your current code example

Answering your question

I think this just stems from a misunderstanding of jOOQ's optimistic locking feature, see also the manual:

There are two settings governing the behaviour of the jOOQ optimistic locking feature:

  • executeWithOptimisticLocking: This allows for turning off the feature entirely.
  • executeWithOptimisticLockingExcludeUnversioned: This allows for turning off the feature for updatable records who are not explicitly versioned.

For backwards compatibility reasons, both flags are turned off by default. If you turn on optimistic locking, then by default, it will be turned on also for UpdatableRecords which are not explicitly configured with a version field. In that case, the entire record is validated when performing the optimistic locking checks. This is probably not what you wanted, so you should set this flag to true.