E1: DB: Custom Stored Procedure Causes Deadlocks in the Database
Last updated on MAY 30, 2017
Applies to:JD Edwards EnterpriseOne Tools - Version SP23 and Prior and later
Information in this document applies to any platform.
This document is not intended to guide customers on how to write/put stored procedures in the database.
This document deals with possible locking (Deadlocks) or application failure which is caused by the implementation of stored procedures in the relational database system.
In general, it is hard to determine the root cause of the issue when additional routine is written outside JDE, so it is important to inform Oracle Support of any changes made at the database level (which may include: function, stored procedures, table triggers and view).
Symptoms appear in EnterpriseOne that return multiple errors/problems. One example of these issues are:
[RUNTIME] *ERROR* CallObject@44c1b9: Server problem. The server may still be available, but because of state information, the entire unit-of-work must be resubmitted user:UserA Env:JPD900
[WARN ] UserA - [RUNTIME] BusFunctionAsynState.executeAsync(): async BSFN failed and need roll back!!
- Default timeout value for parameter enterpriseserverTimeout is 90,000 ms in the JAS.INI file
- EnterpriseOne does not support stored procedures
- You may be able to write functions, stored procedures, views and triggers
- Stored Procedures are pre-compiled database queries that improve the security, efficiency and usability of the database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle and SQL Server support stored procedures. The major benefits of this technology are the substantial performance gains from pre-compiled executions, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.
Changes are as follows:
- Created/called stored procedures to handle Sales Order Header transactions which update table F4201 (Sales Order Header) at 9:00 in the morning
- Coincidentally you have set EnterpriseOne scheduled job at 9:00 to call R42950 - Sales Order Batch Price/Cost Update which updates the Price and Cost based on F4201/F4211 - Sales Order Header/Detail File
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