12.2 E-Business Suite Setting Profile Sign-On:Audit Level Causes Deadlocks | Locks On FND_LOGINS

(Doc ID 1938090.1)

Last updated on AUGUST 29, 2017

Applies to:

Oracle Application Object Library - Version 12.2.3 to 12.2.3 [Release 12.2]
Information in this document applies to any platform.

Symptoms

E-Business Suite 12.2 Applications Object Library, Basic Sysadmin Functions and Maintenance issues

 

 


When attempting to connect with many users at the same time to the system there are several deadlocks as follows:

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
  ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000e0015-000ac180 759 4108 X 787 2000 X
TX-004a001f-000ab6cf 787 2000 X 759 4108 X
 
session 4108: DID 0001-02F7-0000006A session 2000: DID 0001-0313-0000006A
session 2000: DID 0001-0313-0000006A session 4108: DID 0001-02F7-0000006A
 
Rows waited on:
  Session 4108: obj - rowid = 00008743 - AAB5LeAGOAAAAE8AAC
  (dictionary objn - 34627, file - 398, block - 316, slot - 2)
  Session 2000: obj - rowid = 00008743 - AAB5LeAGOAAACetAAg
  (dictionary objn - 34627, file - 398, block - 10157, slot - 32)
 
----- Information for the OTHER waiting sessions -----
Session 2000:
  sid: 2000 ser: 7511 audsid: 79394110 user: 173/APPS
  flags: (0x8000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
  flags2: (0x40009) -/-/INC
  pid: 787 O/S info: user: oracle, term: UNKNOWN, ospid: 20343
  image: oracle@host.domain.com
  client details:
  O/S info: user: applmgr, term: unknown, ospid: 1234
  machine: host.domain.com program: JDBC Thin Client
  client info: 85 0
  application name: e::bes:oracle.apps.icx.security.session.created, hash value=3955462285
  action name: /, hash value=3738137711
  current SQL:
  UPDATE FND_LOGINS FL SET END_TIME = :B2 WHERE SPID = :B1
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=0431t7b7k5wya) -----
UPDATE FND_LOGINS FL SET END_TIME = :B2 WHERE SPID = :B1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object line object
  handle number name
0x35db217d0 107 package body APPS.FND_SIGNON
0x35db217d0 334 package body APPS.FND_SIGNON
0x355e42420 2086 package body APPS.FND_SESSION_MANAGEMENT
0x3598e4960 1 anonymous block

 
The issue can be reproduced at will with the following steps:
1. Set the profile option : Sign-On:Audit Level" to FORMS (any option different then NONE).
2. Connect with many users at the same time.
3. Error thrown in the database log file.

The issue has the following business impact:
Due to this issue, users cannot work when using the AUdit functionality as there are far too many deadlocks.

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