Updating record.original on record.store

[Ryan How]

Hi,

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?

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

(For completeness, this is the sequel of a previous discussion, here:
https://groups.google.com/d/topic/jooq-user/90_oRQYR8Oc/discussion)

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

[Ryan How]

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

[Durchholz, Joachim]

I'm having similar thoughts as Ryan.

Let me ramble a bit; I'm not really sure how well this fits with the direction Jooq is heading. I hope it helps "stepping back from the drawing board" and taking a look from a broader perspective - then maybe it doesn't.
There's also the caveat that I have been unable to follow all the details of the discussion, so I may be missing important details or beating a dead horse. My apologies for that.
That said, I hope it's more useful than distracting. Feel free to ignore.

Tailoring the store operation to a specific use case is making me very nervous; I've been finding time and again that my transaction model doesn't quite fit the model the library designer had in mind.
I suspect that this is partly due to the fact that as soon as you have a cache for the purpose of avoiding unnecessary UPDATEs, you also get optimistic locking for free, so you model the entire transaction model around optimistic locking. Which has the unfortunate side effect that all transaction models that do not use optimistic locking will have less support.

So... I'm thinking about the transactional models I have been seeing and using.

One model is the classic pessimistic locking (PL) model.
It's the one that databases support out of the box; other transactional models are built on top of it.
It's unsuitable for online processing if a transaction can run longer than a handful of seconds.
It's also surprisingly unsuitable for anything that runs longer than about four hours, because it (a) accumulated a gigantic heap of rollback log and (b) if you need to rollback, you usually end up with having a failed batch run and not enough time to retry.
The gist of all this is that while PL is the best, people have always been splitting PL transactions into smaller, shorter transactions, almost since RDBMSes exist.

There's the idempotent transaction model. You run mostly nontransactional, but you make sure that each processing step can be repeated without ill effect, and that failure at any point won't damage the data. Essentially, you read the data but don't worry about whether there are any concurrent updates, and only the insert/update/delete operations need to run in a PL transaction (and sometimes even that isn't strictly required, the RDBMS's internal data structures would probably need transactional protection but writing inconsistent data can easily be corrected by rerunning the transaction).
This works well for all processing steps that transform some constant data into some other, non-overlapping data.
You need tight control over caching for this model: Some data you want to keep cached right at the beginning of the batch run until it is finished (configuration data that doesn't change except under administrator supervision). Some data you do not want to even see a cache: the bulk rows that are streamed through the update process. Some data you want to cache for a while and then throw away: information about a group of data (say, all data relating to a specific building while you're iterating over the data on the wares stored there and collect some statistics).

Another model is the read-process-write with optimistic locking transaction. You read everything in one PL transaction, process the data, and write in a different PL transaction; you apply some clever tricks to fail when this would end overwriting data that has been modified by others.
This is a very hairy approach: Sometimes, you want to fail when just the fields being overwritten have been modified elsewhere (e.g. customer name); sometimes, you want to fail if some other fields in the same record were modified (don't overwrite just the house number if somebody else modified the street name); sometimes, you want to fail if a record in a different table was updated (don't update the new price sum in the order record if some other order detail was modified - you might have to apply a different discount scheme now).
Ultimately, it's the call of the application developer what kinds of changes might have to be checked before an update can go through. Just calling UPDATE with expected old values in the WHERE condition, then counting how many records were actually modified, is an excellent trick, but it's not covering all needs, just the most basic one.

Then there's read-process-read-process-write model with optimistic locking. It's not imposing any additional requirements on the database library I think, it's just that the application programmer needs to be aware that the data he's working with might be outdated.

Then there's the cache-indefinitely case. You read some data such as the list of company locations, which is never going to be modified except under administrator control who'll manually flush any cache and tell J2SE application users to restart their clients so they get a fresh copy of the cache. Automation is possible but runs through extra channels that the DB lib isn't aware of.

Then there's the permanent-sync case. The application side gets notified of changes in any records that it has available locally. This isn't really different than optimistic locking, except that the optimistic locking exception might fly earlier. In the read-process-read-process-write case, it might avoid some exceptions. The downside is that it's entirely nonstandardized and very hard to implement. On the plus side, the entire data movement between application and database is automated and can be optimized; essentially, you're running (or faking, if you will) a database cluster where the application is keeping some records in the form of a Java object.
It's the thing every developer wants and nobody gets :-)

What I'm after is that I wouldn't want Jooq to support a specific transactional model preferentially. This runs the risk of less support for the other models.
It's my main gripe about Hibernate, which works really well for read-process-write-with-optimistic-lock but sucks at every other model. I'm really worried that Jooq is about to enter the same kind of slippery slope, though maybe with a different transactional model.

Just my 0.02 cents.

Regards,
Jo

[Ryan How]

Hi Jo,

It almost seems like an entire project on it's own that has a different
focus than Jooq. Apart from the couple of methods (.store(), .delete()
and .refresh()), then Jooq doesn't really do anything too "smart" (it
only does "awesome"). And I think that works really well, you can use it
just like SQL, but much better integration with Java!. It makes it easy
to learn, work in a predictable manner and just provides benefits!. I
haven't really found a drawback yet, you can even just resort back to
executing SQL if you want!, or part of a statement in SQL.

