Strong Authentication service becomes unresponsive due to a few transactions got stuck

Products

• CA Strong Authentication
• CA Advanced Authentication

Issue/Introduction

Strong Authentication product has been installed and configured in various locations globally. An standalone AuthMinder instance integrated with Oracle Unified Directory. On RHEL 6.7.1 operating system AuthMinder service consuming and storing data in Oracle 11G database and hosted on Tomcat 7.0 version. When the underlying AuthMinder database migrated from 11g to 19c, every couple of hours AuthMinder service is not responding. After restarting the services it works fine for few hours. 

 

Environment

Release :

• CA AA - 9.1.01
• OS - Linux 6.7.1
• Oracle 19c (19.0.0.0.0)
• Tomcat 7.0.70
• JDK version - openjdk "1.8.0_262"
• Oracle Unified Directory version - 12.2.1.4.221009

Cause

After analyzing Arcot WebFort( Strong Auth) logs and compared them with ODBC traces, a few transactions are found stuck. These transactions were invoking multiple threads to database to fetch required data from database. Due to these SQL statements the AuthMinder services is not responding. Strong Auth server has tried to fetch the DB sequence, which was not successful.

SA log
02/16/23 13:35:49.472 DEBUG TXN_NATIVE   02397265 1447416787 - ArDBM::Found query string for query-id : [TEMPLATE_WFSEQ_FETCH_SEQ].
02/16/23 13:35:49.472 DEBUG TXN_NATIVE   02397265 1447416787 - ArDBM::Executing Query[WFSequence_FetchSeq]

ODBC Trace
2023-02-16 13:35:49.473146 (Time elapsed in microseconds :      135) ppid=2395946:pid=2395947:tid=14028304 UCHAR *             0x007f962401aa78 [4294967293] "SELECT SEQUENCENAME, STARTINGVALUE, INCREMENTVALUE, USEDVALUE, DATECREATED, DATEMODIFIED FROM ARWFSEQUENCE WHERE SEQUENCENAME = 'ARWFINSTANCETXNIDSEQ' AND INSTANCENAME='xxxxxxxxx.com-1519387599'\x00"
2023-02-16 13:35:49.473146 (Time elapsed in microseconds :      135) ppid=2395946:pid=2395947:tid=14028304 SDWORD                    -3
2023-02-16 13:35:49.473240 ppid=2395946:pid=2395947:tid=140283044566784                                     ENTER SQLExecute

- From that time on-wards, SA server has acknowledged all the incoming TXN_NATIVE requests and assigned the Thread however it was not processed at all.

02/16/23 13:36:49.493 DEBUG MONITOR      02658718 0MONITOR - Thread [2658718] has been created for [TXN_NATIVE]. Current number of threads [6]. Max configured [128]
02/16/23 13:38:49.599 DEBUG MONITOR      02664268 0MONITOR - Thread [2664268] has been created for [TXN_NATIVE]. Current number of threads [14]. Max configured [128]

Resolution

After analyzing the logs and ODBC trace files and it is found that "TEMPLATE_WFSEQ_FETCH_SEQ" is executed for every transaction. Ideally, this query should have been executed once for every 50 transactions. This interval (50) is configured in the ARWFSEQUENCE table. The query to update the increment value by 50 is mentioned below.

update ARWFSEQUENCE set incrementvalue=50 where instancename='<hostname>';
commit;

Note : Need to restart the SA instance after the above change.