deadlock

Hi,
Iam finding a deadlock in my trace file

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE POTRANS SET ID = :ID,LOC_INBOUND =
null,ACTIVE_FLAG='T' WHERE TRANS_NUM='APIKO233S;
9' AND LOCN_ID = 'UCBH914306'
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-000500be-0001c71a 19 9 X 15 100 X
TX-000700bf-0001c391 15 100 X 19 9 X
session 9: DID 0001-0013-00000002 session 100: DID 0001-000F-00000002
session 100: DID 0001-000F-00000002 session 9: DID 0001-0013-00000002
Rows waited on:
Session 100: obj - rowid = 0000A8A6 - AAAKimAAGAABe6VAAR
(dictionary objn - 43174, file - 6, block - 388757, slot - 17)
Session 9: obj - rowid = 00009BAE - AAAJuuAAGAAAm16AAf
(dictionary objn - 39854, file - 6, block - 159098, slot - 31)
Information on the OTHER waiting sessions:
Session 100:
pid=15 serial=2160 audsid=3168042
Current SQL Statement:
UPDATE TRANSLINES
set STATUS=:b2,DESTLOCN_ID=:b3
where TRANS_NUM=:b1
End of information on OTHER waiting sessions.


There are no locks on any table!!iam getting contineously this trace files as deadlock detected.i know oracle rollbacks automatically one session.But iam getting generated contineously with the trace files as deadlock detected.how to release this dead lock.
any help wil be greately appreciated.
Ruju

Are you using triggers on the base tables? Is it possible that the deadlocks are caused by triggers stepping on each other?

Quote:

---

Originally posted by oraruju
how to release this dead lock.
any help wil be greately appreciated.

---

A deadlock is not something that can be "released", it is an application design flaw that must be corrected in the source.

You should have enough information from the dump to work out the records involved.
It appears session 9 has updated TRANSLINES and is wanting to update POTRANS while session 100 has updated POTRANS and is wanting to update TRANSLINES.
You have the ROWIDs (AAAKimAAGAABe6VAAR,AAAJuuAAGAAAm16AAf) that are the issue, so you can even look at the records.

What you probably want to find out is what program is behind sessions 9 and 100. If possible, they should both lock the tables in the same order. If that isn't possible, you might just want to make transactions as fast as possible to reduce the probability of a deadlock.

PS. The UPDATE POTRANS looks a bit odd using a bind variable for the ID but literals for trans_num and locn_id.

Hi,
How to make these transactions faster?i have indexes on all search criteria columns on these queries?what is the other way to reduce this traffic?
thanks for help
Ruju

When I said 'making transactions faster' I basically mean reducing the time between an INSERT/UPDATE/DELETE and the eventual COMMIT (or ROLLBACK).

How you go about this depends on what is going on.
If you have and end user who'd done an update, then gone off for lunch without committing then
a) The application shouldn't send the update to the database until it is ready to commit (and it may need a change to the locking strategy to check that someone else hasn't updated the row in the meantime)
or
b) After a while the application should timeout and rollback the transaction

If this is a batch job, the developer may have (correctly) thought "I'll commit when I've processed the entire file rather than slowing down the process by committing on each transaction". However if you are getting deadlocks, committing each transaction may slow the process down but should reduce the chance of a deadlock.

Ultimately this is a problem with the programs concerned not individual SQLs.