So it is like the Jooq CRUD API (I'm not quite sure what to call this
part of Jooq, is that a good name?), gives a little taste of a whole new
world of possibilities that can be built on top of JOOQ. Maybe it should
come under a different project and then can be built and maintained
separately. But it seems it still needs to tightly integrate with JOOQ too.

It almost seems if it was going as far as to provide support for all the
different transaction models listed, it might as well handle validation
and plugging in other things, such as logging changes (audit trail) and
things like that? Why not put a cache in there too so then you can have
the permanent-sync case.

I guess what I mean is, we seem to be thinking down a path that in
distinctly different from the core Jooq focus and it would seem
necessary to draw a line somewhere. If the core Jooq could provide the
necessary hooks and database integration, then these things could be
plugged in to support any desired use case without Jooq itself becoming
too specific to one model.

On a side note, does Jooq and the "crud" operations in any way remind
you of Linq and Ado.net ?, I haven't used them for a while, but the
ADO.net datatable is a bit like the Jooq Result and Record objects,
where you can do changes, it tracks those changes, then you can store
the result. Except in ADO.net you work on a "table"/"view" level rather
than record level. Anyway, 2 separate projects...

Thanks, Ryan

[Lukas Eder]

Hello guys,

- jOOQ's main mission is to model SQL as an internal domain specific
language in Java, and to ease the "JDBC pain".
- jOOQ's secondary mission is to model a database's tables and records
and other artefacts in a typesafe way.

Now, the "jOOQ CRUD API" is a nice side-effect of having an
active-record or ADO.net like model of the underlying database tables.
This model does act like a cache, just like JDBC ResultSets, JPA
entities, .NET EntityFramework entities and many other database
resource models "cache" data from the underlying database.

Now, I completely agree that jOOQ shouldn't assume or even impose a
transactional model from the many possible models. This is why I said
that these things have to be done very carefully - if they're going to
be done at all. I think that improving the CRUD API by adding
transaction listeners could be a viable way to solve Ryan's problems
in the future. But just having a couple of "awesome" (thanks Ryan)
features is not a compelling enough reason to introduce this kind of
complexity. Over the time, I'm collecting use-cases for introducing a
generic transaction model, but it is certainly not a priority for jOOQ
3.0, 3.1 and other early 3.x versions, as I don't have a clear view on
the problem domain, yet.

As for optimistic locking, I do agree with Joachim. I'm not 100%
convinced that it is implemented in the right way (yet, i.e.
generically covering all use cases). It covers a lot of use-cases,
though. And the ones that are missing can probably be added in the
future.

> [Ryan] I guess what I mean is, we seem to be thinking down a path that in

> distinctly different from the core Jooq focus and it would seem necessary to
> draw a line somewhere

Yes, drawing a line is tough, as the short-term added value for users
is often very tempting. Another area where this gets challenging is
the mapping of Result / Record to POJOs.

