DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: deadlock

  1. #1
    Join Date
    Apr 2005
    Posts
    7

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: deadlock

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

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: deadlock

    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.
    Jeff Hunter

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    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.

  5. #5
    Join Date
    Apr 2005
    Posts
    7
    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

  6. #6
    Join Date
    Feb 2005
    Posts
    158
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width