RE: Deadlock on DBMS_ALERT_INFO
Date: Thu, 7 Aug 2014 19:02:52 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD126EDDF45_at_G6W2491.americas.hpqcorp.net>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--
http://www.freelists.org/webpage/oracle-l
†Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^ Received on Thu Aug 07 2014 - 21:02:52 CEST