In our application, we have three tables dps_user, account, and profile for building user profile. The profile table has account_id as a foreign key. Based on this design, userprofile.xml is written. account_id is mapped as an item-type in xml which is working fine for one-to-one mapping. Every single account will have a separate profile.
Now we have a scenario one-to-many mapping (One profile associated to multiple accounts). We have a new table profile_account for this which has the mappings as below.
NEW_ID ACC_NO PROFILE_ID
====== ====== ==========
001 001 001
001 002 001
002 001 002
002 002 002
By default Account: 001 will be mapped to Profile: 001. Post-login we will get the new_id. With this value, we will fetch accounts and user will one of the accounts in the UI.
If user selects Account: 002 with new_id, then we have to update the profile with ACC_ID: 002. To achieve this, we retrieve the account item first and then update the corresponding profile item.
When doing this, we are getting below exception often and update is failing.
Error : java.sql.SQLException: java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock
Sometimes the update is successful in profile table but the old account is getting deleted from account table during this update. We have the cascade as insert,update,delete for account_id in profile table.
userProfile.xml:
<item-descriptor name="user" item-cache-size="3000" item-cache-timeout="900000" query-cache-size="1000" query-expire-timeout="900000">
<table name="PROFILE" type="auxiliary" id-column-name="user_id">
<property name="account" display-name="Account" column-name="ACCOUNT_ID" item-type="account" cascade="insert,update,delete" />
</table>
</item-descriptor>
<item-descriptor name="account">
<table name="ACCOUNT" type="primary" id-column-names="ID">
<property name="accountNo" data-type="string" column-name="ACC_NO" />
</table>
</item-descriptor>
