Hi Lukas,
Yes I think the whole transaction listeners, etc is getting a bit
complex. It seems the smarter the .store() method is getting the more
subtle problems happen in different use cases.
In JOOQ 3 I could update the changed and original data manually, which I
think is a great addition. But as of 2.6.1 there isn't really a
workaround. So I'll stick on 2.6.0 until I upgrade to 3.
I absolutely love the record.store() and record.delete() methods, but it
feels like to support all the different use cases that it needs to be
very customisable. Just having the standard actions fits most use cases,
but when it doesn't fit you are unable to change it.
For example, sometimes I create a new record, but if it is "empty" then
I don't want to store it yet, so I'd like record.store() to not do
anything (now I can't remember if that is what happens or not...?).
Sometimes I create a new record and then set a foreign key on it so it
is "ready to go". But then I don't want a call to .store() to save it
because it hasn't been populated with any user data. If you imagine
these records are sitting behind a UI with a page of blank records, then
I only want the ones that the user has filled in to be saved, then you
can see how it might be useful. Otherwise I need to go through and check
if "user" fields have been entered (as opposed to "system" fields), or
copy the UI values over to the JOOQ data objects, it all makes for more
code that if I can add extra behaviour into this one record object, will
take care of all of it for me :).
The more I write, the more I feel what I am doing is too specific to my
application to be implemented in a general way. But what I like about
JOOQ is the simplicity in how it integrated with the application. I can
execute an SQL query, get a type safe object that represents the record,
do any changes and call .store(). Now if it has been changed inbetween,
it has optimistic locking built in!, Great!. Just saved heaps of
boilerplate code. In fact the whole process is just a couple of lines of
code, and I can't forget to check for optimistic locking because it is
built it!. I just want to keep building more and more in!. I want it so
that when I update the record, my UI is automatically updated too!. But
where I could normally extend an object and get my own functionality in
there, I can't with JOOQ, because it already extends that base object
and creates the record specific versions. I can wrap it in another
object, but then I lose the record specific types and have to work with
a generic API, like setValue(Field, value) instead of setMyField(value).
But I might just be wanting too much from a library :). I think if I was
maintaining the library I wouldn't put most of these things in because
they are too specialised, add complexity, slow down other operations,
etc. It's almost like it needs to be pluggable, so custom functionality
can be plugged in. Opening up the internals for JOOQ 3 is great because
it allows for more customisation.
Anyway, I keep getting off topic!
Thanks!
Ryan
On 21/02/2013 6:34 PM, Lukas Eder wrote:
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.
