I nije oracle greška nego je do projektanta koji nije video da ovo može da se desi.
Ovde tebi sesija 25 čeka na sesiju 24 i obrnuto. A izvršava operaciju
. Sesija 25 čeka na slog koji ima rowid 'AAAHvlAABAAAAAAAAA' a sesija 24 izgleda da ne traži nikakav slog trenutno (što je malo čudno), ali se vidi da obe sesije gađaju isti objekat odnosno verovatno ovu tabelu OrderEappro. To se vidi iz kolone
.
Vidi za početak da li je možda tabela OrderEappro parent tabela za neki foreign key. Ako jeste onda proveri da li ti je taj foreugn key indexiran i proveri na šta liče te iste tabele na ona druga dva sistema koja ti se ne bune.
Code:Reviewer: Branka from VA, USA
In following case how to find out objects involved in deadlock. It's showing no
rows and problem is with delete, not with update.
Also, what does it mean when you have 2 same Resource Name?
I know that second package call first one and delete statement is in that first
one. Is resource name name of that Stored Procedure?
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM PARTY WHERE PARTY_ID = :b1
----- PL/SQL Call Stack -----
object line object
handle number name
3c38b45b8 6184 package body PROD_USR.PARTY_CHG_PKG
3cf9d0ff0 1554 package body PROD_USR.IG_PKG
3d5d6de10 3 anonymous block
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
TM-0000743c-00000000 49 356 SX SSX 87 97 SX SSX
TM-0000743c-00000000 87 97 SX SSX 49 356 SX SSX
session 356: DID 0001-0031-0000441C session 97: DID 0001-0057-00002C3E
session 97: DID 0001-0057-00002C3E session 356: DID 0001-0031-0000441C
Rows waited on:
Session 97: no row
Session 356: no row
Information on the OTHER waiting sessions:
Session 97:
pid=87 serial=55565 audsid=8033546 user: 60/PROD_USR
O/S info: user: nobody, term: , ospid: 14814, machine: web3.att.teetimes.com
program:
[email protected] (TNS V1-V3)
application name:
[email protected] (TNS V1-V3), hash value=0
Current SQL Statement:
DELETE FROM PARTY WHERE PARTY_ID = :b1
End of information on OTHER waiting sessions.
Followup:
is party a parent table in a parent child relationship?
GOTO a page to Bookmark Review | Bottom | Top
DEADLOCK DETECTED November 30, 2004
Reviewer: Branka from VA, USA
yes it is.
That is last statement in the Stored Procedure. Before it, all child records are
deleted.
Followup:
is the foreign key in the child table INDEXED?
GOTO a page to Bookmark Review | Bottom | Top
DEADLOCK DETECTED November 30, 2004
Reviewer: Branka from VA, USA
Only one of child tables has index on foreign key column (PARTY_ID). Party table
has 5 child tables.
I find one thing, but I am not sure is that relevant (I changed it any way).
At the beginning of the Stored procedure I have:
SELECT NVL(MIN(COUPON_ID),0) INTO N_TARGET
FROM COUPON_PLAY
WHERE PARTY_ID = nParty_id;
IF N_TARGET > 0 THEN
DELETE FROM COUPON_PLAY
WHERE COUPON_ID = N_TARGET
AND PARTY_ID = nParty_id;
END IF;
And than on the end:
DELETE FROM COUPON_PLAY
WHERE PARTY_ID = nParty_id;
I commented out first part, because it does not seem to have any reason for
that.
This is not table with index on foreign key.
Followup:
this is totally due to unindexed foreign keys.
(and think about it -- you delete from T where fkey = :x -- full scan? not
exactly what you probably want)
run this:
set echo on
drop table c;
drop table p;
create table p ( x int primary key );
create table c ( x references p );
REM create index c_idx on c(x);
insert into p select rownum from all_users where rownum <= 2;
insert into c select * from p;
commit;
delete from c where x = 1;
set echo off
prompt in another session:
prompt delete from c where x = 2;;
set echo on
pause
set echo off
prompt in another session:
prompt delete from p where x = 2;;
prompt right after this blocks...
delete from p where x = 1;
------------------------------------------------------------
Try it with the index on and off.
Šta da ti kažem puno sreće i nemoj da te lože developeri uglavnom su oni krivi :-))