Sporadic AsActivity Table Deadlocks
(Doc ID 2976061.1)
Last updated on SEPTEMBER 28, 2023
Applies to:Oracle Insurance Policy Administration J2EE - Version 188.8.131.52 and later
Information in this document applies to any platform.
On : 10.2.0.29 version, General
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document