The TX is structured <rbs><slot><wrap>
Could you tell us how DID is structured

Thanks

Sir,

In following case how to find out objects involved in deadlock..
as it's showing no rows...

Tue Aug 6 15:15:15 2002
*** SESSION ID:(21.5235) 2002.08.06.15.15.15.391
ksqded1: deadlock detected via did
DEADLOCK DETECTED
Current SQL statement for this session:

UPDATE INV_MST SET PV_NO=:b1,PV_DT=SYSDATE,ACCT_YR_MTH=:b2,ACCT_DT=SYSDATE,PASS
DT=SYSDATE,INV_STATUS='03' WHERE BR_NO=:b3
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-00100000-0000f243 15 21 X 12 19 S
TX-0012000b-0000ff4e 12 19 X 15 21 S
session 21: DID 0001-000F-00000001 session 19: DID 0001-000C-00000001
session 19: DID 0001-000C-00000001 session 21: DID 0001-000F-00000001
Rows waited on:
Session 19: no row
Session 21: no row


Thanks.
atul

Oracle 9i R1
I have tested a locking case:
session 1:
update emp set comm=100 where empno =7367
/

session 2
update emp set comm=10 where empno =7367
/

Then I went the dump, and found no lock infor. Why so? Do I need to set some kind of event level order to dump the locking infor?
Thanks

Could you please elaborate blocking-lock va deadlock ?

Tom,

Where can I find document to understand the output in alert log file.

Hei,

We ran a stored procedure to process the payroll through a forms6i application. After this execution, locking on a record has been occuring frequently, it may be and index lock as you explained earlier. Still i could not realize the scanario.

Here is my trace file details.

*** 2003-07-08 11:59:20.214
*** SESSION ID:(18.1659) 2003-07-08 11:59:20.214
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object HOSCDPAYTEST.NP_PAYROLL_PROCESS
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
9cf90e8 a8bf2ec bba1600 X a8bf2ec bbfca04 S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------

Let me where i'm missing or doing wrong.

Nirmal

This is very helpful info, but i have a senerio which I am not able to idenifty which row are locked in my case:

DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM INSTRUCTIONS WHERE INSTRUCTION_ID = :b1
----- PL/SQL Call Stack -----
object line object
handle number name
3f2497af8 891 package body MASTER.CG$INSTRUCTIONS
3f52c65d0 30 procedure MASTER.DEL_INSTRUCTIONS_PR
3f52ca450 2811 package body MASTER.CG$ORDERS
3f445f838 29 procedure MASTER.DEL_ORDERS_PR
3f447f9d8 110 package body MASTER.ORDERS_PG
3f447f9d8 2100 package body MASTER.ORDERS_PG
3f447bdb8 186 MASTER.ORDERS_TR
3f447f9d8 2133 package body MASTER.ORDERS_PG
3c6fd9060 1 anonymous block
3f4e4ccf8 207 procedure MASTER.DELETE_PR
3f256fab8 1 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:


When i look at the cursor session:


Cursor 347 (419799398): CURBOUND curiob: 41624dbd0
curflg: c5 curpar: 0 curusr: 20 curses 380542900
cursor name: DELETE FROM INSTRUCTIONS WHERE INSTRUCTION_ID = :b1
child pin: 3c14a1560, child lock: 383154b00, parent lock: 3c26e1f50
xscflg: 101424, parent handle: 3f248c098, xscfl2: 1121800
nxt: 3.0x00000368 nxt: 2.0x00000560 nxt: 1.0x000006e8
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bind 0: dty=2 mxl=22(21) mal=00 scl=00 pre=00 oacflg=03 oacfl2=1 size=2 offset=0
No bind buffers allocated


There is no binding varible being signed when the trace file is generated, how would i identify which row is locked?
I am not sure when this is a unique index lock, there is a unique index is created on INSTRUCTIONS.INSTRUCTION_ID in this case....

Tom, I have read through this thread in attempt to find an answer to my question bu don't see it.  I have the following in my trace file:

*** SESSION ID:(33.60520) 2004-05-12 14:47:19.366
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM TTSSSMT T WHERE T.SMT_NR = :b1

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00040022-00021d25        20      33     X             17      20           X
TX-00020011-00020d44        17      20     X             20      33           X
session 33: DID 0001-0014-00000002    session 20: DID 0001-0011-00000002
session 20: DID 0001-0011-00000002    session 33: DID 0001-0014-00000002
Rows waited on:
Session 20: obj - rowid = 00003C33 - AAAD/BAAJAAAb90AAM
Session 33: obj - rowid = 00003C35 - AAADw1AAJAAAcSkAAN
===================================================

When looking up the objects based on rowid's as you suggested, I get object numbers for both rowid's, but only the second rowid gives me an object number that actually exists.  In other words:

SQL> select dbms_rowid.rowid_object('AAAD/BAAJAAAb90AAM') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAD/BAAJAAAB90AAM')
---------------------------------------------
                                        16321

SQL> select object_name from all_objects where object_id = 16321;

no rows selected

What does that mean?

What I would really like to know are the four statements involved in the deadlock.  Obviously we are shown one of them above, but how can I determine what the statements with the two "holds" are and what the other "waiter" is? 

Dump file /u01/app/oracle/admin/e1/adump/ora_186436_e1.trc
Oracle8 Enterprise Edition Release 8.0.4.3.0 - Production
With the Partitioning option

PL/SQL Release 8.0.4.3.0 - Production
ORACLE_HOME = /u01/app/oracle/product/8.0.4.0.0
System name: AIX
Node name: ace
Release: 3
Version: 4
Machine:
Instance name: E1
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 186436, image:

Sun Jun 20 06:15:38 2004
*** SESSION ID:(13.158) 2004.06.20.06.15.38.000
ksqded1: deadlock detected via did
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE TAHIST SET X=SYSDATE WHERE C_SEQ_NO = :b1 AND A_NO = :b2 AND AM_NO = :b3
----- PL/SQL Call Stack -----
object line object
handle number name
49d2daf4 866 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
TX-0016001b-000b0b22 20 13 X 17 15 S
TX-0018002f-000af59a 17 15 X 20 13 S
session 13: DID 0001-0014-00000001 session 15: DID 0001-0011-00000001
session 15: DID 0001-0011-00000001 session 13: DID 0001-0014-00000001
Rows waited on:
Session 15: no row
Session 13: no row
#########################################################

Dump file /u01/app/oracle/admin/e1/adump/ora_128448_e1.trc
Oracle8 Enterprise Edition Release 8.0.4.3.0 - Production
With the Partitioning option
PL/SQL Release 8.0.4.3.0 - Production
ORACLE_HOME = /u01/app/oracle/product/8.0.4.0.0
System name: AIX
Node name: ace
Release: 3
Version: 4
Machine:
Instance name: E1
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 128448, image:

Wed Jun 23 06:25:33 2004
*** SESSION ID:(37.216) 2004.06.23.06.25.33.000
ksqded1: deadlock detected via did
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE TAHIST SET X=SYSDATE WHERE C_SEQ_NO = :b1 AND A_NO = :b2 AND AM_NO = :b3
----- PL/SQL Call Stack -----
object line object
handle number name
49b3d124 866 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
TX-00180008-000af8da 17 37 X 9 19 S
TX-000e0021-000b0790 9 19 X 17 37 X
session 37: DID 0001-0011-00000001 session 19: DID 0001-0009-00000001
session 19: DID 0001-0009-00000001 session 37: DID 0001-0011-00000001
Rows waited on:
Session 19: no row
Session 37: obj - rowid = 00004D99 - AAAE2ZACFAAAHb/AB0/€
€î¿8ØN
########################################################

There are no constraints on the table.
There are no Unique indexes on table.
Oracle 8.0.4

There are lot many sql statements in the trace file. What is the significance of these statements ?

What could be the reason of this deadlock ?

Hello Tom

We recently upgraded from oracle8 (8.0.4) to oracle 8 i(8.1.7). We were using two packages which worked fine in the older version. But now the package are giving the above error. Our vendor advised us to look into the code. But the code is presently with us.

Before I get the codes which is certainly voluminous and pour over it I would like to know is this an upgrade issue that has to be resolved at our end by changing any parameters etc. . If so ,which things I need to look into.

With a lot of thanks
Devarshi

Hello Again
The error appears to have been the lack of compilation of objects of that tablespaces.The vendor advised us to run utlrp.sql under $ORACLE_HOME/rdbms/admin/
directory,which we did. The problem now has dissapered.



Thanks again
Devarshi

I am getting a self-deadlock when trying to compile a package spec only. Here is the trace file in user_dump_dest

*** 2004-08-23 17:47:00.572
*** SESSION ID:(13.39140) 2004-08-23 17:47:00.557
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object XXXX.XXXX
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
38e4c9428 38c2a4230 3905075e0 X 38c2a4230 38ec24c30 S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------


I went thru all the Notes on MetaLink, ran $ORACLE_HOME/rdbms/admin/utlrp.sql, nothing works.

This works fine on other, more controlled environments. It is failing on this development database, but still...

Help! Thanks

Yes, I edited out my object name! It is ACCNT, if you must know :)

I know it is a self-deadlock because the waiting session is the same as the blocking session (38c2a4230)

Also, not much is going on in this database, no users are connected, pretty much idle

Thanks

Turned out that table already had a FBI using one of the functions in the package spec I was compiling!

But why would the 'create or replace package' try to get a X lock on the table and/or why would the same session already have a S lock on the table?

I tried to reproduce this on a much simpler case and was unable to do so i.e. I created a FBI using a packaged function and 'create or replace package' and it worked. My situation is much more complex with lots of more inter dependencies though.

Comments? Thanks

*** 2004-09-17 14:35:59.690
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE accounts
SET
status = :b2
WHERE
id = :b1
----- PL/SQL Call Stack -----
object line object
handle number name
32ad8aa8 25 LOYALTY_OWNER.AS1_AFT_ID_STM
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-00140012-0070d74b 645 592 X 645 592 X
session 592: DID 0001-0285-0000001E session 592: DID 0001-0285-0000001E
Rows waited on:
Session 592: obj - rowid = 00004123 - AAAEEjAEeAAD+Y8AAd
(dictionary objn - 16675, file - 286, block - 1041980, slot - 29)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================

I only see one session id in the trace file . It has been consistently happening in this database .

We are oracle 9204 and running sun os 5.8 . Our application
is built using oracle forms 6.0 . This trace file is generated from the user connected to database using that application .