The point here is: jOOQ may always need to draw new lines. While the
main mission is very clear and finite in scope, the secondary mission
is not. It is open-ended and it should be debated again and again on
this user group. jOOQ's internals have a very high degree of cohesion,
meaning that all the features closely interact together. This is a
good sign, and an effect of having added features slowly, after
careful consideration (features, which aren't low-hanging fruit). Be
assured that a transaction model will be added *very* slowly, if it is
added at all.

> [Ryan] Except in

> ADO.net you work on a "table"/"view" level rather than record level. Anyway,
> 2 separate projects...

Funny, I had just recently thought about .store(), .delete(),
.refresh() methods on Result, performing batch operations under the
hood... Do you see anything worth implementing in jOOQ? Note, that
updatable results are on the roadmap:
https://github.com/jOOQ/jOOQ/issues/1846

Other nice ideas?

Cheers
Lukas

2013/2/24 Ryan How <rh...@exemail.com.au>:

> --
> 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 jooq-user+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Ryan How]

Hi Lukas,

Slow and lots of consideration sounds like a good idea.

I can't remember enough of ADO.net to remember it in much detail :). I
just remember liking the "DataTable" object, it was like having an
in-memory copy of a view of the database that you could "sync" back
after you had changed. Quite a simple concept, but very powerful. I'm
just always surprised to have never found a Java "DataTable". I guess
because JOOQ knows all the keys and references, is typesafe and has
"Tables" in Java, it is half way there to the DataTable.

Anyway, I'm a bit out of touch, I haven't been working on enough
different projects lately to remember all the good and bad points of the
various technologies :)

Thanks, Ryan

[Lukas Eder]

> Slow and lots of consideration sounds like a good idea.

OK then. That means that for now, you'll have to store "changed" flags
and "original" values externally somewhere in your application, once
you migrate to jOOQ 3.0

> I can't remember enough of ADO.net to remember it in much detail :). I just
> remember liking the "DataTable" object, it was like having an in-memory copy
> of a view of the database that you could "sync" back after you had changed.
> Quite a simple concept, but very powerful. I'm just always surprised to have
> never found a Java "DataTable".

Well, a JDBC ResultSet is a "DataTable" in a way. Only, the API is
really very awkward, as you have to position an index in a cursor
first, every time you want to modify a cell at position (x, y)

> I guess because JOOQ knows all the keys and
> references, is typesafe and has "Tables" in Java, it is half way there to
> the DataTable.

I see, yes that sounds much like what I've had in mind recently.
org.jooq.Result could work in an "lazy" and "eager" sync mode.

- "lazy" means that you have to explicitly "flush" changes to the DB.
This probably includes List operations, such as Result.remove(), which
have to be flushed as delete operations. I'll have to think about this
again. There are lots of corner-cases, I guess. I have now registered
this as https://github.com/jOOQ/jOOQ/issues/2265
- "eager" means that you have to fetch a Result using
fetchUpdatable(). This would set the JDBC ResultSet.CONCUR_UPDATABLE
flag, allowing for creating an "online" JDBC ResultSet / cursor, where
the various ResultSet.updateXXX() methods can be used internally, by
jOOQ, every time a record from this result is changed. This is already
registered as https://github.com/jOOQ/jOOQ/issues/1846

I'll do some further reading in the MSDN docs, to see what this
DataTable is all about.

> Anyway, I'm a bit out of touch, I haven't been working on enough different
> projects lately to remember all the good and bad points of the various
> technologies :)

Well anyway, it's good to bring those impressions here :-) There's
always some good idea to take inspiration from

Cheers
Lukas

[Ryan How]

With the DataTable, you can also run queries on it, sort it, define
parent child relationships (aka Foreign Key) with other data tables, it
stores history of changes, create views of it, have listeners on data
change (I think). It is like an in memory database pretty much. I think
the similarities to a JDBC result set end at the idea of having rows and
columns.

I don't think a DataTable works in "eager" mode. It works offline, a
changeset is calculated and applied to the database.

Bear in mind I haven't used this for about 6 years :), but I often find
a lot of good ideas in the .Net world. Even if my preference is Java.

Regaring the .remove() functionality you mentioned, I've implemented
something similar in my project where records not yet in the database,
if .remove() is called they are just discarded client side. If they are
in the database, then the .remove() when flushed will execute a DELETE
on the database (first with an optimistic locking check). This kind of
functionality built in would be great. It would save implementing all
the logic and "tracking" my records.




