[FINERACT-1946] Random order of primary key generation at reverse-replaying multiple transactions

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.8.3
• Fix Version/s: 1.9.0
• Labels: PepperSoup
• Assignee: Adam Saghy
• Reporter: Adam Saghy
• Created: 22/Jun/23 10:02
• Updated: 07/Jan/24 10:53
• Resolved: 28/Jul/23 13:43

Description

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.

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

Issue Links

• is a child of: FINERACT-1874 Release Apache Fineract 1.9.0
• links to: GitHub Pull Request #3328