2749478 - OData API User Upsert: SQLException thrown [301] or ORA-00001: unique constraint violated

OData API User Upsert requests failing with either of the below error messages:

1. DAOException caught: SQLException thrown [301]: unique constraint violated: Table(USERS_SYSINFO) . with the index 0
2. DAOException caught: SQLException thrown ORA-00001: unique constraint (SXXX00SFV4_HANAPM00000.UC_USERS_SYSINFO_EXTERNAL_CODE) violated . with the index 0 

• SuccessFactors BizX
• OData API

1. Perform Upsert request to User enitiy to create / update a user within the system
2. Perform the same upsert request again before the intial upsert has finished processing

  Either one of the requests (but most likely the second request) has the potential to fail with one of the below messages :

• DAOException caught: SQLException thrown [301]: unique constraint violated: Table(USERS_SYSINFO) . with the index 0
• DAOException caught: SQLException thrown ORA-00001: unique constraint (XXX00SFV4_HANAPM00000.UC_USERS_SYSINFO_EXTERNAL_CODE) violated . with the index 0 

More often than not, this issue will be experienced when there is 're-try' logic on sender / client side.

For example:

1. If we have a middleware interface / process to update user data in SuccessFactors
2. The middleware is set to re-send the initial request if no response is received from the server / receiver after 60 seconds
3. If the first request has not finished processing in this 60 second window, the request will be sent again
4. The first requests then finishes processing
5. The second requests then attempts to commit the same information (as was committed in first request) to the database again
6. The operation then fails and produces the of the following error messages, via the API (dependent upon your db enviornment)

Hana DB:

• DAOException caught: SQLException thrown [301]: unique constraint violated: Table(USERS_SYSINFO) . with the index 0

Oracle DB:

• DAOException caught: SQLException thrown ORA-00001: unique constraint (XXX00SFV4_HANAPM00000.UC_USERS_SYSINFO_EXTERNAL_CODE) violated . with the index 0 

Check middleware configurations for the presence of a 're-try' command

If you face this issue and you do not know if it is due to a re-try,

Please create a ticket with LOD-SF-INT and provide the following:

• JSesssion / Request / Log ID(s) for the failed request
• The userId of the which you are upserting the data against

Based on this information we can review the server logs to determine if the issue is due to repeat requests

OData API, Upsert, User, Retry, Repeat requests, Unique constraint violated, USERS_SYSINFO, SQLException thrown [301], SQLException thrown ORA-00001 , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , Integrations , Problem