CREATE TABLE "LOYALTY_OWNER"."ACCOUNTS"
( "ID" NUMBER(12,0) NOT NULL ENABLE,
"ACCOUNT_TYPE" VARCHAR2(1) NOT NULL ENABLE,
"DATE_ENTERED" DATE NOT NULL ENABLE,
"SEED_FLAG" VARCHAR2(1) NOT NULL ENABLE,
"SUPPRESS_STATEMENT_FLAG" VARCHAR2(1) NOT NULL ENABLE,
"CONSOLIDATING_FLAG" VARCHAR2(1) NOT NULL ENABLE,
"CLIENT" NUMBER(12,0),
"CLIENT_PROGRAM" NUMBER(12,0),
"PORTFOLIO" NUMBER(12,0),
"BALANCE" NUMBER(9,0),
"STATUS" VARCHAR2(1),
"CYCLE_DAY" VARCHAR2(2),
"CLIENT_OPEN_DATE" DATE,
"CLIENT_ENROLLMENT_DATE" DATE,
"KEYCODE" NUMBER(12,0),
"DOING_BUSINESS_AS_ID" VARCHAR2(12),
"DOING_BUSINESS_AS_CLIENT" NUMBER(12,0),
"PI_CODE" NUMBER(12,0),
"BROKER" NUMBER(12,0),
"DENIAL_CODE" VARCHAR2(12),
"DENIAL_CODE_PROGRAM" NUMBER(12,0),
"ACCOUNT_NUMBER" VARCHAR2(50),
"MEMBER_NUMBER" NUMBER(10,0),
"CONSOLIDATED_BY" NUMBER(12,0),
"DATE_CHANGED" DATE,
"ACTIVATION_DATE" DATE,
"ACCOUNT_LOGIN" NUMBER(12,0),
"TRAVEL_AGENCY" NUMBER(12,0),
"POINTS_TO_EXPIRE" NUMBER(9,0),
"POINTS_EXPIRE_DATE" DATE,
"PARENT_ACCOUNT_NUMBER" VARCHAR2(50),
"DEPARTMENT_ID" VARCHAR2(12),
"DISPERSAL_METHOD" VARCHAR2(2),
"ACCOUNT_MAINTENANCE_DATE" DATE,
"PROFILE_MAINTENANCE_DATE" DATE,
"ENROLLMENT_METHOD_PC4" NUMBER(12,0),
"PARTNER" NUMBER(12,0),
"SITE" NUMBER(12,0),
"PROMOTION_CODE" VARCHAR2(30),
CONSTRAINT "AC_DISPERSAL_METHOD_VALUE_CK" CHECK (dispersal_method IN ('BC', 'D
D')) ENABLE,
CONSTRAINT "AC_PROF_MAINT_DATE_CK" CHECK (PROFILE_MAINTENANCE_DATE >= DATE_ENT
ERED) ENABLE,
CONSTRAINT "AC_ACCT_MAINT_DATE_CK" CHECK (ACCOUNT_MAINTENANCE_DATE >= DATE_ENT
ERED) ENABLE,
CONSTRAINT "AC_STATUS_VALUE_CK" CHECK (STATUS IN ('A', 'V', 'C', 'P', 'I')) EN
ABLE,
CONSTRAINT "AC_TYPE_CK" CHECK (( account_type = 'A'
AND balance is not null AND client_enrollment_date is not null
AND denial_code is null
AND denial_code_program is null
AND portfolio is not null ) OR ( account_type = 'M'
AND balance is not null AND client_enrollment_date is not null
AND denial_code is null AND denial_code_program is null
AND portfolio is not null ) OR
( account_type = 'P' AND balance is null
AND client_enrollment_date is null
AND client_program is not null
AND client_open_date is null
AND consolidated_by is null
AND consolidating_flag = 'N'
AND cycle_day is null
AND doing_business_as_id is null AND doing_business_as_client is null
AND pi_code is null AND portfolio is null AND seed_flag = 'N'
AND suppress_statement_flag = 'N' )) ENABLE,
CONSTRAINT "AC_ACCT_TYPE_VALUE_CK" CHECK (ACCOUNT_TYPE IN ('A', 'M', 'P')) ENABLE,
CONSTRAINT "AC_POINTS_EXPIRE_CK" CHECK ((points_to_expire IS NOT NULL
AND points_expire_date IS NOT NULL) OR (points_to_expire IS NULL
AND points_expire_date IS NULL)) ENABLE,
CONSTRAINT "AC_CYCLE_DAY_CK" CHECK (cycle_day is null or cycle_day in ('1', '2
', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16',
'17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '3
0', '31', 'ME')) ENABLE,
CONSTRAINT "AC_DBA_CLIENT_CK" CHECK (doing_business_as_client is null
OR doing_business_as_client = client) ENABLE,
CONSTRAINT "AC_ADC_PROGRAM_CK" CHECK (denial_code_program is null
OR denial_code_program = client_program) ENABLE,
CONSTRAINT "AC_SEED_VALUE_CK" CHECK (SEED_FLAG IN ('Y', 'N')) ENABLE,
CONSTRAINT "AC_SUPPRESS_VALUE_CK" CHECK (suppress_statement_flag
IN ('Y', 'N')) ENABLE,
CONSTRAINT "AC_CONSOLIDATE_VALUE_CK" CHECK (CONSOLIDATING_FLAG
IN ('Y', 'N')) ENABLE,
CONSTRAINT "AC_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 16003072 NEXT 8003584 MINEXTENTS 1 MAXEXTENTS 248
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "L" ENABLE,
CONSTRAINT "AC_UK" UNIQUE ("MEMBER_NUMBER")
USING INDEX PCTFREE

*** 2004-09-17 14:35:59.690
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE accounts
SET
status = :b2
WHERE
id = :b1
----- PL/SQL Call Stack -----
object line object
handle number name
32ad8aa8 25 LOYALTY_OWNER.AS1_AFT_ID_STM
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-00140012-0070d74b 645 592 X 645 592 X
session 592: DID 0001-0285-0000001E session 592: DID 0001-0285-0000001E
Rows waited on:
Session 592: obj - rowid = 00004123 - AAAEEjAEeAAD+Y8AAd
(dictionary objn - 16675, file - 286, block - 1041980, slot - 29)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================

In the trace file , AS1_AFT_ID_STM is a trigger on account_status table which fires for insert/delete for each statement. The update statement is part of the code in that trigger.

I've told you exactly what I wanted to see but I'm not getting it.  I specifically asked for import/export so I can see things like this, it is relevant.  I'm not getting what I required/requested. 

I'll guess, since you won't give me what I ask for.

You are using an autonomous transaction.

You are deadlocking with yourself.

I can do that easily:

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE T SET Y = Y WHERE X = :B1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x5dc5228c         4  OPS$TKYTE.T
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-0005002d-00001a40        17      14     X             17      14           X
session 14: DID 0001-0011-00000077  session 14: DID 0001-0011-00000077
Rows waited on:
Session 14: obj - rowid = 000078D5 - AAAHjVAAHAAAACOAAA
  (dictionary objn - 30933, file - 7, block - 142, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================


Tah-Dah, Looks familar.  (when given the information requested, I'm much more successful -- even though I haven't gotten what I asked for..... I'm pretty sure this is it)


Are you ssing an autonomous transaction?

If so, you almost certainly have a bug (nasty ones that I could show you!!) in your developed code.

Data integrity issues.

Transactional Issues.

Design flaws (if you felt the need for them)

if you do, you must remove it -- I am now convinced that autonomous transactions as a feature was a huge mistake.  I've never seen one used in "the real world(tm)" in a manner that is safe.



But, given what I see, I'm almost sure thats what you've done here, you are shooting yourself in the foot.


In the future, please just post what is asked for or say "sorry, I cannot, I'll try some things myself".  


---------------- test case for how to self deadlock in 3 seconds or less -----

drop table t;
                                                                                                                   
create table t ( x int, y int );
                                                                                                                   
create or replace trigger t before update on t
for each row
declare
    pragma autonomous_transaction;
begin
    update t set y = y where x = :new.x;
    commit;
end;
/
                                                                                                                   
insert into t values ( 1, 1 );
commit;
update t set y = y where x = 1;
 

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: httpd@web3.att.teetimes.com (TNS V1-V3)
application name: httpd@web3.att.teetimes.com (TNS V1-V3), hash value=0
Current SQL Statement:
DELETE FROM PARTY WHERE PARTY_ID = :b1
End of information on OTHER waiting sessions.

yes it is.
That is last statement in the Stored Procedure. Before it, all child records are deleted.

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.

Can you tell me what does it mean when you have 2 same resource names, but sessions are different?
What is resource name?

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

ops$tkyte@ORA9IR2> select to_number( '743c', 'xxxx' ) from dual;
 
TO_NUMBER('743C','XXXX')
------------------------
                   29756

they were deadlocked on that object...

do this:


set echo on
drop table t;
create table t ( x int );
insert into t select rownum from all_users;
commit;
select to_char(object_id,'xxxxxxxx') from user_objects where object_name = 'T';
lock table t in share mode;
set echo off
prompt in another session issue:
prompt lock table t in share mode;;
set echo on
pause
set echo off
prompt in another session issue:
prompt update t set x = x where x = 2;
set echo on
update t set x = x where x = 1;


and the trace file will have TM-xxxxxxxx -- that matches the select to_char output above. 

Tom,

We give below the dead lock graph and the Cursor frame allocation dump. Appears to be due to unique indexes..right? If so, how to correct this? Pl. explain...

DEADLOCK DETECTED
Current SQL statement for this session:
update FINANMAP set MAP_CLBAL=(NVL(MAP_CLBAL,0)+:b0) where ROWID=:b1
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-0004005c-0000db0f 12 11 X 13 13 S
TX-00050059-0000daaf 13 13 X 12 11 S
session 11: DID 0001-000C-00000002 session 13: DID 0001-000D-00000002
session 13: DID 0001-000D-00000002 session 11: DID 0001-000C-00000002
Rows waited on:
Session 13: no row
Session 11: no row
===================================================

Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=1 size=56 offset=0
bfp=018feae0 bln=22 avl=05 flg=05
value=-96750
bind 1: dty=1 mxl=32(31) mal=00 scl=00 pre=00 oacflg=00 oacfl2=11 size=0 offset=24
bfp=018feaf8 bln=32 avl=18 flg=01
value="AAAAvUAAbAAAPGXAAa
=========================================================

Thanks...

Tom,

What could be the inference drawn from this when not due to unique index? How to take this forward towards understanding and fixing it up? Pl. suggest.

Thanks

Tom,

I give below the table and index details.

Table name:FINANMAP

Column Name Data type Null?
MAP_CITY_CODE NUMBER(2) NOT NULL
MAP_IND VARCHAR2(2) NOT NULL
MAP_GRP_NO VARCHAR2(3) NOT NULL
MAP_COL_CODE VARCHAR2(6)
MAP_VER_NO NUMBER(4) NOT NULL
MAP_ACC_NO VARCHAR2(10)
MAP_OPBAL NUMBER(15,2)
MAP_CLBAL NUMBER(15,2)
MAP_ACTIV_STATUS VARCHAR2(1)
MAP_MAKER VARCHAR2(12)
MAP_MAKE_DATE DATE
MAP_AUTHORIZER VARCHAR2(12)
MAP_AUTH_DATE DATE

Index name:FINANMAP_IDX on the following columns

MAP_CITY_CODE
MAP_COL_CODE
MAP_GRP_NO
MAP_IND
MAP_VER_NO

There is no foreign key involved here...This is a stand alone table. Update is done using rowid. Pl. suggest the way to move ahead towards resolution.

Thanks

Tom,
Could you please help me on this. Below is an extract from my trace file

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00070015-0000c13d 23 26 X 13 30 X
TX-001b0008-0000c194 13 30 X 23 26 X
session 26: DID 0001-0017-0000004E session 30: DID 0001-000D-000000D6
session 30: DID 0001-000D-000000D6 session 26: DID 0001-0017-0000004E
Rows waited on:
Session 30: obj - rowid = 00001778 - AAAB2/AANAAAIWnAAI
(dictionary objn - 6008, file - 13, block - 34215, slot - 8)
Session 26: obj - rowid = 00001778 - AAAB2/AANAAAIx4AAB
(dictionary objn - 6008, file - 13, block - 35960, slot - 1)


now if i do
select dbms_rowid.rowid_object( 'AAAB2/AANAAAIWnAAI' ) from dual;

The output is :
DBMS_ROWID.ROWID_OBJECT('AAAB2/AANAAAIWNAAI')
---------------------------------------------
7615

whereas there is this line
dictionary objn - 6008
which specifes that object number is 6008 and this is the correct object number (object number of the table that is involved in the deadlock ).

I wanted to know the reason behind getting two different object ids.

Thank you tom for your prompt reply. This is the first time that i had asked you a question and i was thinking that i would be lucky if you answer it in a month. But you made my day.

The deadlock trace file shows the cursors that are currently executing. I wanted to know if this information is fetched from V$SQL_CURSOR. Is it possible to identify the session which executed that cursor.

Thanks Tom,
I thought the cursor information was for all the sessions that were executing when the deadlock occurred.
Currently we are facing a deadlock situation in which two sessions are trying to update the same table. From the trace file i can find out which row were they exactly trying to update. The situation is like this.

hr@ORCL.US.ORACLE.COM> desc employees
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


if a deadlock happens while session 1 is trying to update data for employee number 10 and session 2 is trying to update data for employee number 20, and the locks that they are holding on the rows are TX exclusive. In such a situation is it safe to assume that session 1 is waiting on lock on employee 20 and session 2 is waiting on lock on employee 10, or could there be any other possibility.

Thanks a lot tom.

No tom there isn't an issue of unindexed foreign key. I had run the script given in your book Expert One-On-One to detect unindexed foreign keys.
The table is actually an error log table ( and not employees table as i had put in my previous post )which stores information about errors that occur during the session. There are very frequent updates on this table.

Hi,
and another one on indexes. Another type of deadlock can occur with BITMAP indexes too.

Please have a look at :
Note 171795.1 on metalink.oracle.com

It helps a lot to find why there are deadlocks occuring with sql statements that do not ask for the same row.....


Regards,

Virgile

Hi Tom,

came across an deadlock issue.
After analysing the trace files and later identified the object using dbms_rowid package which caused the deadlock.It was an index on a table which caused the problem.The table doesn't have any unindexed foreign key.It is a base table which has around 0.2 million records and gets the data through global temp tables which are used as interface.

what is the better way to optimize it and to prevent the
deadlock in future.The data size may grow large in future.
Provide your suggestions.

Tom,
Is it possible to get some information relevant to deadlocks from statspack report? I have been asked to analyze the report 1 hour before the deadlock occurred and the hour during which the deadlock occurred.


Thanks,
Khalid

Tom,
yes the deadlock occured in the index.It was bitmap index.
Nonunique. Do i need to do any design changes?

Tom,

What other type index will avoid the reoccurance of deadlock.?is it better to drop the index.

Thanks,
Senthil.

Tom,

I am trying to understand a deadlock problem ,occuring at one of our production site. I have attached a part of
the trace file for your reference
----------------------------------------------------------------------------------
Dump file /appl/oracle/admin/i_live4/udump/i_live4_ora_88956.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /appl/oracle/product/920
System name: AIX
Node name: LGWBRS
Release: 1
Version: 5
Machine: 0052CA5E4C00
Instance name: i_live4
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 88956,

*** 2005-09-28 09:15:55.185
*** SESSION ID:(100.22363) 2005-09-28 09:15:55.120
DEADLOCK DETECTED
Current SQL statement for this session:
begin bag_detail ( parameters... ) ; end ;
----- PL/SQL Call Stack -----
object line object
handle number name
700000020bc9380 82 package body SYS.DBMS_LOCK
700000020bc9380 98 package body SYS.DBMS_LOCK
7000000209f6698 114 package body IBRPRD.LOCKS
7000000239cd990 393 procedure IBRPRD.BAG_DETAIL
700000023b7db10 1 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
UL-4063b1c4-00000000 12 100 X 16 94 X
UL-4063b1e6-00000000 16 94 X 12 100 X
session 100: DID 0001-000C-00000E3B session 94: DID 0001-0010-00000039
session 94: DID 0001-0010-00000039 session 100: DID 0001-000C-00000E3B
Rows waited on:
Session 94: obj - rowid = 00001ACE - AAAFwZAAKAAABoMAAA
(dictionary objn - 6862, file - 10, block - 6668, slot - 0)
Session 100: obj - rowid = 000017FA - AAABoUAAGAAAAAAAAA
(dictionary objn - 6138, file - 6, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 94:
pid=16 serial=11189 audsid=475244 user: 29/IBRIS
O/S info: user: ibris, term: , ospid: 47334, machine: LGWBR
program:
application name:
Current SQL Statement:

begin bag_detail ( parameters ..) ; end ;
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
----------------------------------------------------------------

I have following questions on this.

1. In the thread i have seen locks on resources as TM or TX only, where as in this trace file i got UL but the resources are having exclusive locks and are waiting for exclusive locks as well. WHAT IS UL ?

2.i tried to find out the objects involved in this deadlock, following was output
for session 100 : dictionary objn 6138 gives me a table name and rowid 'AAABoUAAGAAAAAAAAA' also gives me same table name from data_object_id ..fine
for session 94 : dictionary objn 6862 gives me a index name but its on a different table (than obj 6138), but
when i queried for the rowid 'AAAFwZAAKAAABoMAAA' for data_object_id it doesn't give me any result ( i tried
dba_objects, all_objects). So why i am not getting the same index name via rowid for session 94.


3. i tried to get object name from 4063b1c4 and 4063b1e6 via quering all_objects for object_id in (to_number('4063b1c4','xxxxxxxx),('4063b1e6','xxxxxxxx'));
but even that does not resulted any rows. Why its so. is this is not a resource id (object_id).


Thanks
Vivek

Tom,

Would appreciate if you could help us in analyzing the trace.

/u01/oradba/admin/NP4/udump/np4_ora_18541.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /u01/oradba/product/9.2.0.6
System name: SunOS
Node name: chisdp76
Release: 5.8
Version: Generic_117350-20
Machine: sun4u
Instance name: NP4
Redo thread mounted by this instance: 1
Oracle process number: 210
Unix process pid: 18541, image: oracle@chisdp76 (TNS V1-V3)

*** SESSION ID:(203.31993) 2006-01-04 08:39:06.088
DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO CURRENTDAYSUMMARIES (POSTINGDATE, ABANUM, ACCTNUM, BAICODE, AMOUNT, FUNDSTYPE, IMMEDIATEAVAILAMT, DAY1AVAILAMT, DAY2PLUSAVAILAMT, ITEMCNTIND, ITEMCNT, LOADDATE, LOADFEEDNUM, LASTUPDATED, LASTFEEDNUM) VALUES (:B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , SYSDATE, :B1 , SYSDATE, :B1 )
----- PL/SQL Call Stack -----
object line object
handle number name
5bd2f0e0 201 package body NBD_IR.IRLOADER
5bd2f0e0 512 package body NBD_IR.IRLOADER
644ac970 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
TX-001f0019-001ba573 210 203 X 180 323 S
TX-002c000f-0000155a 180 323 X 210 203 S
session 203: DID 0001-00D2-0000018F session 323: DID 0001-00B4-000000FE
session 323: DID 0001-00B4-000000FE session 203: DID 0001-00D2-0000018F
Rows waited on:
Session 323: obj - rowid = 00000B8B - AAAx7DAATAAATz+AAA
(dictionary objn - 2955, file - 19, block - 81150, slot - 0)
Session 203: obj - rowid = 00005CD0 - AAAx7FACGAAAAAAAAA
(dictionary objn - 23760, file - 134, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 323:
pid=180 serial=54171 audsid=23391105 user: 106/<none>
O/S info: user: nbdxfer, term: , ospid: 18537, machine: chisdp76
program: sqlplus@chisdp76 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:

INSERT INTO CURRENTDAYSUMMARIES (POSTINGDATE, ABANUM, ACCTNUM, BAICODE, AMOUNT, FUNDSTYPE, IMMEDIATEAVAILAMT, DAY1AVAILAMT, DAY2PLUSAVAILAMT, ITEMCNTIND, ITEMCNT, LOADDATE, LOADFEEDNUM, LASTUPDATED, LASTFEEDNUM) VALUES (:B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , SYSDATE, :B1 , SYSDATE, :B1 )
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 56466510, call: 5ab867a4, xact: 59f88ab4, curses: 56610708, usrses: 56610708
----------------------------------------
SO: 56466510, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=210, calls cur/top: 5ab867a4/5aca14cc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 50
last post received-location: kcbzww
last process to post me: 5645f508 145 0
last post sent: 0 0 50
last post sent-location: kcbzww
last process posted by me: 5645f508 145 0
(latch info) wait_event=0 bits=10
holding 20006564 Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Context saved from call: 0
state=busy
recovery area:
Dump of memory from 0x564353B8 to 0x564353C0

The objects are CURRENTDAYSUMMARIES (Object ID 2955) and PK_CURRENTDAYSUMMARIES (Object ID 23760)

Also I find this in the trace file.

SO: 566be718, type: 4, owner: 564ce2dc, flag: INIT/-/-/0x00
(session) trans: 58d5c0c8, creator: 564ce2dc, flag: (100041) USR/- BSY/-/-/-
/-/-
DID: 0001-028F-000000ED, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, sql: 5bd23b64, psql: 5bd23b64, user: 106/<none>
O/S info: user: nbdxfer, term: , ospid: 12412, machine: chisdp76
program: sqlplus@chisdp76 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
last wait for 'enqueue' blocking sess=0x5667a058 seq=7790 wait_time=2937613
name|mode=54580006, id1=1f0022, id2=1d3501
temporary object counter: 0

What is wait_time here.

Thx in advance.

Tom, thx for the prompt response.

The insert in performed inside a pl/sql block. The process can be summarized as follows.

1. The data is loaded in a temporary table using sqlldr
2. The pl/sql procedure is called from a ksh script with a parameter that is used in the select criteria from the temporary table in a cursor. Depending on one of the data column, the data is either inserted or updated in to this table (i.e. CURRENTDAYSUMMARIES).

These processes (ksh with different parameter values) run in parallel depending on the number of transactions pending for processing.

There is only one commit in the stored procedure at the end of processing. (we suspect, the deadlock could be because of this large insert too. but no way of proving this to the development team as these deadlocks are very random and can not be reproduced in our test environment).

The table where the data is being inserted is CURRENTDAYSUMMARIES. One of the session (323 from the trace) is waiting on for a shared lock on this table. The second session in the deadlock graph (203) is waiting on for a similar lock on the Primary Key index of the same table (i.e PK_CURRENTDAYSUMMARIES).

Please let me know if you need additional details. I can post part of the code if required (to make things clear).

Do you have bitmap indexes on this table. They do lock many rows and can easily cause deadlock situations during concurrent inserts.

At first glance, I would assume you had a problem with bitmap indexes, or duplicate data sets being loaded in different orders - however, there is an oddity about the trace file: look at the rowids reported:

Session 323: obj - rowid = 00000B8B - AAAx7DAATAAATz+AAA
(dictionary objn - 2955, file - 19, block - 81150, slot - 0)
Session 203: obj - rowid = 00005CD0 - AAAx7FACGAAAAAAAAA
(dictionary objn - 23760, file - 134, block - 0, slot - 0)

NOTE that session 203 is waiting on block ZERO of file 134. But you can't get a row into block zero of a file !

It's also a little suspect (though not necessarily an error) that session 323 has a row wait on object 2955 - Enterprise edition loads so much into the data dictionary that 2955 is usually an object owned by SYS.

This makes it look as if the deadlock dump has been reached through an "unexpected code path" and dumped some silly values.

Is the deadlock an Oracle error 60, or an Oracle error 4020 ?

We do not have bitmap indexes.

Jonathan, as this is one of the oldest database, it was probably created in 7.x (I am not sure though).
The deadlock error we receive is ORA-60.

If duplicate datasets are being loaded, the error should have been "Unique constrain violated" as one of the object in the dead lock graph is the primary key itself. However, the data is not committed till all the data from the temp table is processed (from a batch). Do you think the deadlock is as a result of attempt to update segment header by two processes at the same time in uncommitted transactions?

We all agree that most of the deadlocks are as a result of poor application code. The critical part is that we need to reproduce this and come up with a solution (changes to code) for the developers.

Thx in advance.

Tom,

Not sure if you are referring to the object_id returned from the row_id. The objects are the same.

SQL>select dbms_rowid.rowid_object('AAAx7DAATAAATz+AAA') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAX7DAATAAATZ+AAA')
---------------------------------------------
                                       204483

SQL>select object_name from dba_objects where data_object_id=204483;

OBJECT_NAME
-----------------------------
CURRENTDAYSUMMARIES

SQL>select dbms_rowid.rowid_object('AAAx7FACGAAAAAAAAA') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAX7FACGAAAAAAAAA')
---------------------------------------------
                                       204485

SQL>select object_name from dba_objects where data_object_id=204485;

OBJECT_NAME
---------------------------
PK_CURRENTDAYSUMMARIES

The only other objects in the trace file are the package name where the procedure is located. 

Tom, thanks for taking time to review my postings.

As I mentioned above, both the object ID from the trace file and the data_object_id from the row_id refer to the same objects.

SQL>select object_name, object_id from dba_objects where data_object_id=204485;

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
PK_CURRENTDAYSUMMARIES              23760

SQL >select object_name, object_id from dba_objects where data_object_id=204483;

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
CURRENTDAYSUMMARIES                  2955

I am also trying to simulate (using the code below) the actual process by creating couple of temporary tables. I am able to re-produce one deadlock scenario where the lock(s) requested is shared. 

create table t1 (col1  number, col2 varchar2(100));
create table t2 (col1  number, col2 varchar2(100));
insert into t1 select object_id, object_name from dba_objects;

I created a procedure as follows.

create or replace procedure pop_t2 ( begin_id number, end_id number, upd_ins varchar2) as
  cursor c1 is select * from t1 where col1 between begin_id and end_id;
begin
  for rec in c1 loop
     if ( upd_ins = 'I') then
         insert into t2 values (rec.col1, rec.col2);
     else
         update t2
            set col2 = rec.col2
          where col1 = rec.col1;
     end if;
  end loop;
end;
/

Now open two sessions.

Sess 1                    sess 2
-------                   -------
exec pop_t2(1,2500,'I);
PL/SQL procedure successfully completed.

                          exec pop_t2(2000,4000,'I);

exec pop_t2(3000,5000,'I');
                          ---> I get deadlock here is sess 2
PL/SQL procedure successfully completed.

However, the original request was exclusive for one and shared for another. This is some thing I was not able to reproduce yet. I'll keep you posted if I make any progress.

 

Thanks for clarifying the rowid/object problem. I should have realised you were quoting data object ids when you were in the 200,000 range, rather than the logical object ids in the trace file.

There are still two things about the rowids that puzzle me - first, you don't normally get them for pk index deadlocks, you usually get 'No Rowid' when the problem is waiting to see if a conflicting unique value is going to commit or rollback. Second, that block 0/row 0 is bizarre (and no, it wouldn't be to do with Oracle 7 - block 0 just isn't available).

I ran your test case - at first with no problems. I assume that you missed out the primary key declaration on col1, so I added one and retried - with no problems, session 2 humg as expected (waiting for row 2000 from session 1 to commit), and session 1 carried on happily inserting rows outside the range.

Then I tried a few variations:
Are you using deferrable primary key constraints ? i.e. non-unique indexes supported unique keys ?
Are you using ASSM for the tablespaces ?

I found that I could get the deadlock from your test case (thought not a perfect for your original production problem) with ASSM and deferrable primary keys - example of deadlock graph below:

Resource Name process session holds waits process session holds waits
TX-0009000e-00016aa5 14 10 X 13 9 S
TX-00050017-00016ab8 13 9 X 14 10 S
session 10: DID 0001-000E-00000003 session 9: DID 0001-000D-0000002C
session 9: DID 0001-000D-0000002C session 10: DID 0001-000E-00000003
Rows waited on:
Session 9: obj - rowid = 0000005A - AAAABaAABAAAAAAAAA
(dictionary objn - 90, file - 1, block - 0, slot - 0)

NOTE -- the rowid reported is object 90 (!), block 0, slot 0 - clearly an impossibility (90 is objauth$, owned by sys). There is clearly a bit of code reporting garbage - possibly a bit of code that should not be called.

I haven't done exhaustive tests, but this problem was not consistently repeatable. It dependend on
the process id of the session creating the table.
the process id of the session that did the first insert.
the process id of the session the did the second insert.

On some runs, I didn't get a deadlock.
On some runs, I got a deadlock, but no rowids dumped

I think you should pass your test case to Metalink as an SR, but check very carefully that you can explain EXACTLY how to make it produce the deadlock. It is possible that your deadlock problem is NOT entirely due to your application.

Jonathan,

Thanks you very much for the followup. I had PK constraint defined in the test case, and missed to include while copy-pasting the code from trace files. It was my bad.

We are not using deferrable primary key indexes and non-unique indexes to support unique/primary keys. The tablespaces (production) for these objects are dictionary managed.

During my tests, I was able to consistently reproduce deadlock on one of the SYS object (similar to the one you got). Then I got side tracked trying to reproduce a graph similar to what we got in production. One other test I am trying is to reproduce this while running from 6 different sessions simultaneously. (Max of six processes run in production.) I may have to borrow some code from the actual procedure in order to do this.

I'll open TAR with support (if required), once I gather enough information.

It's just occurred to me why (possibly) we are seeing file 0/block 0 "rowids" with (in my case) ridiculous object numbers.

I suspect the code is reporting the row_wait_XXX columns from v$session - and some time ago, these changed to report ANY block related waits - originally they showed only row lock waits. However, the columns are not cleared properly after the wait - so the strange values in the deadlock trace could be the left-overs from an earlier wait being reported by accident.

Tom, I understand the basic problem why deadlocks can happen with bitmap indexes in concurrent environments. But, could you explain what exactly is happening in the following case?

We're using Oracle Database 10g Enterprise Edition Release 10.1.0.3.0.

Rows waited on:

Session 148: obj - rowid = 0002E779 - AAAud5AAPAAB1gKAAA

(dictionary objn - 190329, file - 15, block - 481290, slot - 0)

Session 136: obj - rowid = 0002E778 - AAAud4AAMAACjQ0AAA

(dictionary objn - 190328, file - 12, block - 668724, slot - 0)

These objects turn out to be non-unique BITMAP indexes on the same table T1 (both are one-column indexes).

The losing session's current SQL is an insert into T2, and the winning session's current SQL is an insert into T3. I don't understand this. My specific questions are:

1) why am I seeing inserts into some other tables as the current statements?

2) I don't quite understand how locks in _different_ indexes would produce a deadlock. Could the order in which indexes are updated be different in different sessions? Or, does Oracle lock actual table rows, even though the trace says the objects are indexes?

Thanks!

-- application only inserts into these tables, no updates or deletes


-- deadlock on indexes UPLOAD_FK0 and UPLOAD_FK1
-- losing session's current SQL is inserting into SMR1
-- winning session's current SQL is inserting into KMR3

-- Including common parent tables:
-- TIME_DIM: static
-- DATE_DIM: new records are inserted occasionally

CREATE TABLE DATE_DIM
(
DATE_ID NUMBER(16) NOT NULL PRIMARY KEY,
DATE_DATE DATE NOT NULL,
DATE_YYYY NUMBER(4) NOT NULL,
DATE_YY NUMBER(2) NOT NULL,
DATE_MM NUMBER(2) NOT NULL,
DATE_MON VARCHAR2(10 BYTE) NOT NULL,
DATE_MONTH VARCHAR2(30 BYTE) NOT NULL,
DATE_DD NUMBER(2) NOT NULL,
DATE_DAY VARCHAR2(30 BYTE) NOT NULL,
DATE_QTR NUMBER(1) NOT NULL,
WEEK_NO NUMBER(2) NOT NULL,
UTC_MILLI_0000 NUMBER(20) NOT NULL,
UTC_MILLI_2359 NUMBER(20) NOT NULL,
CREATE_TIME TIMESTAMP(6),
WEEK_NO_ISO NUMBER(2)
);

CREATE TABLE TIME_DIM
(
TIME_ID NUMBER(16) NOT NULL PRIMARY KEY,
TIME_HH24 NUMBER(2) NOT NULL,
TIME_HH12 NUMBER(2) NOT NULL,
TIME_AMPM VARCHAR2(2 BYTE) NOT NULL,
TIME_MI NUMBER(2) NOT NULL,
DAY_MILLI NUMBER(20) NOT NULL,
CREATE_TIME TIMESTAMP(6)
);


CREATE TABLE UPLOAD
(
UPLOAD_ID NUMBER(16) NOT NULL PRIMARY KEY,
DATE_ID NUMBER(16) NOT NULL,
TIME_ID NUMBER(16) NOT NULL,
UPLOAD_MILLI NUMBER(20) NOT NULL
);

CREATE BITMAP INDEX UPLOAD_FK0 ON UPLOAD (DATE_ID);
CREATE BITMAP INDEX UPLOAD_FK1 ON UPLOAD (TIME_ID);

ALTER TABLE UPLOAD ADD (
CONSTRAINT UPLOAD_FK0
FOREIGN KEY (DATE_ID)
REFERENCES DATE_DIM (DATE_ID)
ON DELETE CASCADE);

ALTER TABLE UPLOAD ADD (
CONSTRAINT UPLOAD_FK1
FOREIGN KEY (TIME_ID)
REFERENCES TIME_DIM (TIME_ID)
ON DELETE CASCADE);



CREATE TABLE SMR1
(
SMR1_ID NUMBER(16) NOT NULL PRIMARY KEY,
DATE_ID NUMBER(16),
TIME_ID NUMBER(16),
RESULT NUMBER(10),
MSG VARCHAR2(2000 BYTE)
);

CREATE BITMAP INDEX SMR1_FK0 ON SMR1 (DATE_ID);
CREATE BITMAP INDEX SMR1_FK1 ON SMR1 (TIME_ID);

ALTER TABLE SMR1 ADD (
CONSTRAINT SMR1_FK0
FOREIGN KEY (DATE_ID)
REFERENCES DATE_DIM (DATE_ID)
ON DELETE CASCADE);

ALTER TABLE SMR1 ADD (
CONSTRAINT SMR1_FK1
FOREIGN KEY (TIME_ID)
REFERENCES TIME_DIM (TIME_ID)
ON DELETE CASCADE);


CREATE TABLE KMR3
(
KMR3_ID NUMBER(16) NOT NULL PRIMARY KEY,
DATE_ID NUMBER(16),
TIME_ID NUMBER(16),
RESULT NUMBER(10),
MSG VARCHAR2(2000 BYTE)
);

CREATE BITMAP INDEX KMR3_FK0 ON KMR3 (DATE_ID);
CREATE BITMAP INDEX KMR3_FK1 ON KMR3 (TIME_ID);

ALTER TABLE KMR3 ADD (
CONSTRAINT KMR3_FK0
FOREIGN KEY (DATE_ID)
REFERENCES DATE_DIM (DATE_ID)
ON DELETE CASCADE);

ALTER TABLE KMR3 ADD (
CONSTRAINT KMR3_FK1
FOREIGN KEY (TIME_ID)
REFERENCES TIME_DIM (TIME_ID)
ON DELETE CASCADE);

T1=UPLOAD
T2=SMR1
T3=KMR3

The trace file says:
- deadlocked objects are indexes UPLOAD_FK0 (session 1) and UPLOAD_FK1 (session 2)
- session 1 is inserting into SMR1
- session 2 is inserting into KMR3

Neither transaction is modifying the parent tables DATE_DIM and TIME_DIM, so I don't think they matter. I included them just in case, for completeness.

Tom, buddy, pal man do I need your help like never before.....

I had some kind of accident, I'll tell you what I know.
On Friday afternoon I tried to get Jon's oerr utility working. I stopped and came in this morning and picked it up again. I wasn't working so I went into sys to see if I could find the procedure and try executing it from the database (it's complied in SYS). I noticed I had a lot of invalid objects (In TOAD) so I tried compiling them. Then I got a deadlock error on one of the sys packages. So I rebooted. Now I can't even login without getting this:

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Jan 23 10:11:01 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-04020: deadlock detected while trying to lock object SYS.DBMS_STANDARD


ERROR:
ORA-04020: deadlock detected while trying to lock object SYS.DBMS_STANDARD


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL

There's all kinds of stuff in the trace file, I'll post what I think is the useful part:

ORA-04020: deadlock detected while trying to lock object SYS.DBMS_STANDARD
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
2D5F36EC 2BB2485C 2CBC8554 X 2BB2485C 2CBDB224 X
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 2CBC8554, type: 51, owner: 2C3779BC, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=2cbc8554 handle=2d5f36ec request=X
call pin=00000000 session pin=00000000
htl=2CBC8590[2CBDAF90,2CBE4C10] htb=2CBE4C10
user=2bb2485c session=2bb251cc count=0 flags=[00] savepoint=3892
LIBRARY OBJECT HANDLE: handle=2d5f36ec
name=SYS.DBMS_STANDARD
hash=2ca03d69 timestamp=12-13-2005 15:01:06
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0005-0005 lock=X pin=X latch#=1
lwt=2D5F3704[2CBC8564,2CBC8564] ltm=2D5F370C[2D5F370C,2D5F370C]
pwt=2D5F371C[2D5F371C,2D5F371C] ptm=2D5F3774[2D5F3774,2D5F3774]
ref=2D5F36F4[2D5F36F4, 2D5F36F4] lnd=2D5F3780[2C5F7F00,2D561C90]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
2cbdb224 2bb2485c 2bb2485c 1 X [00]
LOCK WAITERS:
lock user session count mode
-------- -------- -------- ----- ----
2cbc8554 2bb2485c 2bb251cc 0 X
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
2cbc8824 2bb2485c 2bb2485c 0 1 X 0001
LIBRARY OBJECT: object=2d579380
type=PCKG flags=EXS/LOC/BCM[0005] pflags=NST/DBG [103] status=INVL load=0
DEPENDENCIES: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 2d579450 2d5dfb58 I/P/A 0 NONE
2 2d5dfb08 2d556848 I/-/A 0 NONE
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 2CBDB224, type: 51, owner: 2C38F008, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=2cbdb224 handle=2d5f36ec mode=X
call pin=00000000 session pin=00000000
htl=2CBDB260[2CBE4C10,2CBDAF90] htb=2CBE4C10
user=2bb2485c session=2bb2485c count=1 flags=[00] savepoint=3564
LIBRARY OBJECT HANDLE: handle=2d5f36ec
name=SYS.DBMS_STANDARD
hash=2ca03d69 timestamp=12-13-2005 15:01:06
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0005-0005 lock=X pin=X latch#=1
lwt=2D5F3704[2CBC8564,2CBC8564] ltm=2D5F370C[2D5F370C,2D5F370C]
pwt=2D5F371C[2D5F371C,2D5F371C] ptm=2D5F3774[2D5F3774,2D5F3774]
ref=2D5F36F4[2D5F36F4, 2D5F36F4] lnd=2D5F3780[2C5F7F00,2D561C90]
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
2cbdb224 2bb2485c 2bb2485c 1 X [00]
LOCK WAITERS:
lock user session count mode
-------- -------- -------- ----- ----
2cbc8554 2bb2485c 2bb251cc 0 X
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
2cbc8824 2bb2485c 2bb2485c 0 1 X 0001
LIBRARY OBJECT: object=2d579380
type=PCKG flags=EXS/LOC/BCM[0005] pflags=NST/DBG [103] status=INVL load=0
DEPENDENCIES: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 2d579450 2d5dfb58 I/P/A 0 NONE
2 2d5dfb08 2d556848 I/-/A 0 NONE
--------------------------------------------------------

I'm hoping you can make an educated guess as to what the heck happened because I've never experienced anything like this. Thanks a lot.

No this is not a production system, I mentioned it's my own sandbox on my pc. Also, I know you're not support, it says specifically in the tracefile though "Do not contact support for this".

So it's ok to leave those sys objects invaild? I didn't realize that's normal. I never touch them so I don't know how they became invalid in the first place.

If you wouldn't mind suggesting some possible solutions, I'm kinda dead in the water otherwise. Like I said, it's my own DB so don't worry about screwing things up worse.

I'm a little surprised it's this easy to hose an Oracle database to be honest.

Oh and by the way, I ran that in sys because Jon's article said to:

</code> http://www.jlcomp.demon.co.uk/oerr.html <code>

Thanks for the help.

Yes I can startup, shutdown.  I cannot compile the package though:

SQL> alter package SYS.DBMS_STANDARD compile;
alter package SYS.DBMS_STANDARD compile
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
SYS.CDC_ALTER_CTABLE_BEFORE


SQL>

What I did to cause this was try to compile all the packages in the sys schema.  That's about is exact as I can get.  I thought if I compiled everything, Oracle would be smart enough to compile things in the correct order so things would not cascade and effect each other and the invalids would go away.

How can something be deadlocked after I bounce the instance??  Any other ideas you have would be greatly appreaciated! 

See what you make of this:

SQL> alter trigger SYS.CDC_ALTER_CTABLE_BEFORE disable;

Trigger altered.

SQL> alter package SYS.DBMS_STANDARD compile;

Package altered.

SQL> alter trigger SYS.CDC_ALTER_CTABLE_BEFORE enable;

Trigger altered.

Now I get:

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Jan 23 13:07:38 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-04020: deadlock detected while trying to lock object
SYS.CDC_ALTER_CTABLE_BEFORE


ERROR:
ORA-04020: deadlock detected while trying to lock object
SYS.CDC_ALTER_CTABLE_BEFORE


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Can just disable that trigger?  Do you know what it does? 

CDC is Change Data Capture. Are you using that feature?

-Mark

See $ORACLE_HOME/rdbms/admin/rmcdc.sql

But, I'd advise doing some reading and not just running it blindly. Or, not running it at all if some users on the database need to use CDC.

-Mark

Nope, no deletes or updates, only inserts into UPLOAD, SMR1 and KMR3.

I ran some inserts in two sqlplus sessions, but wasn't able to reproduce. Didn't expect to reproduce it, really, because all we're doing is inserts.

In every transaction, insert into UPLOAD comes first (if at all), then we insert into SMR1 and KMR3, a few records into each, in alternating order, for example: SMR1, KMR3, SMR1, KMR3.

Anyway, the issue is probably with bitmap indexes. We're going to recreate them as regular indexes, and hopefully that will get rid of deadlocks. I was just hoping to understand exactly how the deadlock occured, to educate myself and team.

Thank you Tom and Mark. I think things are back to normal. I'm not really sure what happened, but after a little while I enabled the trigger again and things seem to be ok. No log on errors anyway. Unless this cgc feature is part of installing java in oracle, I don't recall installing it. (This experience is one more thing to add to my list of reasons why I don't like triggers....)

I spoke too soon, "everything seems ok", kiss of death right there. I have a really wack performance issue. If you want me to post on an appropriate thread I will, but I think it's related.

Here is my deadlock trace file:


*** SERVICE NAME:(SYS$USERS) 2006-04-19 08:49:15.140
*** SESSION ID:(1576.56864) 2006-04-19 08:49:15.140
DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO WG2KC_LOG (ID, CUSTOMER_ID, TRANS_DATE, TRANS_ID, AMOUNT, BALANCE, TRANS_TYPE, CHIP_TYPE, ROUND_ID) VALUES (WG2K_CHIP_ID_SEQ.N
EXTVAL, :B5 , SYSDATE, :B4 , :B3 , :B2 , 4, 2, :B1 )
----- PL/SQL Call Stack -----
object line object
handle number name
5f928de58 63 package body LOTTOUSER.BUSINESS
5dd5eaf20 1 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
TX-00ae0007-00043d2d 57 1576 X 335 1544 S
TX-00770009-00214663 335 1544 X 57 1576 S
session 1576: DID 0001-0039-00000020 session 1544: DID 0001-014F-00000018
session 1544: DID 0001-014F-00000018 session 1576: DID 0001-0039-00000020
Rows waited on:
Session 1544: obj - rowid = 0001C6F0 - AAAcbwAAlAAB6RHAAA
(dictionary objn - 116464, file - 37, block - 500807, slot - 0)
Session 1576: obj - rowid = 00005698 - AAAFaYAFAAAHKrGAAA
(dictionary objn - 22168, file - 320, block - 1878726, slot - 0)
Information on the OTHER waiting sessions:
Session 1544:
pid=335 serial=45566 audsid=200798710 user: 10/<none>
O/S info: user: root, term: pts/1, ospid: 20958, machine: qaapp03.qa.alea.ca
program: casino@qaapp03.qa.alea.ca (TNS V1-V3)
application name: casino@qaapp03.qa.alea.ca (TNS V1-V3), hash value=0
Current SQL Statement:
INSERT INTO WG2KC_LOG (ID, CUSTOMER_ID, TRANS_DATE, TRANS_ID, AMOUNT, BALANCE, TRANS_TYPE, CHIP_TYPE, ROUND_ID) VALUES (WG2K_CHIP_ID_SEQ
.NEXTVAL, :B5 , SYSDATE, :B4 , 0 - :B2 , :B3 - :B2 , 6, 2, :B1 )
End of information on OTHER waiting sessions.
===================================================


TESTUSER@ora10> select object_name, object_type
2 from user_objects
3 where data_object_id=
4 (select dbms_rowid.rowid_object('AAAcbwAAlAAB6RHAAA') from dual);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
MLOG$_WG2KC_LOG
TABLE


TESTUSER@ora10> select object_name, object_type
2 from user_objects
3 where data_object_id=
4 (select dbms_rowid.rowid_object('AAAFaYAFAAAHKrGAAA') from dual);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
WG2KC_LOG
TABLE


TESTUSER@ora10> desc WG2KC_LOG
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
ID NOT NULL NUMBER(10)
CUSTOMER_ID NOT NULL NUMBER(10)
TRANS_DATE NOT NULL DATE
TRANS_ID NOT NULL NUMBER(10)
ROUND_ID NUMBER(10)
AMOUNT NUMBER(10)
BALANCE NUMBER(10)
TRANS_TYPE NUMBER(10)
CHIP_TYPE NOT NULL NUMBER(4)
REASON NUMBER(10)

TESTUSER@ora10> select customer_id from WG2KC_LOG where rowid=chartorowid('AAAFaYAFAAAHKrGAAA');

CUSTOMER_ID
-----------
962471235

TESTUSER@ora10> desc MLOG$_WG2KC_LOG
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
AMOUNT NUMBER(10)
CUSTOMER_ID NUMBER(10)
CHIP_TYPE NUMBER(4)
TRANS_TYPE NUMBER(10)
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

TESTUSER@ora10> select customer_id from MLOG$_WG2KC_LOG where rowid =chartorowid('AAAFaYAFAAAHKrGAAA');
select customer_id from MLOG$_WG2KC_LOG where rowid =chartorowid('AAAFaYAFAAAHKrGAAA')
*
ERROR at line 1:
ORA-01410: invalid ROWID


TESTUSER@ora10> select * from MLOG$_WG2KC_LOG where rowid =chartorowid('AAAcbwAAlAAB6RHAAA') ;
select * from MLOG$_WG2KC_LOG where rowid =chartorowid('AAAcbwAAlAAB6RHAAA')
*
ERROR at line 1:
ORA-01410: invalid ROWID


TESTUSER@ora10>


My question is: how come two insert statements (into the same table) were involved in the deadlock situation? That table has a MV build on top of it.

Thanks in advance!

Hi Tom,

I got this strange deadlock information from trace file, where blocker = waiter. How this could happen?

Thanks,

Dusan

*** 2006-04-13 22:13:16.995
DEADLOCK DETECTED
Current SQL statement for this session:
SELECT :"SYS_B_0" FROM CIRCUIT WHERE UPPER(NAME) LIKE UPPER (:"SYS_B_1") AND rownum < :"SYS_B_2" FOR UPDATE
----- PL/SQL Call Stack -----
object line object
handle number name
c0000000b5cf7480 2124 package body CRAMER.ST_NAMEGENERATOR
c0000000b5cf7480 2923 package body CRAMER.ST_NAMEGENERATOR
c0000000d125d058 1714 package body CRAMER.STPKG_SECIRCUITS
c0000000d125d058 2104 package body CRAMER.STPKG_SECIRCUITS
c0000000b34527e0 1395 package body CRAMER.STPKG_SYNCOPS
c0000000db760ad8 1 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
TX-0014001a-000600b9 15 100 X 15 100 X
session 100: DID 0001-000F-000B6907 session 100: DID 0001-000F-000B6907
Rows waited on:
Session 100: obj - rowid = 00005FBE - AAAMNxAAQAAAsJWAAZ
(dictionary objn - 24510, file - 16, block - 180822, slot - 25)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

Some extra information about objects "mentioned in the trace and not existing in dba_objects":

[excerpt from one of our trace files]
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE SULS SET L_TYP_EMPL='' WHERE SUMS_CODE = :b1  AND L_TYP_EMPL IS NOT NULL
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c0000000986a13e0        20  PRESTA_RHD30.T_SUMS_AUR
...
Rows waited on:
Session 56: obj - rowid = 00000CC7 - AAAGI5AATAAAJ/WAA3
Session 64: obj - rowid = 00000CB9 - AAAGIxAATAAADZ6AAP

Here we go:
SQL> select dbms_rowid.rowid_object('AAAGI5AATAAAJ/WAA3') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAGI5AATAAAJ/WAA3')
_____________________________________________
                                        25145

  1* select object_name, object_type from dba_objects where object_id=25145

=> Strange ?

SQL> select object_name, object_type from dba_objects where data_object_id=25145 ;

OBJECT_NAME                             |OBJECT_TYPE
________________________________________|__________________
SUMS                                    |TABLE

1 ligne sélectionnée.

=> Got it. This table is the first involved in the deadlock

SQL> select dbms_rowid.rowid_object('AAAGIxAATAAADZ6AAP') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAGIXAATAAADZ6AAP')
_____________________________________________
                                        25137

1 ligne sélectionnée.

SQL> select object_name, object_type from dba_objects where data_object_id=25137 ;

OBJECT_NAME                             |OBJECT_TYPE
________________________________________|__________________
SULS                                    |TABLE

=> Here is the 2nd one. 

Hi Tom, I have seen deadlock message in one of our databases. However, I could not find the sql that caused this. The following is found in the lmd0 trace. Could you please advise how to troubleshoot this?

==========================
*** 2007-02-12 03:15:07.836
stale cvak fr 1:0xacbcd7d8([0xa][0x2],[CI])[h=KJUSERNL,n=KJUSEREX,b=KJUSERPR,ls=KJUSERSTAT_NOVALUE]:0xa000d < 0x0
*** 2007-02-12 03:24:38.709
Global Wait-For-Graph(WFG) at ddTS[0.ea] :
BLOCKED 0xc02ffe34 3 [0x550003][0x3ef5a8],[TX] [2752516,2119] 3
BLOCKER 0xae1d2220 3 [0x550003][0x3ef5a8],[TX] [2293764,7068] 3
BLOCKED 0xae1d7f50 3 [0x580013][0x492e89],[TX] [2293764,7068] 3
BLOCKER 0xcfa97c18 3 [0x580013][0x492e89],[TX] [2752516,2119] 3
*** 2007-02-12 04:45:07.831
stale cvak fr 1:0xacbcd7d8([0xa][0x2],[CI])[h=KJUSERNL,n=KJUSEREX,b=KJUSERPR,ls=KJUSERSTAT_NOVALUE]:0xcd00cf < 0x0

===============

thanks for your help!
Kiran

Tom

Can you show a small deadlock trace file from begin to end, how do the contents look in total.

Do we do a tkprof on a deadlock trace file also?

Hi Tom

What do You make of this deadlock?
How can a session deadlock by it self?

Br
Kim


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-00080000-0000c631 91 624 X 91 624 X
session 624: DID 0001-005B-00000076 session 624: DID 0001-005B-00000076
Rows waited on:
Session 624: obj - rowid = 000137D0 - AAATluAAhAAB8FHAAe
(dictionary objn - 79824, file - 33, block - 508231, slot - 30)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
Current SQL statement for this session:
DELETE FROM T_FAST_FRAGMENTDATA WHERE FRAGMENTID = :B1
----- PL/SQL Call Stack -----
object line object
handle number name
0000000249D20DE0 24 MAYFLOWER.TRIG_FRAGMENT_DELETE
000000024A1FB890 466 package body MAYFLOWER.TAXONOMY
000000023FC70E30 1 anonymous block
===================================================

---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00030002-000216dc 20 26 X 16 35 S
TX-00010002-00042f40 16 35 X 13 13 X
TX-00030002-000216dc 13 13 X 20 26 X
session 26: DID 0001-0014-00000004 session 35: DID 0001-0010-00000015
session 35: DID 0001-0010-00000015 session 13: DID 0001-000D-00000004
session 13: DID 0001-000D-00000004 session 26: DID 0001-0014-00000004
Rows waited on:
Session 35: obj - rowid = 0000ADA2 - AAAK2iABMAAAAAAAAA
(dictionary objn - 44450, file - 76, block - 0, slot - 0)
Session 13: obj - rowid = 0000B090 - AAALCQABMAAAGNfAAA
(dictionary objn - 45200, file - 76, block - 25439, slot - 0)
Session 26: obj - rowid = 0000AFAC - AAAK+sABNAAAEcMAAT
(dictionary objn - 44972, file - 77, block - 18188, slot - 19)

The question is session 26 does not hold any locks, but it blocked session 35.

*** 2007-08-10 15:27:04.440
*** SESSION ID:(26.789) 2007-08-10 15:27:04.439
DEADLOCK DETECTED ( ORA-00060 )
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-00030002-000216dc 20 26 X 16 35 S
TX-00010002-00042f40 16 35 X 13 13 X
TX-00030002-000216dc 13 13 X 20 26 X
session 26: DID 0001-0014-00000004 session 35: DID 0001-0010-00000015
session 35: DID 0001-0010-00000015 session 13: DID 0001-000D-00000004
session 13: DID 0001-000D-00000004 session 26: DID 0001-0014-00000004
Rows waited on:
Session 35: obj - rowid = 0000ADA2 - AAAK2iABMAAAAAAAAA
(dictionary objn - 44450, file - 76, block - 0, slot - 0)
Session 13: obj - rowid = 0000B090 - AAALCQABMAAAGNfAAA
(dictionary objn - 45200, file - 76, block - 25439, slot - 0)
Session 26: obj - rowid = 0000AFAC - AAAK+sABNAAAEcMAAT
(dictionary objn - 44972, file - 77, block - 18188, slot - 19)
Information on the OTHER waiting sessions:
Session 35:
pid=16 serial=34 audsid=403683 user: 84/SPOKANE
O/S info: user: , term: , ospid: 1234, machine: test02-server
program:
Current SQL Statement:

The question is session 26 does not hold any locks, but it blocked
session 35.

The session 35 is to update unique index columns

We are using RAC and the trace file is not showing any SQLs. Below is one sample trace file which is telling "Single resource deadlock: blocking enqueue which blocks itself". Can you please explain what kind of scenarios will get such a deadlock.

From the application log, the statement one got this error was and update on one table. There is a trigger on this table for update. Trigger is updating data in other tables.

-----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /vendor/oracle/product/9.2.0
System name: Linux
Node name: db-01
Release: 2.4.21-32.ELsmp
Version: #1 SMP Fri Apr 15 21:17:59 EDT 2005
Machine: i686
Instance name: db1
Redo thread mounted by this instance: 1
Oracle process number: 58
Unix process pid: 734, image: oracle@db-01 (TNS V1-V3)

*** 2007-10-31 14:33:29.107
*** SESSION ID:(46.48548) 2007-10-31 14:33:29.107
Single resource deadlock: blocking enqueue which blocks itself, f 0
----------enqueue------------------------
lock version : 4379
Owner node : 0
grant_level : KJUSERCW
req_level : KJUSERPW
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x8adc83d0
procp : 0x8acc58f8
pid : 1301
proc version : 6819
oprocp : (nil)
opid : 0
gid : 0
xid : 3866625 3234
dd_time : 61.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : CONVERTING
Open Options : KJUSERDEADLOCK

Tom, I read your responses about deadlock trace file having Transactions waiting for shared resources and 'no rows'. I have a similar issue but am not able to nail it down.

Following is the trace file
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00040000-00000796 21 133 X 31 128 S
TX-000a0013-00000ee1 31 128 X 21 133 S
session 133: DID 0001-0015-0000B86A session 128: DID 0001-001F-00011779
session 128: DID 0001-001F-00011779 session 133: DID 0001-0015-0000B86A
Rows waited on:
Session 128: no row
Session 133: no row
Information on the OTHER waiting sessions:
Session 128:
pid=31 serial=154 audsid=190289 user: 74/ICE
O/S info: user: NORTH\ssaluja, term: DPF003085, ospid: 4292:4924, machine: NORTH\DPF003085
program: nunit-gui.exe
application name: nunit-gui.exe, hash value=0
Current SQL Statement:
DELETE from P1 where X in ('s1')
End of information on OTHER waiting sessions.
Current SQL statement for this session:
Insert into C values ('s1',n1)
===================================================

Following are my tables and scripts to insert rows. Any help will be appreciated.

create table P1(x varchar2(20) primary key);
create table P2(Y varchar2(20) primary key);
CREATE TABLE C
(
X Varchar2(20),
Y Varchar2(20),
PRIMARY KEY (X, Y)
);

alter table C
add constraint C_FK1 foreign key (X)
references P1 (X) on delete cascade;
alter table C
add constraint C_FK2 foreign key (Y)
references P2 (Y);

create index C_Y_IDX on C(Y)

-- Setup some records--
Insert into P1 values('s1');
Insert into P1 values('s2');
commit;

Two concurrent requests are doing the following

Tx1
Insert into P2 values('n1')
Insert into C values('s1','n1')
Insert into C values('s2','n1')

Tx2
Delete from P1 where x = 's1' -- This will perform a cascade delete
Delete from P2 where y not in (select y from C)

Could you help with the interpreting of the following trace file?

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /opt/oracle/11.1.0/
System name: Linux
Node name: bruair22
Release: 2.6.18-53.1.13.el5
Version: #1 SMP Mon Feb 11 13:27:27 EST 2008
Machine: x86_64
Instance name: caldw02
Redo thread mounted by this instance: 1
Oracle process number: 89
Unix process pid: 18720, image: oracle@bruair22


*** 2008-03-29 04:01:57.236
*** SESSION ID:(127.9590) 2008-03-29 04:01:57.236
*** CLIENT ID:() 2008-03-29 04:01:57.236
*** SERVICE NAME:(SYS$USERS) 2008-03-29 04:01:57.236
*** MODULE NAME:() 2008-03-29 04:01:57.236
*** ACTION NAME:() 2008-03-29 04:01:57.236

A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object ETL.VIOLATIONS
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
0x6e8aef80 0x7884eb50 0x70b05170 ? 0x788bed80 0x70b8ce88 S
0x6e8aef80 0x788796d0 0x70943e40 X 0x7884eb50 0x70b05170 S
0x6e8aef80 0x788bed80 0x70b8ce88 S 0x788796d0 0x70943e40 0
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x70b05170, type: 70, owner: 0x757d9bf0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x792e75c0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:7981, pg=0
LIBRARY OBJECT LOCK: 0x70b05170 handle=0x6e8aef80 req=S
pnc=(nil) pns=(nil) cbb=1467 rpr=1 exc=0 ilh=(nil) ctx=(nil)
use=0x7884eb50 ses=0x788d4340 cnt=0 flg=[0000] spn=0xc9b
LIBRARY HANDLE:0x6e8aef80 bid=32332 hid=b35a7e4c lmd=S pmd=0 sta=VALD
name=ETL.VIOLATIONS
hash=90abea203e5392c00a419921b35a7e4c idn=115854
tim=02-22-2008 12:03:53kkkk-dddd-llll=0000-0741-0741
exc=0 ivc=0 ldc=413 cbb=1467 rpr=1 kdp=0 slc=1 dbg=0
dmtx=0x6e8af018(0, 4994, 0) mtx=0x6e8af050(0, 66904, 335)
nsp=TABL(01) typ=TABL(02) flg=KGHP/TIM/SML/[02000800]
lwt=0x6e8aeff8[0x69923060,0x699828d8]
pwt=0x6e8aefd8[0x6e8aefd8,0x6e8aefd8]
ref=0x6e8af008[0x6e640f00,0x73c464b8]
LIBRARY OBJECT: 0x70db1128
flg=EXS/LOC[0005] pfl=[0000] ssta=VALD load=0
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x70b8ce88, type: 70, owner: 0x77775520, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x792eb580, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:7981, pg=0
LIBRARY OBJECT LOCK: 0x70b8ce88 handle=0x6e8aef80 mod=S
pnc=(nil) pns=(nil) cbb=1466 rpr=1 exc=0 ilh=(nil) ctx=(nil)
use=0x788bed80 ses=0x788bed80 cnt=0 flg=[0000] spn=0xbca
LIBRARY HANDLE:0x6e8aef80 bid=32332 hid=b35a7e4c lmd=S pmd=0 sta=VALD
name=ETL.VIOLATIONS
hash=90abea203e5392c00a419921b35a7e4c idn=115854
tim=02-22-2008 12:03:53kkkk-dddd-llll=0000-0741-0741
exc=0 ivc=0 ldc=413 cbb=1467 rpr=1 kdp=0 slc=1 dbg=0
dmtx=0x6e8af018(0, 4994, 0) mtx=0x6e8af050(0, 66904, 335)
nsp=TABL(01) typ=TABL(02) flg=KGHP/TIM/SML/[02000800]
lwt=0x6e8aeff8[0x69923060,0x699828d8]
pwt=0x6e8aefd8[0x6e8aefd8,0x6e8aefd8]
ref=0x6e8af008[0x6e640f00,0x73c464b8]
LIBRARY OBJECT: 0x70db1128
flg=EXS/LOC[0005] pfl=[0000] ssta=VALD load=0
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x70b8ce88, type: 70, owner: 0x77775520, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x792eb580, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:7981, pg=0
LIBRARY OBJECT LOCK: 0x70b8ce88 handle=0x6e8aef80 mod=S
pnc=(nil) pns=(nil) cbb=1466 rpr=1 exc=0 ilh=(nil) ctx=(nil)
use=0x788bed80 ses=0x788bed80 cnt=0 flg=[0000] spn=0xbca
LIBRARY HANDLE:0x6e8aef80 bid=32332 hid=b35a7e4c lmd=S pmd=0 sta=VALD
name=ETL.VIOLATIONS
hash=90abea203e5392c00a419921b35a7e4c idn=115854
tim=02-22-2008 12:03:53kkkk-dddd-llll=0000-0741-0741
exc=0 ivc=0 ldc=413 cbb=1467 rpr=1 kdp=0 slc=1 dbg=0
dmtx=0x6e8af018(0, 4994, 0) mtx=0x6e8af050(0, 66904, 335)
nsp=TABL(01) typ=TABL(02) flg=KGHP/TIM/SML/[02000800]
lwt=0x6e8aeff8[0x69923060,0x699828d8]
pwt=0x6e8aefd8[0x6e8aefd8,0x6e8aefd8]
ref=0x6e8af008[0x6e640f00,0x73c464b8]
LIBRARY OBJECT: 0x70db1128
flg=EXS/LOC[0005] pfl=[0000] ssta=VALD load=0
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x70943e40, type: 70, owner: 0x788796d0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x792ef540, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:7981, pg=0
LIBRARY OBJECT LOCK: 0x70943e40 handle=0x78e0cb28 mod=N
pnc=(nil) pns=(nil) cbb=1 rpr=1 exc=0 ilh=0x688e3ae0 ctx=0x2aaaac3bfcc8
use=0x788796d0 ses=0x788e18d8 cnt=1 flg=CBK/[0020] spn=0x0
LIBRARY HANDLE:0x78e0cb28 bid=0 hid=0 lmd=N pmd=X sta=VALD
exc=0 ivc=0 ldc=1 cbb=1 rpr=1 kdp=0 slc=1 dbg=0
dmtx=0x78e0cbc0(0, 0, 0) mtx=0x6ea00e98(0, 10, 0)
nsp=CRSR(00) typ=CRSR(00) flg=RON/KGHP/PN0/SML/EXP/[12010100]
lwt=0x78e0cba0[0x78e0cba0,0x78e0cba0]
pwt=0x78e0cb80[0x78e0cb80,0x78e0cb80]
ref=0x78e0cbb0[0x70d6e870,0x70d6e870]
LIBRARY OBJECT: 0x70d6e410
flg=EXS[0001] pfl=[0000] ssta=VALD load=0

*** 2008-03-29 04:01:57.369
----------------------------------------
KGX Atomic Operation Log 0x688e3af0
Mutex 0x70d6eec0(101, 0) idn 2f6259f5 oper EXCL
Cursor Pin uid 101 efd 0 whr 1 slp 0
opr=3 pso=0x70943e40 flg=0
pcs=0x70d6ee40 nxt=(nil) flg=35 cld=0 hd=0x78e0cb28 par=0x7253aa28
ct=0 hsh=0 unp=(nil) unn=0 hvl=7253ad08 nhv=1 ses=0x788796d0
hep=0x70d6eec0 flg=80 ld=1 ob=0x70d6e410 ptr=0x65cfcf68 fex=0x65cfc2d8
--------------------------------------------------------
This lock request was aborted.

Hi Tom,

We have experienced deadlock situation and after reading the notes from this article I am not able to figure out what has happened in this case.
I would appreciate your help.

We have two tables : IDG_OFFERS which preserves Offer_id and its latest Version with Primary Key on OFFER_ID &
IDG_OFFERS_AUDIT which preserves all the previous versions for all the offers for the audit purposes, primary Key being (Offer_ID + Version).

We are not maintaining any FK relationship between these two tables.

whenever a new revision comes, we

1. Select various fields from IDG_OFFERS which are required to preserve for audit.
2. Insert a row in the IDG_OFFERS_AUDIT to preserve its previous history for audit purposes.
3. Update the IDG_OFFERS table with new version attributes.

The object in question (dictionary objn - 14937, file - 63, block - 246617, slot - 0) is the PK on the IDG_OFFERS_AUDIT table.
===========================================================
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
UPDATE IDG_OFFERS SET PIECE_ID = :B23 , EXTERNAL_ID = :B22 , TERRITORY_CODE = :B21 , STREET_DATE = :B20 , SALE_END_DATE = :B19 , EXCLUSIVE_END_DATE = :B18 , COST = :B17 , COST_CURRENCY = :B16 , CURRENT_COST_CODE = :B15 , CURRENT_COST_OVERRIDE = :B14 , CUR_COST_OVERRIDE_CURRENCY = :B13 , CURRENT_COST_EFFECTIVE = :B12 , CURRENT_COST_CODE_NAME = :B11 , RIGHT_BITS = :B10 , ATTRIBUTE_BITS = :B9 , IS_DELETED = :B8 , VERSION = :B7 , PRICE_ALGORITHMIC = :B6 , PRICE_CD = :B5 , PRICE_OVERRIDE = :B4 , PRICE_CURRENCY = :B3 , IS_AGENCY = :B2 , IS_IN_PUBLIC_DOMAIN = :B1 WHERE IDG_OFFERS.OFFER_ID = :B24 RETURNING IDG_OFFERS.VERSION INTO :O0
----- PL/SQL Call Stack -----
object line object
handle number name
0x1fc8bc9e8 1 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
TX-001f0018-000066c7 276 2108 X 213 2022 S
TX-001a0007-0000c52b 213 2022 X 276 2108 S
session 2108: DID 0001-0114-0000531B session 2022: DID 0001-00D5-00007C7E
session 2022: DID 0001-00D5-00007C7E session 2108: DID 0001-0114-0000531B
Rows waited on:
Session 2022: obj - rowid = 00003A59 - AAAKkOAA/AAA8NZAAA
(dictionary objn - 14937, file - 63, block - 246617, slot - 0)
Session 2108: obj - rowid = 00003A59 - AAAKkOAA/AAA8EkAAA
(dictionary objn - 14937, file - 63, block - 246052, slot - 0)
Information on the OTHER waiting sessions:
Session 2022:
pid=213 serial=23988 audsid=20771559 user: 32/OLTP_USER
O/S info: user: idging, term: unknown, ospid: , machine: digital-music-ingestion-5105.iad5.amazon.com
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=0
Current SQL Statement:

UPDATE IDG_OFFERS SET PIECE_ID = :B23 , EXTERNAL_ID = :B22 , TERRITORY_CODE = :B21 , STREET_DATE = :B20 , SALE_END_DATE = :B19 , EXCLUSIVE_END_DATE = :B18 , COST = :B17 , COST_CURRENCY = :B16 , CURRENT_COST_CODE = :B15 , CURRENT_COST_OVERRIDE = :B14 , CUR_COST_OVERRIDE_CURRENCY = :B13 , CURRENT_COST_EFFECTIVE = :B12 , CURRENT_COST_CODE_NAME = :B11 , RIGHT_BITS = :B10 , ATTRIBUTE_BITS = :B9 , IS_DELETED = :B8 , VERSION = :B7 , PRICE_ALGORITHMIC = :B6 , PRICE_CD = :B5 , PRICE_OVERRIDE = :B4 , PRICE_CURRENCY = :B3 , IS_AGENCY = :B2 , IS_IN_PUBLIC_DOMAIN = :B1 WHERE IDG_OFFERS.OFFER_ID = :B24 RETURNING IDG_OFFERS.VERSION INTO :O0
End of information on OTHER waiting sessions.
===================================================

SQL> select object_name from dba_objects where object_id = 14937;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
PK_IDG_OFFERS_AUDIT


SQL> select COLUMN_NAME , COLUMN_POSITION from dba_ind_columns where index_name = 'PK_IDG_OFFERS_AUDIT' order by column_position;

COLUMN_NAME COLUMN_POSITION
------------ ---------------
OFFER_ID 1
VERSION 2



CREATE TABLE "BOOKER"."IDG_OFFERS"
( "OFFER_ID" NUMBER(10,0) NOT NULL ENABLE,
"PIECE_ID" NUMBER(10,0),
"EXTERNAL_ID" VARCHAR2(30),
"TERRITORY_CODE" CHAR(2),
"STREET_DATE" DATE,
"SALE_END_DATE" DATE,
"EXCLUSIVE_END_DATE" DATE,
"COST" NUMBER(8,0),
"PRICE" NUMBER(8,0),
"RIGHT_BITS" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
"ATTRIBUTE_BITS" NUMBER(6,0) DEFAULT 0,
"VERSION" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(8),
"CREATION_DATE" DATE,
"LAST_UPDATED_BY" VARCHAR2(8),
"LAST_UPDATED_DATE" DATE,
"CURRENT_COST_CODE" NUMBER(4,0),
"CURRENT_COST_OVERRIDE" NUMBER(8,0),
"CURRENT_COST_EFFECTIVE" DATE,
"NEXT_COST_CODE" NUMBER(4,0),
"NEXT_COST_OVERRIDE" NUMBER(8,0),
"NEXT_COST_EFFECTIVE" DATE,
"IS_DELETED" CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
"PRICE_ALGORITHMIC" NUMBER(8,0),
"PRICE_CD" NUMBER(8,0),
"PRICE_OVERRIDE" NUMBER(8,0),
"COST_CURRENCY" CHAR(3) DEFAULT 'USD' NOT NULL ENABLE,
"PRICE_CURRENCY" CHAR(3) DEFAULT 'USD' NOT NULL ENABLE,
"IS_IN_PUBLIC_DOMAIN" CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
"CUR_COST_OVERRIDE_CURRENCY" CHAR(3),
"IS_AGENCY" CHAR(1),
"CURRENT_COST_CODE_NAME" VARCHAR2(1000),
CONSTRAINT "PK_IDG_OFFERS" PRIMARY KEY ("OFFER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IDG_IDX" ENABLE,
CHECK (is_deleted IN ('Y','N')) ENABLE,
CONSTRAINT "FK_IDG_ITEMSXIDG_OFFERS" FOREIGN KEY ("OFFER_ID")
REFERENCES "BOOKER"."IDG_ITEMS" ("ITEM_ID") ENABLE,
CONSTRAINT "FK_IDG_PIECESXIDG_OFFERS" FOREIGN KEY ("PIECE_ID")
REFERENCES "BOOKER"."IDG_PIECES" ("PIECE_ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 33554432 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IDG";


CREATE TABLE "BOOKER"."IDG_OFFERS_AUDIT"
( "OFFER_ID" NUMBER(10,0) NOT NULL ENABLE,
"VERSION" NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,
"PIECE_ID" NUMBER(10,0),
"EXTERNAL_ID" VARCHAR2(30),
"TERRITORY_CODE" CHAR(2),
"STREET_DATE" DATE,
"SALE_END_DATE" DATE,
"EXCLUSIVE_END_DATE" DATE,
"COST" NUMBER(8,0),
"PRICE" NUMBER(8,0),
"RIGHT_BITS" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
"ATTRIBUTE_BITS" NUMBER(6,0) DEFAULT 0,
"LAST_UPDATED_BY" VARCHAR2(8),
"LAST_UPDATED_DATE" DATE,
"CURRENT_COST_CODE" NUMBER(4,0),
"CURRENT_COST_OVERRIDE" NUMBER(8,0),
"CURRENT_COST_EFFECTIVE" DATE,
"NEXT_COST_CODE" NUMBER(4,0),
"NEXT_COST_OVERRIDE" NUMBER(8,0),
"NEXT_COST_EFFECTIVE" DATE,
"IS_DELETED" CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
CONSTRAINT "PK_IDG_OFFERS_AUDIT" PRIMARY KEY ("OFFER_ID", "VERSION")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 524288 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IDG_IDX" ENABLE,
CHECK (is_deleted IN ('Y','N')) ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 524288 NEXT 33554432 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "IDG"

/

Other Index Related Information
===============================

SQL> select index_name from dba_indexes where table_name = 'IDG_OFFERS';

INDEX_NAME
------------------------------
X_OFFER_FK_PIECE_ID
PK_IDG_OFFERS

SQL> select index_name from dba_indexes where table_name = 'IDG_OFFERS_AUDIT';

INDEX_NAME
------------------------------
PK_IDG_OFFERS_AUDIT

SQL> select column_name , column_position from dba_ind_columns where index_name = 'X_OFFER_FK_PIECE_ID';

COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COLUMN_POSITION
---------------
PIECE_ID
1

Hi Tom,

Can you please offer some advice o the above.

Thanks

Hi Tom,
Please suggest me what i have to do. here my deadlock mesg is as follows


They are still arriving. I looked at onme of the trace files listed in the alert log and this is what it says...

wasv010 ora_817> more mars_ora_1843.trc
Dump file /ford/app/oracle/product/817/rdbms/log/mars_ora_1843.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /ford/app/oracle/product/817
System name: SunOS
Node name: wasv010
Release: 5.6
Version: Generic_105181-23
Machine: sun4u
Instance name: mars
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 1843, image: oracle@wasv010 (TNS V1-V3)

*** 2009-01-06 06:31:11.550
*** SESSION ID:(11.918) 2009-01-06 06:31:11.547
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE EMPLOYEE_DAYS SET SCHEDULED_TM=:b1 * 60 ,ABSENCE_MBR_CD=:b2,LAST_UPDATED_BY='OSP_TRIGGER',LAST_UPDATED_DT=SYSDATE WHERE GLOBA
L_ID = :b3 AND ATTENDANCE_DT = :b4
----- PL/SQL Call Stack -----
object line object
handle number name
910142e4 36 LEAP.LEAP_OSP_DAYS_TR
910cc6d8 236 procedure LEAP.LEAP_OSP_IN_LOAD
910cccc8 1 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
TX-00050049-0070f35d 20 11 X 19 22 S
TX-00050026-0070adc5 19 22 X 20 11 S
session 11: DID 0001-0014-00000002 session 22: DID 0001-0013-00000002
session 22: DID 0001-0013-00000002 session 11: DID 0001-0014-00000002
Rows waited on:
Session 22: no row
Session 11: no row
===================================================
PROCESS STATE
-------------
Process global information:
process: 8ebd16f4, call: 8ece2fb0, xact: 8f2ef0b0, curses: 8ec1ac38, usrses: 8ec1ac38
----------------------------------------
SO: 8ebd16f4, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
(process) Oracle pid=20, calls cur/top: 8ece2fb0/8ece3050, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 35
last post received-location: kcbzww
last process to post me: 8ebd13d8 6 0
last post sent: 2396539608 13 2
last post sent-location: kslges
last process posted by me: 8ebcdefc 1 6
(latch info) wait_event=0 bits=10
holding 8000416c Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Context saved from call: 0
state=busy
recovery area:
Dump of memory from 0x8EBCD30C to 0x8EBCD314
8EBCD300 00000000 [....]
8EBCD310 00000000 [....]
Process Group: DEFAULT, pseudo proc: 8ec11ac8
O/S info: user: leap, term: UNKNOWN, ospid: 1843
OSD pid info: 1843
----------------------------------------
SO: 8ec1ac38, type: 3, owner: 8ebd16f4, pt: 0, flag: INIT/-/-/0x00
(session) trans: 8f2ef0b0, creator: 8ebd16f4, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0014-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 21/LEAP
O/S info: user: leap, term: , ospid: 1842, machine: wasv010
program: sqlplus@wasv010 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
last wait for 'enqueue' blocking sess=0x8ec208dc seq=21452 wait_time=308
name|mode=54580004, id1=50026, id2=70adc5
----------------------------------------
SO: 8f2ef0b0, type: 25, owner: 8ec1ac38, pt: 0, flag: INIT/-/-/0x00
(trans) bsn = 1827047, flg = 0x80001e03, flg2 = 0x00, prx = 0x0, ros = 5, spn = 1883852
efd = 14
parent xid: 0x0000.000.00000000
env: (scn: 0x042f.8e4e619f xid: 0x0005.049.0070f35d uba: 0x02c0cbcc.0637.10)
cev: (spc = 5958 usi = 5 ubk tsn: 2 rdba: 0x02c0cbcc useg tsn: 2 rdba: 0x00c00412
hwm uba: 0x02c0cbcc.0637.10 col uba: 0x00000000.0000.00
num bl: 6 bk list: 0x8f2c2d10)
(enqueue) TX-00050049-0070F35D DID: 0001-0014-00000002
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res: 8ed73920, mode: X, prv: 8ed73928, sess: 8ec1ac38, proc: 8ebd16f4
xga: 0x0, heap: UGA
----------------------------------------
SO: 8f2c2d10, type: 24, owner: 8f2ef0b0, pt: 0, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint
--------------------------------------
0 1860675 1 0x8ef2d04c
1 1862232 2 0x8f1298ec
2 1863256 1 0x8ee6d9a4
3 1866154 1 0x8f11ce84
4 1867274 2 0x8ee3387c
5 1870633 2 0x8f11cc64
6 1875283 1 0x8ef385fc
7 1878157 1 0x8f11c824
8 1878376 1 0x8ee32e64
9 1879179 2 0x8ef2c0e4
10 1879544 2 0x8f11c57c
11 1879909 1 0x8ee32d54
12 1879982 1 0x8ef2bd2c
13 1880834 1 0x8ee32b34
14 1881077 2 0x8ef2bc1c
15 1881223 2 0x8f02142c
16 1882392 2 0x8f11c1c4
17 1882538 1 0x8ee3299c
18 1882757 2 0x8ef2b974
19 1883341 2 0x8f11bfa4
----------------------------------------
SO: 8f2c1c58, type: 24, owner: 8f2ef0b0, pt: 0, flag: -/-/-/0x00
(List of Blocks) next index = 20
index savepoint itli buffer hint
--------------------------------------
0 1850477 1 0x8f15dc94
1 1850477 1 0x8ee5ec2c
2 1851427 1 0x8ee3ced4
3 1852084 2 0x8f0230dc
4 1853033 1 0x8ee3ce4c
5 1853179 2 0x8f11dc54
6 1853325 1 0x8ee3bcc4
7 1853690 1 0x8ef35e24
8 1854566 1 0x8ef35d9c
9 1854712 1 0x8f022d24


But my code is as follows
UPDATE LEAP.OSP_DAYS
SET tk_ind = temp_osp_rec.tk_ind,
absence_mbr_cd = temp_osp_rec.absence_mbr_cd,
plant_cd = temp_osp_rec.plant_cd,
hours_scheduled = temp_osp_rec.hours_scheduled,
certification_cd = temp_osp_rec.certification_cd,
paid_absence_ind = temp_osp_rec.paid_absence_ind,
prepaid_holiday_type = temp_osp_rec.prepaid_holiday_type,
to_payroll_osp_ind = temp_osp_rec.to_payroll_osp_ind,
to_payroll_osp_dt = temp_osp_rec.to_payroll_osp_dt,
to_payroll_ssp_ind = temp_osp_rec.to_payroll_ssp_ind,
to_payroll_ssp_dt = temp_osp_rec.to_payroll_ssp_dt,
disqual_ind = temp_osp_rec.disqual_ind,
report_level_ind = temp_osp_rec.report_level_ind,
to_payroll_defhol_ind = temp_osp_rec.to_payroll_defhol_ind,
last_updated_dt = v_osp_in_start_dt,
last_updated_by = 'OSP LOAD'

We are seeing following error in trace file
Fri Jun 26 17:07:10 2009
Global Enqueue Services Deadlock detected. More info in filen /local/CPCP/oracle/admin/CPCP2/bdump/cpcp21_lmd0_851974.trc.
Fri Jun 26 17:08:18 2009
Global Enqueue Services Deadlock detected. More info in filen /local/abc/oracle/admin/abc/bdump/cpcp21_lmd0_851974.trc.
Fri Jun 26 17:09:27 2009
Global Enqueue Services Deadlock detected. More info in filen /local/CPCP/oracle/admin/CPCP2/bdump/cpcp21_lmd0_851974.trc.



Trace file contents

/local/CPCP/oracle/admin/CPCP2/bdump/cpcp21_lmd0_851974.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /local/CPCP/oracle/product/9.2.0
System name: AIX
Node name: tsjipudbop101
Release: 3
Version: 5
Machine: 000C7AE6D600
Instance name: CPCP21
Redo thread mounted by this instance: 0 <none>
Oracle process number: 5
Unix process pid: 851974, image: oracle@tsjipudbop101 (LMD0)

*** SESSION ID:(4.1) 2009-04-25 17:38:52.684
open lock on RM 0 0
*** 2009-04-25 17:40:58.990
open lock on RM 0 0
*** 2009-05-29 22:35:54.034
stale cvak fr 1:7000000737f2540([0x0][0x0],[CF])[h=KJUSERNL,n=KJUSERPR,b=KJUSEREX,ls=KJUSERSTAT_NOVALUE]:0x210023 < 0x0
*** 2009-06-14 20:57:28.588
stale cvak fr 1:700000074a3a220([0xfec84f80][0x8e4beb90],[LE])[h=KJUSERNL,n=KJUSERPR,b=KJUSEREX,ls=KJUSERSTAT_NOVALUE]:0x2 < 0x10001
*** 2009-06-20 22:35:40.293
stale cvak fr 1:7000000758039f0([0x0][0x0],[CF])[h=KJUSERNL,n=KJUSEREX,b=KJUSERNL,ls=KJUSERSTAT_NOVALUE]:0x4d004f < 0x0
stale cvak fr 1:7000000758039f0([0x0][0x0],[CF])[h=KJUSERNL,n=KJUSEREX,b=KJUSEREX,ls=KJUSERSTAT_NOVALUE]:0x4d0050 < 0x0
*** 2009-06-26 17:07:10.399
Global Wait-For-Graph(WFG) at ddTS[0.0] :
BLOCKED 700000076a9b5d0 5 [0xc0023][0xe12d],[TX] 131259 1
BLOCKER 700000074b90d08 5 [0xc0023][0xe12d],[TX] 131195 1
BLOCKED 700000074b90e70 5 [0xb0002][0x3cd4a],[TX] 131195 1
BLOCKER 700000076a9b468 5 [0xb0002][0x3cd4a],[TX] 131259 1
*** 2009-06-26 17:08:18.592
Global Wait-For-Graph(WFG) at ddTS[0.0] :
BLOCKED 700000076a9b5d0 5 [0xc0023][0xe12d],[TX] 131259 1
BLOCKER 700000074b90d08 5 [0xc0023][0xe12d],[TX] 131195 1
BLOCKED 700000074b90e70 5 [0xb0014][0x3cd28],[TX] 131195 1
BLOCKER 700000076a9b468 5 [0xb0014][0x3cd28],[TX] 131259 1
*** 2009-06-26 17:09:27.910
Global Wait-For-Graph(WFG) at ddTS[0.0] :
BLOCKED 700000076a9b5d0 5 [0xb0004][0x3cd2c],[TX] 131259 1
BLOCKER 700000074b90d08 5 [0xb0004][0x3cd2c],[TX] 131195 1
BLOCKED 700000074b90e70 5 [0xc0007][0xe0fd],[TX] 131195 1
BLOCKER 700000076a9b468 5 [0xc0007][0xe0fd],[TX] 131259 1
*** 2009-06-26 20:28:24.606
stale cvak fr 1:700000074b2f4d0([0x1e141b21][0xb5d777eb],[LB])[h=KJUSERNL,n=KJUSERPR,b=KJUSEREX,ls=KJUSERSTAT_NOVALUE]:0x2 < 0x10001


No Indormation in trace file? May I know why?

regards
Anil

Hi Tom,

I found one statement in takprof file that is
"buffer deadlock". What this means?


INSERT into tab1 (item,
dept,
..,
timestamp)
values(:b1,
:b2,
:..,
:b22)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 915118 1508.43 1607.14 9878 1013822 14810629 915118
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 915119 1508.43 1607.14 9878 1013822 14810629 915118

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 9878 1.11 118.61
log file sync 51 0.11 0.97
buffer busy waits 295 1.00 2.97
enqueue 71 0.55 1.79
latch free 105 0.06 0.13
log file switch completion 8 0.13 0.54
buffer deadlock 3 0.00 0.00
undo segment extension 100255 0.00 0.06
********************************************************************************

Hi Tom,

Many thanks for reply. I have following few questions.
What to do to mitigate the buffer lock issue?
What to do to mitigate the unique conflicts as mentioned by you in above examples?

Sorry for delay in reply.
Thnaks for your advice. I mean buffer deadlock. Recently we got record locked by another user error in amodule The module has for update nowait clause is not operating on any duplicate row .
please find the below sample deadlock statement in trace file.

bind 3: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0

bfp=1106ac078 bln=32 avl=18 flg=09

value="AAAX1TAAzAAAUdEAAP"

...skipping...

bfp=110d1ec38 bln=07 avl=07 flg=01

value="9/16/2010 6:11:31"

WAIT #63: nam='buffer deadlock' ela= 2 p1=402761125 p2=12 p3=0

EXEC #63:c=0,e=864,p=0,cr=4,cu=18,mis=0,r=1,dep=1,og=4,tim=1254505753182146

BINDS #54:

bind 0: dty=1 mxl=32(07) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0

bfp=110479118 bln=32 avl=07 flg=05

value="1205804"


So i want to know why this error is coming and how ti mitigate this issue?
This module throws error often on a tbale which has multiple partitions.

In one module i got similar type of error where the module was not doing any dml operation on duplicate rows i got ORA-00060: Deadlock detected error in error log.

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      9878        1.11        118.61
  log file sync                                  51        0.11          0.97
  buffer busy waits                             295        1.00          2.97
  enqueue                                        71        0.55          1.79
  latch free                                    105        0.06          0.13
  log file switch completion                      8        0.13          0.54
  buffer deadlock                                 3        0.00          0.00
  undo segment extension                     100255        0.00          0.06

Hi Tom,

working on 10205

we face daily 3-4 dead locks in alert log, here is the trace file for one of the update and insert statements

*** 2011-09-17 10:51:40.612
*** ACTION NAME:() 2011-09-17 10:51:40.409
*** MODULE NAME:(JDBC Thin Client) 2011-09-17 10:51:40.409
*** SERVICE NAME:(SYS$USERS) 2011-09-17 10:51:40.409
*** SESSION ID:(353.11811) 2011-09-17 10:51:40.409
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-00410026-000ac3ff 461 353 X 463 618 X
TX-000d002d-001543e7 463 618 X 461 353 X
session 353: DID 0001-01CD-000004D1 session 618: DID 0001-01CF-00000A0E
session 618: DID 0001-01CF-00000A0E session 353: DID 0001-01CD-000004D1
Rows waited on:
Session 618: obj - rowid = 0001125B - AAAW0QACfAABrw5AA4
(dictionary objn - 70235, file - 159, block - 441401, slot - 56)
Session 353: obj - rowid = 0001125B - AAAW0QAAOAAAXTbAAC
(dictionary objn - 70235, file - 14, block - 95451, slot - 2)
Information on the OTHER waiting sessions:
Session 618:
sid: 618 ser: 16147 audsid: 44777494 user: 116/FINNCAS_BFPROD
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8)
pid: 463 O/S info: user: oracle, term: UNKNOWN, ospid: 10109036
image: oracle@bcfprddb
O/S info: user: root, term: unknown, ospid: 1234, machine: bcfpcas
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
UPDATE LOS_APP_STATUS SET LAS_ID_N =:1 , LAS_USER_ID_C =:2 , LAS_STATUS_C = :3 , LAS_EDIT_D = SYSDATE WHERE APP_ID_C = :4 AND LAS_ACTIVITY_ID= :5
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE LOS_APP_STATUS SET LAS_ID_N =:1 , LAS_USER_ID_C =:2 , LAS_STATUS_C = :3 , LAS_EDIT_D = SYSDATE WHERE APP_ID_C = :4 AND LAS_ACTIVITY_ID= :5
====================================================================================================================================================================================

Below is the deadlock caused by insert statement

*** 2011-09-17 09:21:18.620
*** ACTION NAME:() 2011-09-17 09:21:18.619
*** MODULE NAME:() 2011-09-17 09:21:18.619
*** SERVICE NAME:(SYS$USERS) 2011-09-17 09:21:18.619
*** CLIENT ID:() 2011-09-17 09:21:18.619
*** SESSION ID:(353.11176) 2011-09-17 09:21:18.619
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-00230023-00149f23 88 353 X 82 380 S
TX-00610028-00030c40 82 380 X 88 353 S
session 353: DID 0001-0058-000012EC session 380: DID 0001-0052-0000172C
session 380: DID 0001-0052-0000172C session 353: DID 0001-0058-000012EC
Rows waited on:
Session 380: obj - rowid = 0001038D - AADed9ADWAAArQAAAA
(dictionary objn - 66445, file - 214, block - 177152, slot - 0)
Session 353: obj - rowid = 00079ED8 - AADiH8AFFAABkOXAAA
(dictionary objn - 499416, file - 325, block - 410519, slot - 0)
Information on the OTHER waiting sessions:
Session 380:
sid: 380 ser: 11071 audsid: 44771305 user: 112/FINNPDC_BFPROD
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x8)
pid: 82 O/S info: user: oracle, term: UNKNOWN, ospid: 11288618
image: oracle@bcfprddb
O/S info: user: oracle, term: , ospid: 2855002, machine: bcfplms
program: frmweb@bcfplms (TNS V1-V3)
application name: frmweb@bcfplms (TNS V1-V3), hash value=403290991
Current SQL Statement:
INSERT INTO PDC_PDC_TMP_TXN_H(PDCID,AGREEMENTNO,BRANCHID,LEGALVEHICLEID,REMARKS,LOANACCTNUM,LANTYPE) VALUES (:1,:2,:3,:4,:5,:6,:7)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
INSERT INTO PDC_DUMMY_CHQSNO (AGREEMENTID, INSTLNUM, INSTRUMENT, CHQSEQNOUSED ) VALUES (:B4 , :B3 , :B2 , :B1 )
----- PL/SQL Call Stack -----
object line object
handle number name
700000341ed3de8 112 procedure FINNLEA_BFPROD.SP_AUTO_PDC_GEN
700000341ed3de8 586 procedure FINNLEA_BFPROD.SP_AUTO_PDC_GEN
70000038d5dfd20 1 anonymous block
============================================================

please suggest what could be the solution to this

According to the two deadlock graphs the poster has presented, his deadlock is not due to unindexed Foreign key as far as when this is the case the deadlock graph will show at least one TM-enqueue which is not the case here. So we rule out for him this possibility

The first graph presents a Transaction enqueue (TX) held and waited on eXclusive mode (X) which is a row level lock
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00410026-000ac3ff 461 353 X 463 618 X
TX-000d002d-001543e7 463 618 X 461 353 X

UPDATE LOS_APP_STATUS SET LAS_ID_N =:1 , LAS_USER_ID_C =:2 , LAS_STATUS_C = :3 , LAS_EDIT_D =
SYSDATE WHERE APP_ID_C = :4 AND LAS_ACTIVITY_ID= :5

UPDATE LOS_APP_STATUS SET LAS_ID_N =:1 , LAS_USER_ID_C =:2 , LAS_STATUS_C = :3 , LAS_EDIT_D =
SYSDATE WHERE APP_ID_C = :4 AND LAS_ACTIVITY_ID= :5

It could be that those two sessions are overlapping in their update of two sets of same row identifiers

The second graph presents a Transaction enqueue (TX) held on mode X(exclusive) and waited on mode S (Shared)

---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00230023-00149f23 88 353 X 82 380 S
TX-00610028-00030c40 82 380 X 88 353 S



INSERT INTO
PDC_PDC_TMP_TXN_H(PDCID,AGREEMENTNO,BRANCHID,LEGALVEHICLEID,REMARKS,LOANACCTNUM,LANTYPE) VALUES
(:1,:2,:3,:4,:5,:6,:7)

INSERT INTO PDC_DUMMY_CHQSNO (AGREEMENTID, INSTLNUM, INSTRUMENT, CHQSEQNOUSED ) VALUES (:B4 , :B3 ,
:B2 , :B1 )

In this situation yes the poster should see if
(1) his application contains bitmap indexes
(2) If table PDC_PDC_TMP_TXN_H is an index organized table
(3) if Primary key row values are overlapping on insert

Dear Sir,

What about the below content of trace file for deadlock? I'm confused as the both waiter sessions are in shared (S) mode.

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00320013-00000450 150 865 X 61 1855 S
TX-00b4001d-0000040a 61 1855 X 150 865 S

session 865: DID 0001-0096-0000075F session 1855: DID 0001-003D-00001826
session 1855: DID 0001-003D-00001826 session 865: DID 0001-0096-0000075F

Rows waited on:
Session 865: no row
Session 1855: obj - rowid = 0000F0BE - AAAYM9AAHAAAfpxAAA
(dictionary objn - 61630, file - 7, block - 129649, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 1855:
sid: 1855 ser: 10999 audsid: 342682 user: 3029/ADMINUSR
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 61 O/S info: user: oracle, term: UNKNOWN, ospid: 18874522
image: oracle@ibmtpmsud1
client details:
O/S info: user: tptm059d, term: , ospid: 15204462
machine: ibmtpmsua1 program: sqlplus@ibmtpmsua1 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
UPDATE WQ_DETAIL SET WQ_STAT_CD = 'CLD' WHERE WQ_SEQ_NBR = :B1 RETURNING WQ_APRVL_LVL_NBR INTO :O0

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=6dc2qunrasnuf) -----
UPDATE WQ_DETAIL SET WQ_STAT_CD = 'CLD' WHERE WQ_SEQ_NBR = :B1 RETURNING WQ_APRVL_LVL_NBR INTO :O0
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object


more over, the object id [dictionary objn - 61630] is different from one which is showing in the current SQL.

Can you help me to find out the scenario where this can happen? Our database version is 11.2.0.2

Thanks in advance.

Pintu

Hi,

We have random deadlocks on production server. Here is some logs:

*** 2012-04-26 12:55:05.387
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
TM-00006d37-00000000 54 174 SX 52 111 SX SSX
TX-001d0000-000004e1 52 111 X 54 174 X

session 174: DID 0001-0036-0003FA45 session 111: DID 0001-0034-0002ED95
session 111: DID 0001-0034-0002ED95 session 174: DID 0001-0036-0003FA45

Rows waited on:
Session 174: obj - rowid = 00006D37 - AAAG03AAEAAAI5dAAB
(dictionary objn - 27959, file - 4, block - 36445, slot - 1)
Session 111: obj - rowid = 00006D37 - AAAG03AAEAAAPtzAAA
(dictionary objn - 27959, file - 4, block - 64371, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 111:
sid: 111 ser: 16985 audsid: 1395365 user: 32/SCHEMA1
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 52 O/S info: user: SCHEMA1, term: UNKNOWN, ospid: 26305
image: oracle@esshpop001.nsn-intra.net
client details:
O/S info: user: hera, term: unknown, ospid: 1234
machine: esvmp083.nsn-intra.net program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
DELETE FROM my_table_name where LAST_UPDATE < :1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=afs3quyjpchhr) -----
delete from SCHEMA1.my_table_name where column_name=:1

I see that deadlocks occur when something being deleting from table. I tried to reproduce several with times by instructions found in the web but with no luck.

I do following scenario in sql developer(Session1 and Session2 represents two instances of opened SQL Developers):
1.Session1 updates table A
2.Session2 updates table B
3.Session1 updates table B
4.Session2 updates table A

This scenario make both sql developer's to work/think endlessly but I do not see any errors about deadlocks.

Could you please clarify how I can reproduce deadlock?

COLUMN_NAME                 LAST_UPDATE
-----------------           ---------------------
X                           100
X                           1

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select 'X' column_name, 100000-rownum  last_update
  4    from all_objects
  5  /

Table created.

ops$tkyte%ORA11GR2> create index cname_idx on t(column_name);

Index created.

ops$tkyte%ORA11GR2> create index last_update_idx on t(last_update);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> explain plan for delete /*+ index( t cname_idx ) */  from t where column_name = 'X';

Explained.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1071367609

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |           | 68582 |  1071K|   147   (1)| 00:00:02 |
|   1 |  DELETE           | T         |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| CNAME_IDX | 68582 |  1071K|   147   (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COLUMN_NAME"='X')

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

ops$tkyte%ORA11GR2> explain plan for delete /*+ index( t last_update_idx ) */  from t where last_update < 100001;

Explained.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2498500162

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                 | 68582 |  1071K|   178   (1)| 00:00:03 |
|   1 |  DELETE           | T               |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| LAST_UPDATE_IDX | 68582 |  1071K|   178   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_UPDATE"<100001)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set echo off
get ready to run this in another window, don't run it, just get it ready:
delete /*+ index( t last_update_idx ) */  from t where last_update < 100001;
then hit enter here (do not run it!)
be ready to run it AS SOON AS POSSIBLE after you hit enter back here again

Ok, hit enter in the other window AS SOON AS POSSIBLE
ops$tkyte%ORA11GR2> delete /*+ index( t cname_idx ) */  from t where column_name = 'X';
delete /*+ index( t cname_idx ) */  from t where column_name = 'X'
                                         *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Tom,
users are getting ORA-00060 dead lock errors but the error does not show up in the alert log. In this scenario how to find out the trace file generated by the dead lock? We have THREE node RAC. Are there any views which store store information on dead locks?

Please help

In 11.2.0.3 RAC strangly in the alert log I do not get ora-60 any more or any other ora- error. Just a script stating deadlock on resource and a tracefile where you get the information.
The trace file name is static and is the same (on different paths usually) for all instances.
So looking for ora-60 does not help..
maybe you should look for "Deadlock"

We found the problem of our deadlocks thanks to this post and the clear responses!

In our case, using a bitmap index on a column in primary table caused the issues. Changed it to regular and no more deadlocks!

Thanks for all you do!

Thanks Tom. This article was very useful. Removing the bitmapped index and adding the regular index solved the issue. Good to know.

I'm getting the deadlocks and can't find it's reason. I've read all the posts but didn't find my case.
Here's my trace:

 
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000223bc-00000000        61     730    SX   SSX       45     329    SX   SSX
TM-000223bc-00000000        45     329    SX   SSX       61     730    SX   SSX
 
session 730: DID 0001-003D-00010B2C session 329: DID 0001-002D-000201C3 
session 329: DID 0001-002D-000201C3 session 730: DID 0001-003D-00010B2C 
 
Rows waited on:
  Session 730: no row
  Session 329: obj - rowid = 0003E263 - AAA+JqAANAAPCjRAAA
  (dictionary objn - 254563, file - 13, block - 3942609, slot - 0)
 
----- Information for the OTHER waiting sessions -----
Session 329:
  sid: 329 ser: 33773 audsid: 139595250 user: 68/ULTRA
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 45 O/S info: user: oracle, term: UNKNOWN, ospid: 30363
    image: oracle@db01
  client details:
    O/S info: user: система, term: SRV-ULT-APP-02, ospid: 5760:6064
    machine: ULTIMA\SRV-ULT-APP-02 program: UltimaService.exe
    client info: (AbidovU) 7508 {a7d0f832-806a-4426-957a-77ce2a12e616}
    action name: 173532 @ {a7d0f832-806a-4426-957a-77ce2a12e616}, hash value=1449491086
  current SQL:
  DELETE FROM TP_GOODS WHERE ID = :vOld_ID
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=ct8qs5qqav00f) -----
DELETE FROM TP_GOODS WHERE ID = :vOld_ID

254563 is an index on a logging table.
So, tp_goods has a trigger, which writes changes to the log_changes table, which has a log_id field, which is indexed by normal reverse index.

How can I find the root of the problem ?

How can we find which value was in the stf_id when the deadlock occured.

In my file I don't find something like this:

...
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=48 offset=0
bfp=01a70280 bln=22 avl=02 flg=05
value=5 <<<<<=== the bind variable values
bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=0 offset=24 <<<<<=== the bind variable values
bfp=01a70298 bln=22 avl=02 flg=01
value=4
End of cursor dump
***************** dump of cursor xsc=1a7681c **********************
........ 

We use DBMS_SCHEDULER to run my DDL-Statements in parallel sessions. Each session has a own range of Data, so there should not be a deadlock. 
The code runs 3 years without deadlock. But now there seems to be a particular case which causes deadlocks. 

An extract from my file:

Trace file /oracle/db/database/muest191/diag/rdbms/muest191/muest191/trace/muest191_j019_32065.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /oracle/db/product/12.1.0.2
System name: Linux
Node name: U1191021
Release: 3.12.49-11-default
Version: #1 SMP Wed Nov 11 20:52:43 UTC 2015 (8d714a0)
Machine: x86_64
Instance name: muest191
Redo thread mounted by this instance: 1
Oracle process number: 226
Unix process pid: 32065, image: oracle@U1191021 (J019)


*** 2017-08-14 22:15:09.909
*** SESSION ID:(59.27352) 2017-08-14 22:15:09.909
*** CLIENT ID:() 2017-08-14 22:15:09.909
*** SERVICE NAME:(SYS$USERS) 2017-08-14 22:15:09.909
*** MODULE NAME:(DBMS_SCHEDULER) 2017-08-14 22:15:09.909
*** CLIENT DRIVER:() 2017-08-14 22:15:09.909
*** ACTION NAME:(DATENVERTEILUNG_PROZESSOR21) 2017-08-14 22:15:09.909
 
 

*** 2017-08-14 22:15:09.909
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors
[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-00150000-00000002-00000000-00000000        226      59     X            220     707           S
TX-00130000-00000065-00000000-00000000        220     707     X            203     280           S
TX-00050014-000000B9-00000000-00000000        203     280     X            222     758           S
TX-0004000A-000000CA-00000000-00000000        222     758     X            226      59           S
 
session 59: DID 0001-00E2-00000002 session 707: DID 0001-00DC-00000002 
session 707: DID 0001-00DC-00000002 session 280: DID 0001-00CB-00000097 
session 280: DID 0001-00CB-00000097 session 758: DID 0001-00DE-00000002 
session 758: DID 0001-00DE-00000002 session 59: DID 0001-00E2-00000002 
 
Rows waited on:
  Session 59: obj - rowid = 000141C9 - AAAUHJAAJAAD77aAAA
  (dictionary objn - 82377, file - 9, block - 1031898, slot - 0)
  Session 707: obj - rowid = 000141C9 - AAAUHJAAJAAD7u2AAA
  (dictionary objn - 82377, file - 9, block - 1031094, slot - 0)
  Session 280: obj - rowid = 000141C9 - AAAUHJAAJAAD75bAAA
  (dictionary objn - 82377, file - 9, block - 1031771, slot - 0)
  Session 758: obj - rowid = 000141C9 - AAAUHJAAJAAD77rAAA
  (dictionary objn - 82377, file - 9, block - 1031915, slot - 0)
 
----- Information for the OTHER waiting sessions -----
Session 707:
  sid: 707 ser: 45004 audsid: 500118 user: 85/MISTRAL_MUEST_SCHEMA
    flags: (0x210041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 220 O/S info: user: oracle, term: UNKNOWN, ospid: 32053
    image: oracle@U1191021 (J016)
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 32053
    machine: U1191021 program: oracle@U1191021 (J016)
    application name: DBMS_SCHEDULER, hash value=2478762354
    action name: DATENVERTEILUNG_PROZESSOR20, hash value=3602391032
  current SQL:
  UPDATE STF SET STF_BGK =:B19 , STF_BPVM=:B18 , STF_DPF= :B17 , STF_KVERZ=:B16 ,STF_NEU=:B15 ,STF_NRGPB=:B14 , STF_NZ=:B13 ,STF_STB=:B12 , STF_VOLL=:B11 ,STF_STAND=:B10 ,STF_RTEL=:B9 , STF_GKEZ=:B8 , STF_RUECKST=:B7 +:B6 , STF_RSTNR=:B5 , STF_VSTNR=:B4 , STF_HSTNR=:B3 , STF_KSTNR=:B2 WHERE STF_ID=:B1 
 
Session 280:
  sid: 280 ser: 17790 audsid: 500094 user: 85/MISTRAL_MUEST_SCHEMA
    flags: (0x210041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 203 O/S info: user: oracle, term: UNKNOWN, ospid: 32019
    image: oracle@U1191021 (J005)
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 32019
    machine: U1191021 program: oracle@U1191021 (J005)
    application name: DBMS_SCHEDULER, hash value=2478762354
    action name: DATENVERTEILUNG_PROZESSOR10, hash value=1689634959
  current SQL:
  UPDATE STF SET STF_BGK =:B19 , STF_BPVM=:B18 , STF_DPF= :B17 , STF_KVERZ=:B16 ,STF_NEU=:B15 ,STF_NRGPB=:B14 , STF_NZ=:B13 ,STF_STB=:B12 , STF_VOLL=:B11 ,STF_STAND=:B10 ,STF_RTEL=:B9 , STF_GKEZ=:B8 , STF_RUECKST=:B7 +:B6 , STF_RSTNR=:B5 , STF_VSTNR=:B4 , STF_HSTNR=:B3 , STF_KSTNR=:B2 WHERE STF_ID=:B1 
 
Session 758:
  sid: 758 ser: 47430 audsid: 500120 user: 85/MISTRAL_MUEST_SCHEMA
    flags: (0x210041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 222 O/S info: user: oracle, term: UNKNOWN, ospid: 32057
    image: oracle@U1191021 (J017)
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 32057
    machine: U1191021 program: oracle@U1191021 (J017)
    application name: DBMS_SCHEDULER, hash value=2478762354
    action name: DATENVERTEILUNG_PROZESSOR15, hash value=3145486770
  current SQL:
  UPDATE STF SET STF_BGK =:B19 , STF_BPVM=:B18 , STF_DPF= :B17 , STF_KVERZ=:B16 ,STF_NEU=:B15 ,STF_NRGPB=:B14 , STF_NZ=:B13 ,STF_STB=:B12 , STF_VOLL=:B11 ,STF_STAND=:B10 ,STF_RTEL=:B9 , STF_GKEZ=:B8 , STF_RUECKST=:B7 +:B6 , STF_RSTNR=:B5 , STF_VSTNR=:B4 , STF_HSTNR=:B3 , STF_KSTNR=:B2 WHERE STF_ID=:B1 
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=8d8dv4sr4vdau) -----
UPDATE STF SET STF_BGK =:B19 , STF_BPVM=:B18 , STF_DPF= :B17 , STF_KVERZ=:B16 ,STF_NEU=:B15 ,STF_NRGPB=:B14 , STF_NZ=:B13 ,STF_STB=:B12 , STF_VOLL=:B11 ,STF_STAND=:B10 ,STF_RTEL=:B9 , STF_GKEZ=:B8 , STF_RUECKST=:B7 +:B6 , STF_RSTNR=:B5 , STF_VSTNR=:B4 , STF_HSTNR=:B3 , STF_KSTNR=:B2 WHERE STF_ID=:B1 
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x87b403b8       637  package body MISTRAL_MUEST_SCHEMA.AUFBAU_DATENMODELL_65
0x87b403b8      3600  package body MISTRAL_MUEST_SCHEMA.AUFBAU_DATENMODELL_65
0x87573918         1  anonymous block
===================================================

SQL> create table t ( x int, y int unique );

Table created.

SQL>
SQL> insert into t
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

Session 1
=========
SQL>
SQL> update t
  2  set y = 20
  3  where x = 4;

1 row updated.

Session 2
=========
SQL> update t
  2  set y = 100
  3  where x = 8;

1 row updated.

SQL> update t
  2  set y = 20
  3  where x = 6;
[stuck]

Session 1
=========

SQL> update t
  2  set y = 100
  3  where x = 2;
[stuck]

I'm wondering if it might be advisable to add a note to the original answer from Tom, so that people on 10g onwards, should use the DATA_OBJETY_ID rather than the OBJECT_ID, with the value returned from DBM_ROWID.ROWID_OBJECT.

I fell foul of this problem myself recently when a table had been MOVED (alter table move) which changed the DATA_OBJECT_ID to a new value different from the OBJECT_ID.

Just a thought.


Cheers,
Norm.