My Oracle Support Banner

EAP: Voucher Build deadlock issue on DB2 when updating PS_GRP_AP (Doc ID 2349384.1)

Last updated on OCTOBER 01, 2018

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When running Voucher Build on DB2, process abends at Step AP_VCHRBLD.MAIN.Step19 with an error. Step is related to Control Groups even though the Customer no longer uses Control Groups.

ERROR
-- 10:17:40.553 .(AP_VCHRBLD.MAIN.Step19) (SQL)
UPDATE PS_GRP_AP SET PROCESS_INSTANCE = 0
/
-- Row(s) affected: 0
-- 10:18:42.641 Process 1330655 ABENDED at Step AP_VCHRBLD.MAIN.Step19
(Action SQL) -- RC = 8601
ROLLBACK
/
-- 10:18:42.871 SQL Error: [IBM][CLI Driver][DB2] SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code "00C9008E".
SQLSTATE=57033

 (SQLSTATE 57033) -913

++++++++++++++++++++++++

It appears that creating a Voucher on-line is conflicting with Voucher Build.

When creating the Voucher on-line, the PeopleCode/SQL trace shows that a Select is occurring for PS_GRP_AP:

PSAPPSRV.20407 (51) 1-16923 14.00.08 0.000116 31: SQLExec("Select grp_ap_status from ps_grp_ap where business_unit = :1 and grp_ap_id = :2", VOUCHER.BUSINESS_UNIT, VOUCHER.GRP_AP_ID, &GRP_STATUS);
PSAPPSRV.20407 (51) 1-16924 14.00.08 0.000008 Fetch Field: VOUCHER.BUSINESS_UNIT Value=00010
PSAPPSRV.20407 (51) 1-16925 14.00.08 0.000010 Fetch Field: VOUCHER.BUSINESS_UNIT Value=00010
PSAPPSRV.20407 (51) 1-16926 14.00.08 0.000006 Fetch Field: VOUCHER.GRP_AP_ID Contains Null Value
PSAPPSRV.20407 (51) 1-16927 14.00.08 0.010206 Cur#1.20407.PSFSUNT RC=0 Dur=0.010184 COM Stmt=Select grp_ap_status from ps_grp_ap where business_unit = :1 and grp_ap_id = :2
PSAPPSRV.20407 (51) 1-16928 14.00.08 0.000035 Cur#1.20407.PSFSUNT RC=0 Dur=0.000001 Bind-1 type=2 length=5 value=00010
PSAPPSRV.20407 (51) 1-16929 14.00.08 0.000003 Cur#1.20407.PSFSUNT RC=0 Dur=0.000000 Bind-2 type=2 length=1 value=

At the same time, when Voucher Build is running, Step AP_VCHRBLD.MAIN.Step19 is trying to update the PROCESS_INSTANCE to 0 for PS_GRP_AP.

It appears that the deadlock is occurring since Voucher Build wants to update the PROCESS_INSTANCE to 0 for PS_GRP_AP at the same time that the on-line Voucher creation is trying to select GRP_AP_STATUS from PS_GRP_AP.

STEPS
1. Run Voucher Build to process around 100 Vouchers
2. At the same time Voucher Build is running, create an on-line Voucher
3. Voucher Build will abend at the above step with a deadlock error

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!


In this Document
Symptoms
Changes
Cause
Solution
References


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