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:
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:
pid=15 serial=2160 audsid=3168042
Current SQL Statement:
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.
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.
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)
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.