Through this case study, an actual implementation is discussed to minimize potential deadlocks in EnterpriseOne Distribution, Manufacturing and Financial Modules. Below are two common tables that can be used to isolate certain transactions:
F0011 (Batch Header Control Records) Batch header is to be written, when a certain transaction affects General Ledger; to set boundary of transaction
F00095 (Business Object Reservation) Maintain higher integrity by preventing other users/processes from accessing a certain table simultaneously by recording data into the F00095 - Business Object Reservation table.
Note: Custom Table Triggers and/or Stored Procedures and Functions cause deadlocks. Ensure that any external program does not affect daily transactions. Refer to <Document 1301427.1> E1: DB: Custom Stored Procedure Causes Deadlock in the Database
(An example of a deadlock scenario in the Distribution Module)In updating Sales Order, the order gets held and held orders are released before the transaction is committed.
No Deadlock Scenario:
Transaction 1: through P42101-Sales Order Entry
1. ENTER: SalesOrderApplCtrlEX 2. ENTER: F4211FSEndDoc 3. ENTER: WriteHoldCode sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) 3. EXIT: for WriteHoldCode=0 2. EXIT: for F4211FSEndDoc=0 1. EXIT: for SalesOrderApplCtrlEX=0
Transaction 2: through P43070-Held Order Release
1. ENTER: ReleaseHeldOrder sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999)1. EXIT: for ReleaseHeldOrder=0
Deadlock Scenario:
Transaction 1: through P42101-Sales Order Entry
1. ENTER: SalesOrderApplCtrlEX 2. ENTER: F4211FSEndDoc 3. ENTER: WriteHoldCode sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) 3. EXIT: for WriteHoldCode=0
Transaction 2: through P43070-Held Order Release
1. ENTER: ReleaseHeldOrder sql> UPDATE PRODDTA.F4209 SET (...) WHERE (order number 9999) *** Deadlock takes place ***1. EXIT: for ReleaseHeldOrder=0 ... 2. EXIT: for F4211FSEndDoc=01. EXIT: for SalesOrderApplCtrlEX=0
SOLUTION
This type of issue may occur when 'Transaction Processing' is on/enabled. To prevent this type of issue, 'Record Reservation' logic can be implemented. For details on Record Reservation, refer to <Document 625589.1> E1: 40: User Record Reservation.
Fast path to UDC
Look for Product Code: 00 (Foundation Environment) and User Defined Codes 'RR' - Object In Use Application ID
Ensure application ID P42101 and P43070 are added
Notes:
This is just one possible scenario of a deadlock issue and does not include all other deadlock situations.
F4211FSEndDoc (B4200310) is called when 'Transaction Processing' is ON. The UPDATE of F4209 issues an exclusive lock until the transaction is committed by sql> COMMIT TRANSACTION. Refer to <Document 1208208.1> E1: DB: Transaction, Transaction Processing, Commit and Rollback.
From the first update SQL statement until the commit transaction, BLOCKING against F4209 will take place.
For example, two different transactions through from two different applications (P42101 and P43070) are competing to update table F4209 (Held Orders)
For a small transaction from the first update of F4209 to F4211FSEndDoc it may not take time but if there are a large number of detail lines, it may take some time in updating the detail information (We can call this blocking from Update statement to Commit Transaction)
Once the second UPDATE is issued in transaction 2 above, deadlock occurs and an error will be written to the JDE & JDEDEBUG logs (if debug is enabled) and to E1ROOT.LOG
JDE.LOG will contain the error message after locking takes place (review the JDE.LOG from the server that runs logic (or Enterprise Server)
Call Object Kernel can potentially go to zombie state.
E1ROOT.LOG will contain COSE #1001 Timeout errors when calling(first Level) BSFN SalesOrderApplCtrlEX (B4210900). Again for this case stufy only; Call Object kernel log or JDE.LOG which trace system level information will be useful for further analysis.
By defining applications P42101 and P43070 through UDC 00/RR - Record Reservation, the second update never takes place until all the data through P42101 is committed/roll-backed so this is safe way to enforce integrity as well as to avoid possible deadlocks.
Generally, record reservation routines will be written at Dialog Initialized Event as soon as the Revision form (where data is added/updated) is opened in UPDATE MODE.
P43070 will reserve order when the Find button is clicked (Ex: after grid buffer is written or when data can be seen in the grid). This routine is implemented through <Bug 10912055>.
UDC 00/RR is validated through BSFN N0000604 (ValidateApplicationId -Validate Application ID). If a certain routine does not call this business function, the object/record reservation routine is hard coded within a certain routine.
(An example of a deadlock scenario in the Manufacturing Module)In adding/updating BOM (Bill of Material) through P3002, deadlocks may take place against table F4102 as two processes (Internal Routines) are competing to updating the same table within EndDoc routine- F3002EndDocument. This example shows how a deadlock occurs within one transaction in a single application.
No Deadlock Scenario:
Turn off Transaction Processing so AUTO COMMIT takes place
Deadlock Scenario:
Transaction 1: through P3002-Bill of Material
1. ENTER: F3002EndDocument 2. ENTER: UpdateLowLevelCodes sql> UPDATE PRODDTA.F4102 SET ... IBLLX=7 WHERE (IBITM = 999 AND IBMCU =' M30') 2. EXIT: for UpdateLowLevelCodes=0 2. ENTER: UpdateF4102MRPNetChg sql> UPDATE PRODDTA.F4102 SET ... IBMRPC='1' WHERE (IBITM = 999 AND IBMCU = ' M30') *** Deadlock takes place *** 2. EXIT: for UpdateF4102MRPNetChg=0 sql> **** Other Insert/Update against F3002 ***1. EXIT: for F3002EndDocument=0
Notes:
By default 'Transaction Processing' is ON/enabled for Headerless/detail forms in the Manufacturing module (form/grid properties)
For this reason, it determines that Transaction Processing is ON even though 'Transaction Processing' is not ON when calling business function F3002EndDocument (N3002040)
This type of issue may occur regardless of the size of data in THE grid
For example, business functions UpdateLowLevelCodes (B3001080 - F4102 Update Low Level Codes) and UpdateF4102MRPNetChg (B4000930 - F4102 Update MRP Net Change) update the same table F4102 (Item Branch File) since the first UPDATE is not committed it blocks the same row to be updated. The second update tries to get locking information indefinitely
If your applied bugs, you can implement transaction processing
<Bug 13490757> DATABASE RECORD LOCK UPDATING NET CHANGE FLAG MRPC ON PURCHASE ORDER AND PARTSLIST
<Bug 11007632> ETO DEADLOCK BY PO RECEIPT - SAR: 8925493
Because offending business function UpdateF4102MRPNetChg (B4000930 - F4102 Update MRP Net Change) sets table F4102 outside transaction processing boundaries
For detail, refer to <Document 1513147.1> E1: DB: JDEDB_COMMIT_MANUAL, JDEDB_COMMIT_AUTO, Transaction Processing Boundaries and Deadlock
Refer to <Document 989586.1> E1: 30: FAQ - Transaction Processing in Manufacturing Modules
A number of applications update F4102.MRPC (NetChangeFlag). For details, refer to <Document 1050946.1> E1: 34: F4102.IBMRPC - NetChangeFlag (Flag - Net Change)
SOLUTION
Apply relevant ESUs which contain objects or disable Transaction Processing through Form Design Aid
Back To Top
Example 3: In Voucher Entry
(An example of a deadlock scenario in the Financial Module)In adding invoice, voucher or journal, two different transactions in a single application may cause deadlocks when updating F0011 - Batch Header.
No Deadlock Scenario:
Transaction 1:
1. ENTER: F0411FSEndDoc 2. ENTER: BatchReviseOnTransactionCommit sql> UPDATE PRODDTA.F0011 SET ...,ICIST='U',ICIAPP='P',... WHERE ( ICICUT = 'V' AND ICICU = 9999 ) 2. EXIT: for BatchReviseOnTransactionCommit=0 *** Additional manipulation of data *** 1. EXIT: for F0411FSEndDoc=0
Transaction 2:
1. ENTER: BatchReviseOnExit sql> UPDATE PRODDTA.F0011 SET ...,ICIST='A',ICIAPP='A',... WHERE ( ICICUT = 'V' AND ICICU = 9999 ) 1. EXIT: for BatchReviseOnExit=0
Deadlock Scenario:
Transaction 1:
1. ENTER: F0411FSEndDoc 2. ENTER: BatchReviseOnTransactionCommit sql> UPDATE PRODDTA.F0011 SET ...,ICIST='U',ICIAPP='P',... WHERE ( ICICUT = 'V' AND ICICU = 9999 ) 2. EXIT: for BatchReviseOnTransactionCommit=0
Transaction 2:
1. ENTER: BatchReviseOnExit sql> UPDATE PRODDTA.F0011 SET ...,ICIST='A',ICIAPP='A',... WHERE ( ICICUT = 'V' AND ICICU = 9999 ) *** Deadlock takes place ***1. EXIT: for BatchReviseOnExit=01. EXIT: for F0411FSEndDoc=0
SOLUTION
If both transactions are called when 'Transaction Processing' is enabled, then call both BSFNs (xxxEndDoc and BatchReviseOnExit) asynchronously.Alternatively, implement batch creation. Alternatively, call the first BSFN synchronously. (A drawback of this configuration is that users have to wait until the first transaction is fully committed). See the following table which illustrates this further:
1st BSFN Call: F0411FSEndDoc
2nd BSFN Call: BatchReviseOnExit
Result
Transaction Processing (On/Off)
Async
Transaction Processing (On/Off)
Async
Ref #1> On
Yes
On
Yes
OK
Ref #2> On
Yes
On
No
Not OK
Ref #3> On
No
On/Off
Yes/No
OK
Ref #4> Off
No
On/Off
Yes/No
OK
Notes:
Batch Header is a standard method to control monetary transactions.
When Transaction 2 steps in before transaction 1 finishes, deadlocking can occur.
For this type of error, determine whether 'Transaction Processing' is enabled when calling BSFNS: xxxEndDoc and BatchReviseOnExit (B0000008) and check whether these get called synchronously or asynchronously in the application.
This type of issue can be transaction volume specific.
Deadlocks can occur if BatchReviseOnExit is called after xxxEndDoc in Post OK button event or in 'Post Cancel Button clicked' event.
Similar deadlock issues can occur in application P4312 (Purchase Order Receipt) belonging to the Distribution Module as this application also creates G/L records.
Related Master Business Functions are AccRecFSEndDoc (B03B0011), F0411FSEndDoc (B0400047) and F0911FSEndDoc (B0900049).
If transaction processing is disabled when calling xxxEndDoc, a deadlock will not take place because F0011 gets updated as soon as it gets issued. But usually for higher atomity, xxxEndDoc gets called when Transaction Processing in on.
Reference 1: If both BSFNs are running with Transaction Processing enabled, and both are running Asynchronously, the second BSFN will held until the first BSFN ends (likewise single threaded Call Object kernel configuration).
Reference 2: If the volume of transactions for xxxEndDoc is huge, it may take a long time. In this situation, the second BSFN may interfere with the first transaction.
Reference 3 & 4: The second transaction will be waiting for the first transaction to return back to the calling application.
Commonly, Batch Header can be deleted and updated through Business Function FSCloseBatch (B0000042 - F0011 Close Batch) when clicking the Cancel button (in the Data Entry form) or clicking the Close button (in Browse form) to indicate the transaction is completed/done.
If the user clicks the Cancel button immediately after clicking the OK button, and in the case where there are huge number of records, deadlocking may occur. This is usually seen in systems with slow performance. Try improving the system's performance through system tuning and maintenance.
Example 4: Example in Transaction which impact more than 2 modules
Refer to <Note 1050946.1> E1: 34: F4102.IBMRPC - NetChangeFlag (Flag - Net Change) FAQ
When encountering deadlocks:
ShipConfirmEndDoc (N4200790 - Shipment Confirmation End Document) can call F4311EndDoc (XT4311Z1 - F4311 End Document) and F4312EndDoc (XT4312Z1 - F4312 End Document) for ST/OT (Transfer Order) transaction
Purchase Order Receipt for ETO order (where Work Order Number is populated) can call F4312EndDoc and F3111EndDoc (B3101260 - F3111 Parts List End Doc) where both MBF tries to update F4102.MRPC
Deadlock Scenario:
Transaction 1
1. ShipConfirmEndDoc 2. F4211GetShipmentInfo 3. F4311OrderIntegrity 4. F4311EditLine 4. F4311EndDoc 5. SetNetChangeFlag SELECT IBITM, IBMCU, IBMRPC, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY FROM CRPDTA.F4102 WHERE ( IBITM = 1234.000000 AND IBMCU = ' M30' ) 6. GetAuditInfo 6. EXIT: for GetAuditInfo=0 UPDATE CRPDTA.F4102 SET IBITM=1234.000000,IBMCU=' M30',IBMRPC='1',IBUSER='JDE',IBPID='EP4310',IBJOBN='LOCAL',IBUPMJ=112341,IBTDAY=185546.000000 WHERE ( IBITM = 1234.000000 AND IBMCU = ' M30' )... 2. F4312EndDoc 3. F0911FSEndDoc 3. F4111EndDocument 3. F4311EndDoc 3. UpdateF4102MRPNetChg SELECT IBITM, IBMCU, IBVEND, IBMRPC, IBUSER, IBPID, IBJOBN, IBUPMJ, IBTDAY FROM CRPDTA.F4102 WHERE ( IBITM = 1234.000000 AND ' M30' ) UPDATE CRPDTA.F4102 SET IBITM=1234.000000,IBMCU=' M30',IBVEND=4343.000000,IBMRPC='1',IBUSER='JDE',IBPID='EP4205',IBJOBN='LOCAL',IBUPMJ=112341,IBTDAY=185551.000000 WHERE ( IBITM = 1234.000000 AND IBMCU = ' M30' ) OCI0000178 - Unable to execute - UPDATE CRPDTA.F4102 SET IBITM=:BND1,IBMCU=:BND2,IBVEND=:BND3,IBMRPC=:BND4,IBUSER=:BND5,IBPID=:BND6,IBJOBN=:BND7,IBUPMJ=:BND8,IBTDAY=:BND9 WHERE ( IBITM = :KEY1 AND IBMCU = :KEY2 )...
SOLUTION:
In case you face any deadlock (Transaction Failure) for routines explained above, do apply <BUG 11007632> - ETO DEADLOCK BY PO RECEIPT - SAR: 8925493 which sets update of F4102.mrpc for B4000930 - UpdateF4102MRPNetChg (F4102 Updata MRP Net Change) and <Bug 13490757>: DATABASE RECORD LOCK UPDATING NET CHANGE FLAG MRPC ON PURCHASE ORDER AND PARTSLIST for B3100170 - SetNetChangeFlag (Set Net Change Flag) at outside Transaction Processing boundaries through below code:
Both F4311EndDocument and F4312Document are called through Table Trigger against F4211 for ST/OT orders
F4312EndDoc can be called when the processing option of P4205 (Shipment Confirmation) for In-transit account is ON
BSFN F4311OrderIntegrity can be called through Table Trigger in F4211 for ST/OT orders
To have full list of business function which put a certain table outside transaction processing, refer to <Document 1513147.1> E1: DB: JDEDB_COMMIT_MANUAL, JDEDB_COMMIT_AUTO, Transaction Processing Boundaries and Deadlock
Application release upgrade (for example, TP is implemented against P4210, P31111 and so on)
Custom table trigger, procedure and function
then make it sure your custom code does not contribute deadlock by checking transaction processing (and Record Reservation implemenation) as written at <Document 1320968.1> E1: DB: Listing of Applications with Transaction Processing by EnterpriseOne Release