P42101 and P43070 Cause Deadlock in Competing to Update a Held Orders (F4209) Table
Goal
Troubleshooting Steps
When users are changing data in Sales Order Entry (P42101) and immediately try to Release Held Orders (P43070) causes a dead lock to Held Orders (F4209) table as <Bug:10969260> reads.
Possible business scenario where this may happen:
Sales staff create sales orders through R47011 with order hold (e.g., EDI Hold) with more than 30 detail lines
Review/Update SO created through P42101 - Sales Order Entry
Release order through P43070 - Release Held Order
When does this error occur?
Case 1 - Valid transaction Sales staff create sales orders through R47011 with order hold (e.g., EDI Hold) with more than 30 detail lines
Review/Update SO created through P42101 - Sales Order Entry
Release order through P43070 - Release Held Order
Through P42101 (TRANSACTION #1) 1. ENTER: SalesOrderApplCtrlEX 2. ENTER: F4211FSEndDoc /* With Transaction Processing ON - MANNUAL COMMIT */ 3. ENTER: WriteHoldCode sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) 3. EXIT: for WriteHoldCode=0 2. EXIT: for F4211FSEndDoc=0 /* sql> COMMIT TRANSACTION */ 1. EXIT: for SalesOrderApplCtrlEX=0
Through P43070 (TRANSACTION #2) 1. ENTER: ReleaseHeldOrder sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) 1. EXIT: for ReleaseHeldOrder=0
Note:
For above call stack F4211FSEndDoc is called with 'Transaction Processing' on so UPDATE of F4209 issues exclusive lock till sql> COMMIT TRANSACTION is coming. Refer Doc ID 1208208.1 - E1: DB: Transaction, Transaction Processing, Commit and Rollback.
From the 1st update statement till commit transaction blocking against F4209 will take place.
Case 2 - Deadlock Transaction
Through P42101 (TRANSACTION #1) 1. ENTER: SalesOrderApplCtrlEX 2. ENTER: F4211FSEndDoc /* With Transaction Processing ON - MANNUAL COMMIT */ 3. ENTER: WriteHoldCode sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) 3. EXIT: for WriteHoldCode= *** Continue to COMMIT other tables *** *** TRANSACTION #2 tries to get locking information for blocked table by TRANSACTION #1 ***
Through P43070 (TRANSACTION #2) 1. ENTER: ReleaseHeldOrder sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) /* Transaction Stops here in getting locking information for table F4209 */ /* Because it is blocked by 1st TRANSACTION */ (TRANSACTION FAILURE)
Note:
For this example, two different transactions through two different applications (P42101 and P43070) are competing to update Held Orders (F4209) table.
For small transaction from the 1st update of F4209 to F4211FSEndDoc it may not take time but if the number of transaction is huge in updating detail information it may consume some time.
This is one of possible scenario rather than implying all the locking always being occurred this manner.
Since 2nd UPDATE was issued it falls into deadlock error will be written in jde.log and jdedebug.log (if any) and e1root.log:
jde.log will contain after locking took place
This call object kernel be zombied
e1root.log will contain COSE #1001 Timeout with calling(1st Level) BSFN SalesOrderApplCtrlEX
This type of issue may occur when 'Transaction Processing' is on in dealing with critical transaction.
To prevent this type of issue 'Record Reservation' logic will be implemented.
How can this be fixed?
(Fast Path) UDC
Look for Product Code: 00 (Foundation Environment) and User Defined Codes 'RR' - Object In Use Application ID
Verify/Add application ID P42101 and P43070 (If it is missing)
Currently record reservation for applications P42101 and P43070 are implemented as below,
P42101 handled record reservation as E1: 42: MVC Architecture (Powerform) and Sales Order Entry (P42101) (Doc ID 1212163.1), simplified routine:
Call B4210400 - W42101D_CreateSalesOrder (W42101D Create Sales Order View Controller) which is calling BSFN ValidateApplicationId then F00095ReserveObject
Call F4211FSBeginDoc
Call F4211FSEditLine
Then call B4210900- SalesOrderApplCtrlEX (Sales Order Application Controller - External) which is calling B4204200 - CommitSalesOrder which eventually is calling B4200310 - F4211FSEndDoc then F00095RemoveReservation
P43070 handled record reservation through FORM: Work With Held Orders [FIND BROWSE]
Call BSFN F00095RemoveReservation (F00095 Remove Business Object Reservation)
Note:
Above routine is simplified routine in reality there will be more validation and assignment
By defining application ID P42101 and P43070 through UDC (00|RR) 2nd update never take place till all the data through P42101 gets committed/roll-backed so this is safe way to guard integrity let alone which avoid possible deadlock
Generally record reservation routine will be written at Dialog Initialized Event as soon as Revision form (where you can add/update data) is opened in UPDATE MODE
However P43070 will reserve order in your clicking find button (more detail, after grid buffer was written or when you can see data in Grid). This routine is implemented through SAR 7938202
UDC(00|RR) is to be validated through BSFN N0000604 - ValidateApplicationId (Validate Application Id). If a certain routine does not call this business function object/record reservation routine is hard coded within a certain routine
In installing a new release program ID P42101 may not be in the F0005 for (00|RR) so it is important to add it into UDC (00|RR) reference E1: 40: User Record Reservations (Doc ID 625589.1) to determine what is suggestion made by EnterpriseOne.
In procurement if the setting is not done properly same issue can be occurred between P4310 and P43060. Demo data you have referenced may not have application ID P43060 in UDC 00/RR. If so, add a record for the application P43060 to minimize possible deadlock issue for tables F4209 and F4311.To have full list of application which has this type of implementation Refer <Document 1142464.1> E1: FDA: F00095 Record/Object Reservation in EnterpriseOne Interactive Application
To look at upcoming or archived Advisor Webcasts please see Advisor Webcast Details <Document 548764.1> if your topic is not currently scheduled please suggest it.