[
https://issues.apache.org/jira/browse/FINERACT-1946?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Adam Saghy updated FINERACT-1946:
---------------------------------
Summary: Random order of primary key generation at reverse-replaying
multiple transactions on the same day (was: Random order of primary key
generation at reverse-replaying multiple transactions)
> Random order of primary key generation at reverse-replaying multiple
> transactions on the same day
> -------------------------------------------------------------------------------------------------
>
> Key: FINERACT-1946
> URL: https://issues.apache.org/jira/browse/FINERACT-1946
> Project: Apache Fineract
> Issue Type: Bug
> Affects Versions: 1.8.3
> Reporter: Adam Saghy
> Priority: Major
> Fix For: 1.9.0
>
>
> Due to the nature of JPA specification the order of the persistence of the
> managed entities are not defined.
> When multiple entities are reverse-replayed the new entities will be
> persisted not in a defined order hence the IDENTITY primary key generation:
> "{_}Since the provider needs to get the generated ID after each
> {{{}INSERT{}}}, it splits the batch operation to single {{INSERT}} operators
> and fetches the generated ID value after each execution. We just cannot send
> a batch of {{{}INSERT statements and get a batch of generated IDs because we
> won’t be able to associate generated IDs to JPA objects reliably. The reason
> is that database does not guarantee that the order of the generated IDs will
> be the same as the order of INSERTS{}}}. Moreover, {{INSERT}} statements may
> not be executed in the same order as in the batch. So, the only reliable way
> to get IDs for inserted records – split the batch.{_}"
> source: [https://jpa-buddy.com/blog/the-ultimate-guide-on-db-generated/]
> Since the loan transactions are ordered primarily based on transaction id and
> secondarily based on the primary key if multiple transactions with the same
> transaction date got reverse-replayed the order of the transactions might be
> changed and hence not consistent.
> This could be a major problem, however it can be considered as an edge case
> in most situations.
> h3. Solution
> *Step 1* - Ordering should be based on {{transaction date}} + {{creation
> date}} + {{ID}} in this order (For years mysql was used with second
> precision. That implies keeping the ID in the ordering sequence.)
> *Step 2* - Set precision of 6 for DATETIME / TIMESTAMP for Mysql / MariaDB
> (PostgresDB is not effected, by default it is using precision of 6 digits
> after seconds)
> *It should not cause any backward compatibility issues by default, but having
> more precision we can rely on the ordering by creation date time for the same
> day transactions!*
> *Moreover, the* {{LoanTransactionComparator}} *was using* {{creation date
> time}} *as the secondary ordering attribute since 2015!*
> *Acceptance criteria*
> * The order of the transactions shall be consistent after the
> reverse-replaying
> * The order of the reverse-replayed transactions shall match with the
> original order
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