Thanks, Ryan

[Lukas Eder]

> With the DataTable, you can also run queries on it, sort it, define parent
> child relationships (aka Foreign Key) with other data tables, it stores
> history of changes, create views of it, have listeners on data change (I
> think). It is like an in memory database pretty much. I think the
> similarities to a JDBC result set end at the idea of having rows and
> columns.

I see. Well, I have no plans just yet, of implementing an in-memory
database cache ;-) I guess, in the whole LINQ world, blending
in-memory C# / VB.NET tables with actual SQL Server tables starts
making sense, though.

> I don't think a DataTable works in "eager" mode. It works offline, a
> changeset is calculated and applied to the database.

Yes, good luck with concurrency, there :-)

> Bear in mind I haven't used this for about 6 years :), but I often find a
> lot of good ideas in the .Net world. Even if my preference is Java.

Yes, I agree.

> Regaring the .remove() functionality you mentioned, I've implemented
> something similar in my project where records not yet in the database, if
> .remove() is called they are just discarded client side. If they are in the
> database, then the .remove() when flushed will execute a DELETE on the
> database (first with an optimistic locking check). This kind of
> functionality built in would be great. It would save implementing all the
> logic and "tracking" my records.

Sounds good. Although you'll still have to track *some* data, because
of jOOQ being unaware of transaction rollbacks...

Cheers
Lukas

[Ryan How]

Hi Lukas,

Related issue, but not the same as I had started with...

I'm using MVCC mode in the database. So as my understanding has it, the
optimistic locking may not work quite as expected. If the writing of the
records happens in concurrent transactions, then each will write
successfully (as they are reading their own version of record), but on
commit the (2nd?) transaction will throw an exception because the
underlying database will not accept the commit. Which is fine, it still
means it did it's job, but it will be a different exception to the JOOQ
optimistic locking exception.

Or so is my understanding of how MVCC works.

Just thinking it adds to the reasons not too put extra "smarts" into
JOOQ. I could turn MVCC off, but I pretty much rely on it to avoid
deadlocks.

Also, with MVCC on, the FOR UPDATE on the optimistic locks is not really
needed. It just adds potential for deadlocks for me :). Tables are
always locked in the same order (well they should be!), so it should be
fine.

I just wanted to mention it as I thought it relevant to the discussion.




Thanks, Ryan

[Lukas Eder]

Just thinking it adds to the reasons not too put extra "smarts" into JOOQ. I could turn MVCC off, but I pretty much rely on it to avoid deadlocks.

This seemed like such a low-hanging fruit. But the more we think about it, the more I'm inclined to deprecate optimistic locking entirely...

[Ryan How]

Yep. It only works correctly if the conditions are "perfect" :). But I sure will miss it! I'm highly considering creating a local fork of jooq and customising some bits to suit my needs. That way I only have to consider my immediate use case.

You'll have to explain the low-hanging fruit metaphor.

Thanks, Ryan

On 1/03/2013 1:39 AM, Lukas Eder wrote:

Just thinking it adds to the reasons not too put extra "smarts" into JOOQ. I could turn MVCC off, but I pretty much rely on it to avoid deadlocks.

This seemed like such a low-hanging fruit. But the more we think about it, the more I'm inclined to deprecate optimistic locking entirely...

[Lukas Eder]

Yep. It only works correctly if the conditions are "perfect" :). But I sure will miss it! I'm highly considering creating a local fork of jooq and customising some bits to suit my needs. That way I only have to consider my immediate use case.

Don't worry. I won't remove it immediately. Anyway, not before jOOQ 4.0... Maybe there is a good solution, still. So let's wait with action, for now.

 

You'll have to explain the low-hanging fruit metaphor.

Well, jOOQ *seems* to have all the necessary information for a simple implementation of optimistic locking. But in fact, it doesn't because jOOQ knows nothing about surrounding transactions. As locking is always tightly coupled with transaction handling, what looks like a low-hanging fruit (easy to implement) is in fact pandora's box... Get it? :-)

Cheers

Lukas