Tables are getting locked and are receiving an ORA-00054 Error in Risk Authentication

Products

• CA Rapid App Security
• CA Advanced Authentication
• CA API Gateway
• CA Risk Authentication

Issue/Introduction

Our Tables are getting locked in Oracle and we are receiving an ORA-00054: resource busy and acquire with NOWAIT specified, why would Riskminder lock these tables?

Environment

Release: 9.x
Component: Risk Authentication

Resolution

Riskminder does not lock the table but it does have to lock the entire user record for each login request. If the system is busy and a user has to logon multiple times this flag may still be set and the error can occur. 

In normal circumstances, you will not see this error unless you have too many login attempts from the same user at the same time, which can cause the row to lock.

Additional Information:

Below the information is a continuation of the answer above detailed for your Oracle DBA.

This is not necessarily a problem but to reduce the number of these occurrences you can decrease the USERLOCKEDTIMEDIFF and DEVICELOCKEDTIMEDIFF in ARRFCONFIGURATION table from 120s to around 30s which would update the USERLOCKED/DEVICELOCKED flag more frequently(which means before the next transaction on that particular user/device arrives) to avoid the USERLOCKED/DEVICELOCKED scenarios and the ORA-00054(resource busy) errors

As part of Riskfort, we use only the DML operations, not the DDL operation. If this is the scenario you are looking for, below is the explanation:

Whenever we perform a transaction in Riskfort, we try to perform many insert/update operations on many tables, but we have noticed these errors whenever we do DML operations on ARRFUSERCONTEXT/ARRFDEVICECONTEXT tables. For this operation, we lock a row related to that user/device with the USERLOCKED/DEVICELOCKED flag in these ARRFUSERCONTEXT/ARRFDEVICECONTEXT tables, to avoid any failure in the successive update operation.

To reduce these errors, we suggest to decrease the USERLOCKEDTIMEDIFF and DEVICELOCKEDTIMEDIFF in ARRFCONFIGURATION table from 120s to around 30s which would update the USERLOCKED/DEVICELOCKED flag more frequently(which means before the next transaction on that particular user/device arrives) to avoid the USERLOCKED/DEVICELOCKED scenarios and the ORA-00054(resource busy) errors