Sporadic AsActivity Table Deadlocks (Doc ID 2976061.1)

Last updated on SEPTEMBER 28, 2023

Applies to:

Symptoms

On : 10.2.0.29 version, General

ACTUAL BEHAVIOR
---------------
Observed that there are deadlocks occurring in the Production Environment which is causing processes to hang.
As a result, OIPA is consuming the allocated thread pool connection capacity in Weblogic and the Weblogic is detecting the stuck threads and changing its status to overloaded.
The issue is sporadic and is hard to recreate on demand.

Steps followed to identify the issue:
- Retrieved the deadlock information by engaging DBA. Isolated the queries/operations that were causing the deadlock
- Verified the Connection and DataSource settings on Application Server and JDBC driver and made sure they are configured according to guidelines.

Some Additional Details:
- MSSQL Server 2017 EE and JDBC driver is JTDS
- Capture a deadlock which we believe is being caused by OIPA base queries. Observing the deadlocks on AsActivity table.
- As a result, Observing hung DB processes; which in turn seems to be causing table lock on AsEntityLock, AsActivitySequence etc tables.
- Observing OIPA code is setting auto-commit false, which is turning IMPLICIT TRANSACITONS on and this may be contributing to the issue as well.
- The indexes on AsActivity table are the ones that come with the OIPA stock database, so they are assumed to be optimized by Oracle.
- Run report over regular deadlocks occurring in PROD and attaching that in a folder.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.