Re: Optimistic locking in jOOQ

[Lukas Eder]

Hi Sergey,

Let's discuss this on the user group

> Regarding implementation of "Optimistic Locking" in jOOQ.
> It requires new additional select and even row locking.
>
> I thought that "Optimisting Locking" implementation should not do any locks
> and additional selects.

Your solution is better than the one I have currently implemented in
jOOQ *if* you have knowledge about the database schema - i.e. you know
that there is a version/timestamp column. In that case, you can avoid
the round-trip of another SELECT statement and the overhead of
comparing all values.

If jOOQ were to support this too, the version column and increment
algorithm would have to be specified. The API could look like this,
then:

---------------------------------------------
interface UpdatableRecord {
// Regular store
int store();
// Current "optimistic" locking store
int storeLocked();
// Your improved "optimistic" locking store (this could also be
"protected" API)
int storeLockedWithVersion(Field<? extends Number> version);
int storeLockedWithTimestamp(Field<Timestamp> timestamp);

// ... repeat the same API for delete()
}
---------------------------------------------

As you said, using code-generation configurations, the
version/timestamp columns could be designated (e.g. using regular
expressions) and additional utility methods could be generated in
records:

---------------------------------------------
class MyRecord extends UpdatableRecordImpl {
int storeLockedWithVersion();
int storeLockedWithTimestamp();
}
---------------------------------------------

Now those method names are a bit awkward. As the (unreleased)
storeLocked() method hasn't made it into the public API yet, I'm open
to suggestions...
Anyone?

Cheers
Lukas

2012/7/19 Sergey Epik <serge...@gmail.com>:
> Hello Lukas,
>
> Regarding implementation of "Optimistic Locking" in jOOQ.
> It requires new additional select and even row locking.
>
> I thought that "Optimisting Locking" implementation should not do any locks
> and additional selects.
>
> We just define one of the columns (in generator configuration) as "Version"
> or "Timestamp" and do update the following way:
>
> int rows = update some_table
> set some_table.col1 = <new value1>,
> set some_table.col2 = <new value2>,
> set some_table.version = some_table.version + 1
> where some_table.id = <primary> and some_table.version =
> <previous_version_value>
>
> if rows = 0 {
> throw new DataChangedException("...");
> }
>
> What do you think?
>
> --
> Best regards,
>

[Lukas Eder]

... This idea will be tracked as feature request #1591:
https://sourceforge.net/apps/trac/jooq/ticket/1591

It will likely make it into jOOQ 2.5.0

[Sergey Epik]

Hello Lukas,

It would be great to use the same API and use operation store() without any suffixes.

If we define some optimistic locking strategy in generator's configuration (for example, use "version" column strategy), it may be applied in store() method transparently to client.

--
Best regards

[Lukas Eder]

Hi Sergey,

> It would be great to use the same API and use operation store() without any
> suffixes.
>
> If we define some optimistic locking strategy in generator's configuration
> (for example, use "version" column strategy), it may be applied in store()
> method transparently to client.

I don't entirely agree with that. Even if you may have configured a
MY_TABLE.VERSION to be the version column used for optimistic locking,
you don't *always* want to apply optimistic locking on store(). E.g,
some batch job updating an irrelevant column might want to perform
this update, no matter how many times the same record was updated by
users in the mean time.

However, I guess that in those cases, storeLocked() could
automatically assume the behaviour you suggested, though. E.g.

- If no version/timestamp column is defined in the codegen
configuration, then storeLocked() would behave as it does right now
- If a version/timestamp column is defined in the codegen
configuration, then storeLocked() would use that column as you
suggested

[Sergey Epik]

Hello Lukas,

Yes, entity may have columns with different update "cycle", when we do not want to update "version" column.
For example entity "Reseller" (id, name, code, parent_reseller etc.) may be designed with "balance" column.
"Reseller" properties are changed less often than "balance" (it rather case when two entities are mixed in one).
In this case "balance" column can be changed using direct update statement factory.update(Reseller).set(balance, <new value>) etc..
(still not sure that we need special API for optimistic locking).

Both "timestamp" and "version" strategies should care about automatic update of related fields.

It's well known pattern, when entity has "audit" fields (CREATED_TS, CREATED_BY, UPDATED_TS, UPDATED_BY).
These fields contain information when entity created/updated and who is author of these changes.

Not sure, may be these ideas can be combined in one extension point, that allows different strategies: optimistic locking waving and updating audit fields.

--
Best regards,

[Lukas Eder]

Hi Sergey

> In this case "balance" column can be changed using direct update statement
> factory.update(Reseller).set(balance, <new value>) etc..
> (still not sure that we need special API for optimistic locking).

(note, everything said about store() is true for delete() as well...)

I see, so according to you, backend jobs can perform "forced" updates
through explicit usage of the Factory's update() methods, whereas
client code operating on records will always perform optimistic
locking. To be consistent, however, this would mean that storeLocked()
would be removed again, and store() would implement optimistic locking
no matter if "timestamp" / "version" columns have been defined (i.e.
always assuming today's storeLocked() behaviour).

I'm afraid that this might break existing jOOQ client code... Yet, I
understand that 80% of all calls to store() align well with optimistic
locking.
An option would be to add a new Settings property, indicating whether
store() should perform optimistic locking in the absence of a
"timestamp" / "version" column. For backwards compatibility, this
property is set to "false" by default.

> It's well known pattern, when entity has "audit" fields (CREATED_TS,
> CREATED_BY, UPDATED_TS, UPDATED_BY).
> These fields contain information when entity created/updated and who is
> author of these changes.
>
> Not sure, may be these ideas can be combined in one extension point, that
> allows different strategies: optimistic locking waving and updating audit
> fields.

Yes, that is true. This idea was seen on the user group before in this thread:
https://groups.google.com/d/topic/jooq-user/iS3UBATSorA/discussion

And in the thread that originally lead to me implementing optimistic
locking support:
https://groups.google.com/d/topic/jooq-user/bGUYxeNd5X0/discussion

While I'm inclined to say that this is something quite
application-specific, I can imagine that this would be useful to a
great many jOOQ users. Hence, I will track this now as #1592:
https://sourceforge.net/apps/trac/jooq/ticket/1592

So in essence, the following can be said:

- Many tables have a "version" / "timestamp" field used for optimistic locking.
- Many tables have DC, UC, DM, UM fields (D=Date, U=User, C=Creation,
M=Modification)
- NVL(DC, DM) often coincides with "timestamp"
- UpdatableTable would allow to formally access all of these fields.
This access can be configured and generated in jooq-codegen
- If "version" or "timestamp" are provided, store() and delete() would
check for their integrity before actually performing an UPDATE/DELETE
- If DC/DM are provided, these values are set with every INSERT or
UPDATE respectively. Either, System.currentTimeMillis() is used, or
Factory.currentTimestamp()
- If UC/UM are provided, these values are set according to some
configurable Field<String> (e.g. val("Lukas"), or
Factory.currentUser()). I'm not sure yet how to configure this.

Note: Any of the above can also be set using database triggers,
though. Instead of a DataChangedException, triggers would generate a
regular DataAccessException. In that case, jOOQ couldn't provide any
help to users.

I like where this is going...

Cheers
Lukas

[Lukas Eder]

Optimistic locking has been implemented for jOOQ 2.5.0.
https://github.com/jOOQ/jOOQ/issues/1596

It will feature:

- A flag in Settings to activate optimistic locking. Not everyone will
want to profit from this feature. For backwards-compatibility,
optimistic locking is deactivated by default
- No new methods are added. Optimistic locking is implemented in
UpdatableRecord.store() and UpdatableRecord.delete(), as well as
TableRecord.storeUsing() and TableRecord.deleteUsing()
- A new <recordVersionFields/> and <recordTimestampFields/> code
generator configuration properties have been added. These fields allow
for specifying regular expressions similar to <includes/> and
<excludes/> for matching table column names that are to be treated as
auto-generated version/timestamp columns. A table can have version
and/or timestamp columns. store() will always increment those values
on INSERT and UPDATE statements. store() and delete() will check those
values in the executed UPDATE / DELETE's WHERE clauses if the
previously mentioned Settings property is set. This is implemented
according to Sergey Epik's suggestion in this thread here.
- Without any specific code generation configuration, optimistic
locking is implemented trivially by issuing an additional SELECT ..
FOR UPDATE prior to the actual UPDATE or DELETE statement. This
additional statement is used to compare record values in Java. This is
implemented according to Ryan How's suggestion in this thread:
https://groups.google.com/d/topic/jooq-user/bGUYxeNd5X0/discussion
- These new features do not affect regular INSERT / UPDATE / DELETE
statements created from a Factory
- I am currently evaluating whether these new features should affect
Factory.batchStore(), and how

Currently, "version" and "timestamp" values are generated by jOOQ in
Java. More sophisticated "version" types and generation
implementations may be added later, on demand (for instance, generate
"version" values from a sequence, or from any arbitrary Field<?>
expression.

[Lukas Eder]

jOOQ 3.17 has added support for client side computed columns: 

https://github.com/jOOQ/jOOQ/issues/9879

A special case of these computed columns are audit columns, which will also be supported out of the box in jOOQ 3.17:

https://github.com/jOOQ/jOOQ/issues/1592