Dead Lock

ajaybelde
ajaybelde used Ask the Experts™
on
Could you please help me to troubleshoot this deadlock?

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-00020024-0006b73c        24     629     X             29     625           X
TX-00080006-0007b326        29     625     X             24     629           X
session 629: DID 0001-0018-000A77DC     session 625: DID 0001-001D-000A7A0D
session 625: DID 0001-001D-000A7A0D     session 629: DID 0001-0018-000A77DC
Rows waited on:
Session 625: obj - rowid = 00003512 - AAAJmuAAtAACfUPAAe
  (dictionary objn - 13586, file - 45, block - 652559, slot - 30) Session 629: obj - rowid = 00003512 - AAAJmuAAtAACfUPAAh
  (dictionary objn - 13586, file - 45, block - 652559, slot - 33) Information on the OTHER waiting sessions:
Session 625:
  pid=29 serial=23967 audsid=4030939 user: 32/CVNCODS
  O/S info: user: SVC_BusObj, term: INDAPP84, ospid: 4812:5112, machine: ENT\INDAPP84
            program: al_engine.exe
  application name: al_engine.exe, hash value=4255119177
  Current SQL Statement:
  UPDATE "CVNCODS"."RF_CONTAINER_LOCATION"
SET "PARENT_CONTAINER_LOCATION_FK" = :AL_AfterVariable1 WHERE "NATURAL_KEY" = :AL_BeforeVariable0 End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE "CVNCODS"."RF_CONTAINER_LOCATION"
SET "LOCATION_IDENTIFIER" = :AL_AfterVariable1 WHERE "CONTAINER_LOCATION_PK" = :AL_BeforeVariable0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

Commented:
the application is executing this query in 2 sessions simultaneously:
UPDATE "CVNCODS"."RF_CONTAINER_LOCATION"
SET "PARENT_CONTAINER_LOCATION_FK" = :AL_AfterVariable1
WHERE "NATURAL_KEY" = :AL_BeforeVariable0

it's probably doing a secondary statement too and then trying to commit the batch
Geert GOracle dba
Top Expert 2009

Commented:
looks like you have already bumped into this problem before
http://www.experts-exchange.com/Database/Oracle/Q_27669673.html

are you sure you understand "deadlock" ?

Author

Commented:
According to AWR report these are the two statements ran that time.Were they blocked each other?
UPDATE "CVNCODS"."RF_CONTAINER_LOCATION" SET "LOCATION_IDENTIFIER" = :AL_AfterVariable1 WHERE "CONTAINER_LOCATION_PK" = :AL_BeforeVariable0  

 UPDATE "CVNCODS"."RF_CONTAINER_LOCATION" SET "LOCATION_NAME" = :B8 , "STORAGE_CONDITION_FK" = :B7 , "SOURCE_SYSTEM_CODE" = :B6 , "SOURCE_SYSTEM_ID" = :B5 , "EXTERNAL_LOCATION_NAME" = :B4 , "CONTAINER_LOCATION_TYPE_FK" = :B3 , "SMART_LOCATION_ID" = :B2 , "SRC_STORAGE_COND" = :B1 WHERE "NATURAL_KEY" = :B9
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Geert GOracle dba
Top Expert 2009

Commented:
a deadlock indeed means that 2 sessions are blocking each other

Author

Commented:
Yes, few days back deadlock occured due to cascade deletes & we have changed our apllication code to lock master table before locking child table but this is different one.

Author

Commented:
I understand what deadlock means.
After occurance of this deadlock we re-ran the job & it has completed fine without any locks,.i am not sure why!
Oracle dba
Top Expert 2009
Commented:
i see container_location so i assume warehouse
>> possible that a container move was happening to a same location
> next time the job is run, the location may not be free so the deadlock will not occur for this location again

basically, it's a design flaw in the application
and since you are getting it repeatedly, looks like it wasn't designed for multiuser/multisession

Author

Commented:
Your assumption(warehouse) is correct & There might be a possibility of container move happened to same location.
I will work with app_team on this.

Thanks for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial