Default JDBC lock repository does not use time zone when storing creation date, causing "lock"

[lucasvc]

In what version(s) of Spring Integration are you seeing this issue?
5.5.13.RELEASE

Describe the bug
The default JDBC lock repository allows locking threads/instances using database. At table <prefix>LOCK column CREATED_DATE avoids deadlocks, but that time is stored without time zone knowledge, so when last acquirer has a higher time and it drops the lock, the other members won't get the lock until they reach the same time.

To Reproduce

• Start a first leader-election Spring application using JDBC lock, within a machine with a "higher" time zone, i.e. a CEST (which as for today is +02:00).
  • Confirm that <prefix>LOCK.CREATED_DATE is being updated with the local date time.
• Start a second leader-election application, within a machine with a "lesser" time zone, i.e. UTC.
  • If this application takes the lock, shutdown and start it again until does not.
• Shutdown first application.
• Second application does not take lock.

Expected behavior
Second application should take lock after the first is shutdown.

Sample
// working on it

Workaround
The table creation SQL scripts use (most of them) the type TIMESTAMP. But SQL also defines the type TIMESTAMP WITH TIME ZONE, that at least Oracle supports it. If the column uses this type the issue is not reproducible anymore.

[artembilan]

Yes, you just can modify an SQL script we provide for your data base.
Those are present as samples how DB structure can be: any compatible modifications are possible.

I also checked the logic of that DefaultLockRepository with a LocalDateTime.now(ZoneOffset.UTC).
Does it make sense as a fix?

[artembilan]

I opened Pull Request with a possible fix: #3886.
But I'm not sure if it is correct (or perfect): I'm not so good with java.time API.
This LocalDateTime says in the JavaDoc:

A date-time without a time-zone in the ISO-8601 calendar system, such as 2007-12-03T10:15:30.

But then my tests show some discrepancy, therefore I use an explicit UTC timezone:

> println LocalDateTime.now()
> println LocalDateTime.now(ZoneOffset.UTC)
2022-09-08T13:53:09.529393
2022-09-08T17:53:09.529393

I'm opened for any suggestions.

Thank you!

[lucasvc]

I opened Pull Request with a possible fix: #3886. But I'm not sure if it is correct (or perfect): I'm not so good with java.time API. This LocalDateTime says in the JavaDoc:

A date-time without a time-zone in the ISO-8601 calendar system, such as 2007-12-03T10:15:30.

That is, without a time-zime will use your local-machine time-zone.

But then my tests show some discrepancy, therefore I use an explicit UTC timezone:

> println LocalDateTime.now()
> println LocalDateTime.now(ZoneOffset.UTC)
2022-09-08T13:53:09.529393
2022-09-08T17:53:09.529393

I'm opened for any suggestions.

Thank you!

I see that perfect!