My Oracle Support Banner

Seeing Database Locks Due to UPDATE CSM_CONTR_BUSS_TXN_TYPES_ACC Causing Complete System To Lock Down (Doc ID 2423217.1)

Last updated on JULY 15, 2018

Applies to:

Oracle Mobile Field Service - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.6 version,

ACTUAL BEHAVIOR
---------------
Seeing session block issue running in PROD for the Telesevice module which is causing slowness on application and program is getting stocked its not moving at all.

Blocker session details :
 CSM/OMFS_PALM - e:CSM:bes:oracle.apps.jtf.cac.task.createTask
which is runing
UPDATE CSM_CONTR_BUSS_TXN_TYPES_ACC SET percent_covered = :1 ,
 up_to_amount = :2 WHERE contract_service_id = :3 AND business_p
 rocess_id = :4 AND txn_billing_type_id = :5

SID/Serial : 4265,49103
Foreground : PID: 28850 - frmweb@tprd6ebs11.toshibacommerce.com (TNS V1-V3
Shadow : PID: 5300 - oracle@tprd6odb33.toshibacommerce.com
Terminal : / UNKNOWN
OS User : appaaup on tprd6ebs11.toshibacommerce.com
Ora User : APPS
Details : CS/TGCS_NA_TS_HRDWARE_SUPP_SU - e:CS:frm:JTFTKMAN
Status Flags: ACTIVE DEDICATED USER
Tran Active : 00000020D23593F8
Login Time : Sat 13:12:55
Last Call : Sat 13:13:39 - 74.6 min
Lock/ Latch : 00000020C3684110/ NONE
Latch Spin : NONE
Current SQL statement:
begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;
Previous SQL statement:
begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;
Session Waits:
WAITING: enq: TX - row lock contention
Locks:
TRANSAC ENQ H: X R: NONE - RS+SLOT#46923808 WRP#507568
DML/DATA ENQ H: RX R: NONE - CSM_PARTIES_ACC
TRANSAC ENQ H: NONE R: X - RS+SLOT#786450 WRP#2438089
DML/DATA ENQ H: RX R: NONE - JTF_TASK_ASSIGNMENTS_AUDIT_B
DML/DATA ENQ H: RX R: NONE - CSM_PARTY_SITES_ACC
DML/DATA ENQ H: RX R: NONE - CSM_CONTR_BUSS_TXN_TYPES_ACC
DML/DATA ENQ H: RX R: NONE - ASG_SYSTEM_DIRTY_QUEUE
DML/DATA ENQ H: RX R: NONE - CSM_NOTES_ACC
DML/DATA ENQ H: RX R: NONE - JTF_TASK_ASSIGNMENTS
DML/DATA ENQ H: RX R: NONE - CSM_MTL_SYSTEM_ITEMS_ACC
TYPE=AE H: S R: NONE - ID1=8013913 ID2=0
DML/DATA ENQ H: RX R: NONE - CSM_INCIDENTS_ALL_ACC
DML/DATA ENQ H: RX R: NONE - CSM_SR_CONTACTS_ACC



2,3788,24460, 26052 begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;
4,2616,14257, 29919 begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;
3,489,29298, 13887 begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;

SID/Serial : 485,43078
Foreground : PID: 7744 - frmweb@tprd6ebs11.toshibacommerce.com (TNS V1-V3
Shadow : PID: 31746 - oracle@tprd6odb32.toshibacommerce.com
Terminal : / UNKNOWN
OS User : appaaup on tprd6ebs11.toshibacommerce.com
Ora User : APPS
Details : CS/TGCS_NA_TS_HDW_SUPP_REP - e:CS:bes:oracle.apps.jtf.cac.task.updateTask
Status Flags: ACTIVE DEDICATED USER
Tran Active : 0000002051870020
Login Time : Sat 14:06:36
Last Call : Sat 14:07:39 - 29.4 min
Lock/ Latch : 00000020C36C2DB0/ NONE
Latch Spin : NONE
Current SQL statement:
begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;
Previous SQL statement:
begin CSM_WF_PKG.Task_Assignment_Post_Ins(:1); end;
Session Waits:
WAITING: enq: TX - row lock contention


EXPECTED BEHAVIOR
-----------------------
Shouldn't have session block issue for TeleService module



STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1 - Service - Sevice Request - Find Service Request - User refresh the Task to see the Status getting updated or not
2 - CRM Administrator - Task and escalation Manager - Task -Tasks - Find Task and try to the schedule it .
3 - Field service Manager - Field Service Dispatcher - Dispatch center - This is also used by some of the Dispatcher to Assign the task to the technician.
So when user keep opening the above forms it seems the application session is getting blocked and it causing the slowness.



Changes

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.