Optimistic Locking

[Ryan How]

Hi,

Is there any way for JOOQ to handle optimistic locking automatically?

So given an updateable record something like the following:

MyRecord r = .... // some query
r.setValue(.....) // do some stuff
r.storeWithCheck(); // throws an exception if has been updated by another connection

Rather than adding a version column it could do a select on the record and if the initial values are different then throw the exception.

Or is there another approach to achieve this?

Thanks! Ryan

[Lukas Eder]

Hello Ryan,

> Is there any way for JOOQ to handle optimistic locking automatically?

There is currently no automatic way of dealing with optimistic locking.

> Rather than adding a version column it could do a select on the record and
> if the initial values are different then throw the exception.

That sounds like a reasonable approach. I guess, in order to avoid
race conditions, the select would have to be a SELECT ... FOR UPDATE.
I'll register this as feature request #1547:
https://sourceforge.net/apps/trac/jooq/ticket/1547

> Or is there another approach to achieve this?

Right now, I could imagine that you'll be able to implement this on an
ExecuteListener level. Prior to executing an UPDATE, you could
manually issue the necessary select statement and throw your own
custom RuntimeException. The relevant section in the manual and
Javadocs are these:

- http://www.jooq.org/manual/ADVANCED/ExecuteListener/
- http://www.jooq.org/javadoc/latest/org/jooq/ExecuteListener.html

Cheers
Lukas

2012/7/9 Ryan How <rh...@exemail.com.au>:

[Ryan How]

Thanks Lukas!

I'll see if you get to it in your next release before I've implemented it in my web app :).

JOOQ is fantastic :).

Cheers, Ryan

[Lukas Eder]

Hi Ryan,

> I'll see if you get to it in your next release before I've implemented it in
> my web app :).

I was planning the inverse: Wait and see if you implement it and then
integrate a contribution :-)

> JOOQ is fantastic :).

Thanks!

Cheers
Lukas

[Ryan How]

It will be a few months at my end. Although a much needed feature for
data consistency, it isn't as high priority as a few other things. But I
would be happy to make a contribution once done and make JOOQ even more
fantastic :)

[Lukas Eder]

> It will be a few months at my end.

OK. That raises chances of me getting this implemented before you :-)

Cheers

[Lukas Eder]

>> It will be a few months at my end.
>
> OK. That raises chances of me getting this implemented before you :-)

... which I did. Implementing optimistic locking in jOOQ was quite simple.
Here are a couple of characteristics of this change:

- There are now UpdatableRecord.store() and storeLocked()
- The new name was chosen to indicate that client code may assume to
have "optimistically locked" the underlying record
- The only difference between the two is that storeLocked performs an
additional SELECT .. FOR UPDATE to check for changes. The "pessimistic
locking" FOR UPDATE clause is needed to avoid race conditions between
the SELECT and the subsequent UPDATE. FOR UPDATE may fail early, if
another transaction locked the database record in the mean time. This
is desired.
- SELECT .. FOR UPDATE is supported in most databases. In CUBRID and
SQL Server, jOOQ simulates it using JDBC cursors
(TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE). I have recently blogged
about this here:
http://blog.jooq.org/2012/05/18/for-update-simulation-in-sql-server-and-cubrid/
- In SQLite, FOR UPDATE is omitted as there is no way to simulate it.
Race conditions can occur.
- If the selected record's values don't correspond to the original
values of the record being updated, a new (unchecked)
DataChangedException is thrown. It is also thrown if the underlying
database record has been deleted in the mean time.

So, this is implemented on GitHub master, and will be included in the
next jOOQ 2.5.0-SNAPSHOT version.

Cheers
Lukas

[Ryan How]

Thanks Lukas,

That's fantastic. I'll be sure to give it a good work out :).

Cheers, Ryan

[Lukas Eder]

> That's fantastic. I'll be sure to give it a good work out :).

Great, any pre-2.5.0 release feedback is helpful, as it is easier to
change the API and semantics before actually releasing it. The
SNAPSHOT can be found here:
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

Note, I am thinking that there should also be an
UpdatableRecord.deleteLocked() method with similar semantics. Using
deleteLocked():

- An exception should be thrown if the database record was already deleted
- An exception should be thrown if the database record has been
changed before deletion

What do you think?

[Ryan How]

