RE: Deadlock on DBMS_ALERT_INFO
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: list <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Aug 2014 19:02:52 +0000
Lu, the vendor's response seems like the only practical thing that can be done
in the short term since the update in question is Oracle internal code. Oracle
has always considered deadlocks an application design issue so you are not
going to get much help from Oracle unless you can prove the issue is with their
internal code itself.
What full version of Oracle is being used and is the environment RAC or non-RAC?
We tried using dbms_alert in the past but mostly gave up on it due to the
issues it had in an OPS (later RAC) environment which I think were actually due
to issues in dbms_lock which dbms_alert makes use of. I know we had issues as
recently as 8.1.7. If you are RAC see if you can assign the signalers and
waiters for the alert to run on one instance and see if that makes any
difference as it looks that is what we did for the one alter I see we still
make use of.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Lu Jiang
Sent: Wednesday, August 06, 2014 4:42 PM
To: list
Subject: Deadlock on DBMS_ALERT_INFO
Hello all,
We are having a deadlock issue on DBMS_ALERT_INFO in a production database.
Did run ADDM report and also submitted SR to Oracle support. The recommendation
is ‘The application code and logic need to be modified’.
Have sent the related info to the application vendor, but the vendor’s solution
is only to increase application retries when deadlock occurs.
To prevent this kind of deadlocks, is there anything I can do as a DBA? Could
anyone shed some light on this?
Thanks,
Lu
The following is from the trace file:
user session for deadlock lock 0X00000001D50D5148
sid: 2121 ser: 37862 audsid: 30482721 user: 0/SYS
flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8)
pid: 57 O/S info: user: SYSTEM, term: FHM1PM64DBNA1, ospid: 76664
image: ORACLE.EXE (SHAD)
O/S info: user: FHM1PR\fhm1plp28, term: FHM1PCTXAPSA1, ospid: 12832:13964,
machine: FHM1PR\FHM1PCTXAPSA1
program: PASPVTAA.exe
application name: PASPVTAA.exe, hash value=521341192
Current SQL Statement:
UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME =
UPPER(:B1 )
Thanks,
Lu
--
//www.freelists.org/webpage/oracle-l
��i��0���zX���+�� n��{�+i�^
Other related posts:
