E1: DB: Custom Stored Procedure Causes Deadlocks in the Database (Doc ID 1301427.1)

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.

Symptoms

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: COSE#1000 Request timeout: timeout after 300000ms host E1Logic:6015(1134) SocID:1139 PID:6004 BSFN:ShipConfirmEndDoc user:UserA Env:JPD900
[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!!

 

Notes:
  • 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

Changes are as follows:

Cause

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