Locks On BAL_GRP_T During Cancellation Of Service (Doc ID 1470839.1)

Last updated on APRIL 12, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.1.0.0 to 7.3.1.0.0 [Release 7.3.1]
Information in this document applies to any platform.
***Checked for the currency on 14-OCt-2014***

Symptoms

On a 731 production system, it was observed that lot of locks are happening on BAL_GRP_T table, and at times this is halting all other operations. The locks are getting cleared only when DBA kills them.

Following information was gathered and analyzed :

- From the database side, looked at trace file ( showing the dead locks), AWR report, Locking sqls
- From brm system, looked at cm and dm logs and pstack of the cm process during the problem situation

Observations:

*  Locks were on bal_grp_t table
*  Below query is the one that is holding the lock :

select   poid_DB, poid_ID0, poid_TYPE, poid_REV from bal_grp_t   where bal_grp_t.poid_ID0 = :1   for update of bal_grp_t.poid_id0

*  The balance group that was getting locked belonged to an account having around :

16000 active and inactive services
32124 Purchased_product
91451 Purchased_discount

*  The operation that was happening during this period is a process that is trying to cancel the service.
*  The above balance group that was locked is attached to the account and none of the services are associated to this balance group
*  Another notable aspect was that service level cancellation is taking place and each service has it own unique balance group.
*  But during these cancellations, the account level balance group is getting locked. Ideally, as the action is on service, the service level balance group should be locked and not the account level balance group.
*  From the cm pinlog it was observed that the account's bal_grp_t was getting locked towards the end of op_subscription_cancel_product() when fm_subs_utils_update_effectivet() is invoked to update the effective_t of the account.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms