Identity Portal server.log showing ORA-00054 and ORA-00060 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired

Products

• CA Identity Suite

Issue/Introduction

Two different clusters of Identity Portal under Vaap using different ORACLE 19 in different servers and BOTH of them are being given the error described in the logs.
The product keep working but DBA team is worried about these messages ORA-00054 and ORA-00060

1. ORA-00054: 

ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

2022-04-25 15:24:44,448 ERROR [org.jboss.as] (Controller Boot Thread) WFLYSRV0026: WildFly Full 15.0.1.Final (WildFly Core 7.0.0.Final) started (with errors) in 386856ms - Started 754 of 770 services (1 services failed or missing dependencies, 61 services are lazy, passive or on-demand)
2022-04-25 15:40:58,226 INFO  [stdout] (defaultScheduler-1) [SIGMA APPENDER - FROM WEB] 2022-04-25 15:40:58,226 - WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 54, SQLState: 61000
2022-04-25 15:40:58,227 INFO  [stdout] (defaultScheduler-1) [SIGMA APPENDER - FROM WEB] 2022-04-25 15:40:58,227 - ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
2022-04-25 15:40:58,227 INFO  [stdout] (defaultScheduler-1)
2022-04-25 15:40:58,228 INFO  [stdout] (defaultScheduler-1) [SIGMA APPENDER - FROM WEB] 2022-04-25 15:40:58,228 - INFO org.hibernate.event.internal.DefaultLoadEventListener - HHH000327: Error performing load command : org.hibernate.dialect.lock.PessimisticEntityLockException: could not obtain pessimistic lock

2. ORA-00060

2022-07-12 19:16:33,748 INFO [stdout] (statisticsScheduler-1) [SIGMA APPENDER - FROM WEB] 2022-07-12 19:16:33,748 - ERROR org. hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00060: deadlock detected while waiting for resource

 

Environment

Release : 14.4

Component : SIGMA-Identity Suite

Resolution

Engineering Team updated that about these messages happening inside of Identity Portal server.log can disregarded

1) ORA-00054:

Engineering posted that this issue (ORA-00054) exists since version 14.2 with Oracle 12c and 19c.
There is no functional impact with this error.
To reduce the frequency, we must disable the "Enable stats cleaner" in the portal admin console.

We will plan this for 14.4.3. At that moment we will think of implementation.

Even if we keep statistics scheduler disabled, we don't have any loss. These should be considered as warning messages

2) ORA-00060:

We have two schedulers for Identity Portal (taskStatusSchedular and statisticsScheduler) that are trying to update the "Tech" table.
Both schedulers try to work on the "Tech" table at the same time with a scheduleWithFixedDelay of 4 seconds.
When a scheduler acquires the lock, the second scheduler is expected to return with NO_WAIT_TIME and log an error in our application.
This is working as designed!

The Engineering team updated that they will evaluated the solution to avoid contention of two schedulers (taskScheduler, statisticsScheduler) to work on the same table or to suppress the error during the blocking state. Engineering will be evaluating doing this during one of the next product release cycle.

For the time being, the customer can disable "Statistics Cleanup" in the Identity Portal admin console if they do not prefer to see the error and without any harm to Identity Portal operations

Information about "Enable statistics cleaner" functionality are available in techdos in this link here