Hi Ryan, (For completeness, this is the sequel of a previous discussion, here: https://groups.google.com/d/topic/jooq-user/90_oRQYR8Oc/discussion)
> This issue https://github.com/jOOQ/jOOQ/issues/1995 is creating an issue for > me which I am not sure how to work around. > > My use case is. > > 1. I am using optimistic locking > 2. I am storing a bunch of objects in a transaction. > 3. An error occurs part way through - transaction rolled back > 4. Error fixed, try saving again. > 5. Get a DataChangedException > > The issue is that record.original was updated, but then the data was rolled > back. > > I can't figure out a way how to store the record again, so the data is lost. > Any ideas? > > I preferred before #1995 was applied, then I could manually refresh the > records at the end of the transaction. Then this had no issues. Is there a > way to disable the updating of original values for optimistic locking? Of course, #1995 was implemented because already before, your workflow didn't quite work out :-) Looks like we will continue hitting walls with jOOQ's CRUD behaviour... I like the thought that a record's changed/original state is non-transactional. The main reason for this is the fact that it is supposed to be loaded within the same transaction that stores it. Hence, updating the "changed" and "original" values is of the essence, if you want to execute things like these (pseudo-code): R rec = fetchOne(T); rec.setValue1(1); rec.store(); // UPDATE t SET value1 = 1 WHERE [t.id = xx] rec.setValue2(2); rec.store(); // UPDATE t SET value2 = 2 WHERE [t.id = xx] On the second update, it is essential that rec.original(value1) has been updated to 1. Otherwise, the second update would fail due to an incorrect implementation of optimistic locking. So the fix in #1995 was correct from this point of view. Of course, when using optimistic locking, the record's internal state will escape the transaction and enter a new one with "original" values from the previous transaction (hence the need for optimistic locking). Just to be sure that we're discussing about the same things. Here's a summary of the requirements: - jOOQ records should know when the primary key has "changed". This allows for distinguishing INSERT and UPDATE statements on the store() operation. - jOOQ records should store() only "changed" values. This allows for making use of DEFAULT values when inserting. - jOOQ records need to keep track of "original" values in order to implement optimistic locking (when not using timestamp or version columns, but your current problem would also appear when using those features) - Within a transaction, successful storing should lead to the updating of "original" values, in order for records to be storeable several times in the same transaction when optimistic locking is applied So with these requirements and reasoning behind them, I'm not sure if there is an elegant solution to revert the "original" values in case of a transaction rollback, unless jOOQ would start handling (or at least listening) to transactions itself. To get this entirely correct, there would be a couple of things to consider: - JDBC's Savepoints would need to be supported as well - JTA's UserTransaction and Synchronization events would need to be supported as well - Spring's transaction listener lifecycle would probably need to be supported as well - more? Things get complex here. I'd love to go into supporting transaction events in jOOQ eventually. But these things have to be done very carefully. I guess that a jOOQ 2.x Factory / jOOQ 3.x Executor would need to be able to register a TransactionListener that can be implemented by client code. If you're using jOOQ with standalone JDBC connections, then jOOQ could be able to intercept JDBC transaction events itself. There are two ways to implement this, which come to my mind: 1. jOOQ could maintain its own UNDO / REDO log. Every record would have to know the state of its "changed" flags and "original" values at every Savepoint in the transaction, in order to be able to roll back those values to the way they were at that time. This could work, but it means a lot of overhead. 2. jOOQ could try to rely on the database to provide the "original" values at the time of any Savepoint. However, this would a) not help bringing back "changed" flags b) possibly lead to race conditions, as the new "original" values might no longer be the ones they were when the rolled-back transaction was started Anyway, I have a feeling that the reward of going into these topics has to be higher than just that of getting optimistic locking right. Are there many other compelling use-cases that make adding the complex topic of transaction support interesting to jOOQ? Or is there a simpler way to roll back "changed" and "original" values, safely? Or is the optimistic locking feature maybe out of scope for the jOOQ library? Cheers Lukas -- 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/groups/opt_out.
