Wednesday, December 06, 2006

Oracle and Tuxedo Transaction Deadlock

Follow these steps to locate the Tuxedo logs of the services that have deadlocked in an Oracle database. Tuxedo service debugging should have already been implemented before the deadlock occurred.

Part of Oracle alert.log:
Mon Dec 4 13:59:36 2006
ORA-000060: Deadlock detected. More info in file /u103/oracle/admin/DB/udump/db_ora_1567.trc.

Optionally confirm time of deadlock:
$ grep 'SESSION ID' db_ora_1567.trc
*** SESSION ID:(176.15362) 2006-12-04 13:59:27.820

Find the Tuxedo program name and machine names. Replace "host" with a string that matches your application server host names.
$ grep host db_ora_1567.trc
Node name: dbhost
Unix process pid: 1567, image: oracle@dbhost (TNS V1-V3)
O/S info: user: arbor, term: , ospid: 10159, machine: app018
program: om_Customer@app018 (TNS V1-V3)
application name: om_Customer@app018 (TNS V1-V3), hash value=0
OSD pid info: Unix process pid: 1567, image: oracle@dbhost (TNS V1-V3)
O/S info: user: arbor, term: , ospid: 12110, machine: app020
program: om_OrderProces@app020 (TNS V1-V3)
application name: om_OrderProces@app020 (TNS V1-V3), hash value=0

On machine app018 go to Tuxedo service log directory:
$ cd log

Find the files with the service and time that were in the database deadlock trace file:
$ ls -otr om_Customer* om_OrderProces*|tail -9
-rw-r--r-- 1 arbor 3733 Dec 4 00:01 om_OrderProcessing-061204-000033.03.10169.server.sql00100
-rw-r--r-- 1 arbor 303300 Dec 4 00:01 om_OrderProcessing-061204-000026.03.10705.server.001
-rw-r--r-- 1 arbor 3378 Dec 4 12:10 om_Customer-061204-120950.03.10159.server.sql00100
-rw-r--r-- 1 arbor 46855616 Dec 4 19:57 om_OrderProcessing-061204-000003.03.10169.server.dbg
-rw-r--r-- 1 arbor 5525130 Dec 4 19:57 om_OrderProcessing-061204-000003.03.10169.server.001
-rw-r--r-- 1 arbor 882855649 Dec 4 19:57 om_Customer-061204-012635.03.10159.server.dbg
-rw-r--r-- 1 arbor 259093081 Dec 4 19:57 om_Customer-061204-012635.03.10159.server.001
-rw-rw---- 1 arbor 3017100 Dec 4 19:57 om_OrderProcessing.3.err
-rw-rw---- 1 arbor 324921588 Dec 4 19:57 om_Customer.3.err

These tuxedo log files along with the database deadlock trace file can be used to determine the cause and potential resolution of the deadlock condition.

No comments:

Post a Comment