2741801 - OData API User Upsert: Parallel Upserts to User Entity Failing

You are performing Parallel / Concurrent OData API upserts to the user entity, to either create new users, update existing users (or both)

You observe that your API upserts are failing with some / all of the below error messages:

• UserEJBException: DAOException caught: SQLException thrown No more data to read from socket 295
• UserEJBException: DAOException caught: SQLException thrown ORA-00028: your session has been killed 301
• UserEJBException: DAOException caught: SQLException thrown ORA-00060: deadlock detected while waiting for resource 1
• UserEJBException: ServiceApplicationException caught: Wrapped Exception: Wrapped Exception: Wrapped Exception: Wrapped Exception: 70

• SuccessFactors BizX
• OData API  

To reproduce this issue, the following can be done:

1. Perform parallel API upsert calls to the User entity  
2. Update two or more users to report to the same manager (using parallel API requests)
3. You will observe that your upserts failing intermittently with some / all of the above errors (outlined in symptom section)

The cause of the user upserts failing with the above messages, is most likely due to the Manager updates.

User 1 is assigned Manager A
User 2 is assigned Manager A
User 3 is assigned Manager A

When we update two or more users to have the same manager in a parallel fashion, the following happens at a database level, when committing the changes:

When User 1's record is updated, the record of Manager A is locked (as the manager record must be updated also e.g. directReports)

When the transaction to update User 2's record is executed, it attempts to access Manager A's record also (which is currently locked by the transaction for User 1)

The same will happen to User 3 when the transaction is executed and every subsequent transaction that follows, that is assigning the same manager.

Both User 2 & User 3 will be waiting for the record of Manager A to become unlocked.

(the transactions are essentially competing with each other for access to Manager A's record)

The above described scenario results in deadlock occurring within the database

The deadlock lengthens the transaction time

Long-running transactions of this nature are blocked by our servers

1. There is no resolution for this currently.
2. This is an uncommon use-case and currently we recommend not updating user's managers in parallel
3. We are currently working to enhance the system to accommodate this use case

OData API, Parallel, Manager, User, Upsert, EJBException, ORA-00028, ORA-00060, Wrapped Exception, No more data to read from socket , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , Integrations , Problem