I have three tables Account, AccountStatus and AccountStatusCodes. AccountStatusCodes is a master table at it has fixed account status codes. Account table will have different account listed. AccountStatus table is a kind of history table, whenever user performs some action on account, the old acount status is updated with flag N and new account status will be inserted. So on each operation on Account the Account status will mantain history with timestamp, user id who updated the status and Y/N flag.
Relationship - Many to many relationship from Account table to AccountStatusCodes code is broken like this 1. One Account can have multiple AccountStatus - Account --> one to many --> AccountStatus 2. One AccountStatusCodes can have multiple AccountStatus - AccountStatusCodes --> one to many --> AccountStatus
JPA Entity code - The actual code is not allowed to share, hence sharing the modifiable code to explain the scenario.
Class: AccountEntity
@DynamicUpdate(value = true)
public class AccountEntity{
private Long accountKey;
//Skipped other variables and getter setters
}
Class: AccountStatusEntity
public class AccountStatusEntity{
@Id
@SequenceGenerator(name = "ACCOUNT_STATUSES_DOCUMENT_STATUSKEY_GENERATOR" , sequenceName = "AS_SEQ")
@GeneratedValue(generator = "ACCOUNT_STATUSES_DOCUMENT_STATUSKEY_GENERATOR")
@Column(name = "ACCOUNT_STATUS_KEY" , unique = true , nullable = false , precision = 10)
private Integer accountStatusKey;
@ManyToOne
@JoinColumn(name = "ACCOUNT_STATUS_CODE_KEY" , nullable = false)
private AccountStatusCodeEntity accountStatusCodeEntity;
@ManyToOne
@JoinColumn(name = "ACCOUNT_KEY" , nullable = false)
private AccountEntity accountEntity;
@Column(name = "CURRENT_STATUS_IND" , nullable = false , length = 1)
private String currentStatusInd;
//skipped other variables and getter setters
}
Class: AccountStatusCodeEntity
public class AccountStatusCodeEntity{
@Id
@Column(name = "ACCOUNT_STATUS_CODE_KEY")
@GeneratedValue(generator = "ASC_SEQ")
@SequenceGenerator(name = "ASC_SEQ", sequenceName = "ASC_SEQ")
private Integer accountStatusCodeKey;
@OneToMany(mappedBy = "accountStatusEntity")
private List<AccountStatusEntity> accountStatuseEntitiess;
}
In the application each user performs some operation on the account and each time the account status gets incremented to next AccountStatusCode and it maintains the history in AccountStatusEntity table by modifying existing status to flag N and inserting new status with timestamp, user id and flag Y.
So there will be two DB operation performed with the @Transaction first one is to update old status to N and insert new status with Y.
The method which does this has following code.
private void moveAccountStatus(final Long accountKey, final String loggedInUserID, final Integer currentStatus,
final Integer nextStatus) {
//Search existing account status with accountKey
// here I have skipped the code which will pull latest status entity from the history table based on date
final AccountStatusEntity accountStatusEntity =
accountDAO.findAccountStatusByAccountKey(accountKey);
AccountEntity accountEntity;
AccountStatusEntity newAccountStatusEntity;
if (accountStatusEntity != null) {
accountEntity = accountDAO.findAccountByAccountKey(accountKey);
accountStatusEntity.setCurrentStatusInd(Constants.NO);
accountStatusEntity.setModifiedBy(loggedInUserID);
accountStatusEntity.setModifiedTs(new Date());
accountStatusEntity.setAccountEntity(accountEntity);
//The update method here is calling the JPA merge() to update the records in the table.
accountDAO.update(accountStatusEntity);
//Create new object of AccountStatusEntity to insert new row with the flag Y
newAccountStatusEntity = new AccountStatusEntity();
//Set the next status
newAccountStatusEntity.setAccountStatusCodeEntity(
(AccountStatusCodeEntity) accountDAO.getById(AccountStatusCodeEntity.class, nextStatus));
newAccountStatusEntity.setCurrentStatusInd(Constants.YES);
newAccountStatusEntity.setCreatedBy(loggedInUserID);
newAccountStatusEntity.setCreatedTs(new Date());
newAccountStatusEntity.setAccountEntity(accountEntity);
//The create() method is also calling the JPA merge() method. The Id is null hence it will consider a insert statement and will insert a new record.
accountDAO.create(newAccountStatusEntity);
}
}
This process works 99% fine in the Production but some times this method is createing duplicate record in the table AccountStatusEntity with the same timestamp, userid and flag Y. The method did not update record with flag N or due to some issue the old record is also getting updated with flag Y.
Table: AccountStatus
___________________________________________________________________________________________________________________
accountStatusKey | accountKey | accountStatusCodeKey | currentStatusInd | Created_TS | Created_BY
___________________________________________________________________________________________________________________
| | | | |
1 | 5 | 3 | Y | A | 4/9/2018
2 | 5 | 3 | Y | A | 4/9/2018
___________________________________________________________________________________________________________________
The above table shows the records which is getting created after the method execution. The accountStatusKey 1 should have the accountStatusCodeKey 2 (the old status code is 2 and next status code is 3) and currentStatusInd N. But somehow the merge method is inserting two records here.
One solution I can make to create unique constraint on the column so that it will avoid this situation but I just wanted to know why the merge method of JPA is creating this issue. Another solution which I have not tried is to use JPA's persist() method at the time of insert instead of merge().
This issue is hard to reproduce in Development environment as 99% time it works, Also users reports this issue very late so that could not trace the log files. As per the logs on dev environment, when the JPA transaction gets executed the insert statement is getting logged first and then the update query is getting logged in the log file. I am not sure how the statement order follows in the Transaction case.
I know the question is too long but I just wanted to give the exact background for understanding this issue. TIA.
