Optimistic Locking issue

[Ryan How]

Hi Lukas,

I've having an issue with optimistic locking.

The issue happens if you.

fetch record

change some data

store

change some data

store - gives DataChangeException

It seems the internal "original" fields are not reset after a store. Which I am not sure if they should be. But it would make sense to me for the above code to work. Would there be any issues with resetting the "original" fields after successfully saving a record?. Or can there be a setting to enable that behaviour?

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

> It seems the internal "original" fields are not reset after a store. Which I
> am not sure if they should be. But it would make sense to me for the above
> code to work. Would there be any issues with resetting the "original" fields
> after successfully saving a record?

You're probably right. "original" values should be updated, once a
record is stored successfully. I'll check to see if I can reproduce
this. I have registered #1995 for this:
https://github.com/jOOQ/jOOQ/issues/1995

> Or can there be a setting to enable
> that behaviour?

I'd prefer if there aren't too many settings complicating things. A
successful store should "refresh" those "original" values, that were
affected by the store()

Cheers
Lukas

[Lukas Eder]

> You're probably right. "original" values should be updated, once a
> record is stored successfully. I'll check to see if I can reproduce
> this. I have registered #1995 for this:
> https://github.com/jOOQ/jOOQ/issues/1995

I can confirm this issue. Other use-cases are affected, too - e.g.
- batch storing
- Record.into(Table)

This is fixed on GitHub master and will be merged downstream to the
upcoming 2.6.1 patch release

Cheers
Lukas

[Ryan How]

Hi Lukas,

I was just thinking about this (I haven't tried it yet from git). What
do you think about the case where multiple records are stored, but an
error occurs part way so the transaction is rolled back. This will mean
that the objects are out of sync with the database (Some will have
updated "original" data, others will not). The only way of getting them
back in sync is to do a "refresh" which will effectively clear out any
uncommitted data. In this case the old behaviour would be preferable,
then after the transaction is successfully committed the records
"original" data can be manually triggered to be set to the records
current data.

Any thoughts?

Perhaps if using batchStore then they can all be stored and then updated
afterwards as a single unit?

I guess there is no way to automatically handle this as JOOQ can't be
sure when the underlying transaction is actually committed? I'm not sure
how this situation is normally handled with optimistic locking and
transactions.

Thanks, Ryan

[Lukas Eder]

Hello Ryan,

> I guess there is no way to automatically handle this as JOOQ can't be sure
> when the underlying transaction is actually committed? I'm not sure how this
> situation is normally handled with optimistic locking and transactions.

jOOQ would have to go very far in order to implement transaction state
listeners that would be triggered on a rollback. I'm thinking about
distributed transactions, where the transactional state is separated
from the JDBC connection and maintained in a
javax.transaction.UserTransaction...

So no, jOOQ can't and probably shouldn't handle this automatically. An
option for you would be this jOOQ 2.7 / 3.0 logic:

public <R extends UpdatableRecord<R>> void myStore(R record) {

// Create a copy with the same changed flag values
int size = record.getFields().size();
boolean[] flags = new boolean[size];
for (int i = 0; i < size; i++) {
flags[i] = record.changed(i);
}

try {
record.store();
}
catch (DataAccessException e) {
for (int i = 0; i < size; i++) {
record.changed(flags[i]);
}

doRollback();
}
}