Is your feature request related to a problem? Please describe.

I am using Javers in a multi-tenant application, and my multi-tenancy strategy is database-per-tenant meaning each tenant has their data stored in a completely isolated database. I'm running Spring Boot, and to manage multiple databases I'm using an implementation of AbstractRoutingDataSource. The problem I am running into with Javers is while generating commit_pk in SequenceAllocation.java, the nextValue method uses some sort of local caching - I am pasting a code snippet below.

synchronized long nextValue(SequenceNextValQuery sequenceNextValQuery, Transaction transaction) throws SQLException{
        if (recalculationNeeded()) {
            long currentSequenceValue = sequenceNextValQuery.queryForNextVal(sequenceName, transaction);
            recalculate(currentSequenceValue);
        }
        return nextLocalValue();
    }

long nextLocalValue() {
      if(recalculationNeeded()) {
          throw new SequenceLimitReachedException("Sequence " + sequenceName + " has reached its limit of " + currentLimit + ". "
                  + "Before fetching value, check if recalculation is needed using recalculationNeeded() method.");
      }
      currentValue++;
      return currentValue - 1;
}

This behavior assumes there is only one underlying database. Here’s where it breaks in a multi-database setup:

• Suppose a request comes in and writes to DB1. Javers fetches the current sequence value as 4 and starts issuing IDs like 401, 402, etc.
• A subsequent request writes to DB2, where the current sequence value is 100. However, due to the cached local value, Javers continues issuing IDs like 403, 404, which may clash with existing commits in DB2, breaking audit integrity.

Describe the solution you'd like

I ran into a similar problem with global ID caching, which was fixed by the sqlGlobalIdCacheDisabled flag. I want to do a similar implementation here, something like commitPkCacheDisabled. In the nextValue method, I will check if the cache is disabled, and if so then I will run queryForNextVal(...) ignoring the local sequence value. This will lead to a extra query for each write, but sequence select queries are very fast and the performance hit should be minimal.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
I believe this has been discussed previously as mentioned below - happy to implement it and contribute a PR.

Hello @bartoszwalacik , it's available on last release ?

We have a multitenant microsevice with 3 distinct db, but when I switch to another tenant , the cache sequence javers it's a problem.

It's possible to disable the cache ?

Originally posted by @nseb in #1378