I hadn't really thought about deleting yet (I'm deciding what kind of
audit trail approach to take, whether to mark a record as deleted or
actually delete and copy to some kind of audit log). But it would round
off the feature, so I think that would be good to include.

[Lukas Eder]

> I hadn't really thought about deleting yet (I'm deciding what kind of audit
> trail approach to take, whether to mark a record as deleted or actually
> delete and copy to some kind of audit log).

I had thought about this many times. Audit trails / data history is
something that is done very frequently. It leads to a big amount of
boilerplate, especially when complex data (i.e. normalised data across
several relations) needs historisation.

I wonder if this is something that could be handled by jOOQ - or if
this goes too far into implementation-specific stuff. I'd be
interested if you could share your experience, once you have decided
which approach to take...

> But it would round off the
> feature, so I think that would be good to include.

Done.

[Ryan How]

.storeLocked() works very nicely :). So simple, just add 6 letters and
suddenly a whole better level of data integrity :)

I was planning on implementing the audit trail through a JOOQ listener.
I'm not sure how yet (Like you said with complex data it becomes...
complex), but at least I can listen for all data changes and then handle
the audit trail in a single spot rather than through a bunch of DAOs.

The other thing I am thinking about to pre-empt the optimistic locking
is an automatic change detection (through the form of a periodic
refresh, or perhaps a listener, but that wouldn't detect non-jooq
updates or work in a cluster or anything). But that is probably getting
too application specific to be part of JOOQ.

In any case I'll let you know if I come up with some nice JOOQ extentions.

Thanks for your great work.

[Lukas Eder]

> .storeLocked() works very nicely :). So simple, just add 6 letters and
> suddenly a whole better level of data integrity :)

Great!

> I was planning on implementing the audit trail through a JOOQ listener. I'm
> not sure how yet (Like you said with complex data it becomes... complex),
> but at least I can listen for all data changes and then handle the audit
> trail in a single spot rather than through a bunch of DAOs.

Yes, those ExecuteListeners seem to have been a very good addition for
many users. Sounds reasonable to use them for simple generic audit
trails, as well. Maybe, you could use Configuration.getData() /
setData() to communicate with your AuditListener:
http://www.jooq.org/javadoc/latest/org/jooq/Configuration.html#setData%28java.lang.String,%20java.lang.Object%29

That way, you could create your own custom "audit-enabled" Factories.

> The other thing I am thinking about to pre-empt the optimistic locking is an
> automatic change detection (through the form of a periodic refresh, or
> perhaps a listener, but that wouldn't detect non-jooq updates or work in a
> cluster or anything). But that is probably getting too application specific
> to be part of JOOQ.

Sounds like a cache with invalidation mechanisms. jOOQ could have
ResultQuery.fetchLive() methods, which would register fetched "live"
records in a LiveRecordManager. This manager would maintain
WeakReferences to all of those "live" records, in order to
periodically refresh them according to custom configuration
parameters. "Live" records could also allow to register listeners that
will be notified upon refresh.

Apart from the fact that this will be non-trivial to implement
correctly for many user-site platforms (with all the required
synchronisation and thread-safety and performance-tuning), it might be
useful to some jOOQ users.

I'll track this idea as long-term feature request, #1581:
https://sourceforge.net/apps/trac/jooq/ticket/1581

> In any case I'll let you know if I come up with some nice JOOQ extentions.

Great, thanks!

Cheers
Lukas

[Lukas Eder]

>> .storeLocked() works very nicely :). So simple, just add 6 letters and
>> suddenly a whole better level of data integrity :)
>
> Great!

The latest version on GitHub now uses a Settings property to indicate
that jOOQ should use optimistic locking. This is documented here:
https://github.com/jOOQ/jOOQ/issues/1547

The unpublished storeLocked() and deleteLocked() methods have been
removed again. This will keep the API more concise and better aligned
with what will be implemented soon, after Sergey Epik's suggestions in
this thread (optimistic locking using "timestamp" or "version"
columns):
https://groups.google.com/d/topic/jooq-user/OO8TMx07ZCU/discussion

[Ryan How]

Sounds good. And thanks for the update so I'm not wondering what
happened next time I update the JOOQ library :). I was a little
concerned with the naming of the method, but couldn't think of anything
better. Timestamp and version is also great, a more standard way of
optimistic locking and should be more efficient than selecting the
entire record.