Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andre.

Asked: October 02, 2001 - 8:52 am UTC

Last updated: January 21, 2019 - 1:08 am UTC

Version: 8.1.5

Viewed 100K+ times! This question is

You Asked

Hi Tom !

How can I understand which two rows where involved in the following deadlock, which appears in this excerpt of a tracefile ?

Especially in the last two lines, Oracle seems to convey this information, but I was not able to locate the object or the rowids.

Would please elaborate on the whole output if possible ?

And how can prevent or at least avoid this situation ?

Thanks a lot !

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE RA_INTERFACE_LINES_ALL SET LINE_GDF_ATTRIBUTE1=:b1,LINE_GDF_ATTRIBUTE2=DECODE(:b2, NULL ,DECODE(:b3, NULL ,:b4,:b3),LINE_GDF_ATTRIBUTE2),LINE_GDF_ATTRIBUTE3=DECODE(:b6, NULL ,:b7,:b6),LINE_GDF_ATTRIBUTE4=DECODE(:b9, NULL ,DECODE(:b10, NULL ,:b11,:b10),LINE_GDF_ATTRIBUTE4),LINE_GDF_ATTRIBUTE5=DECODE(:b13, NULL ,DECODE(:b14, NULL ,:b15,:b14),LINE_GDF_ATTRIBUTE5),LINE_GDF_ATTRIBUTE6=DECODE(:b17, NULL ,DECODE(:b18, NULL ,:b19,:b18),LINE_GDF_ATTRIBUTE6),LINE_GDF_ATTRIBUTE7=DECODE(:b21, NULL ,:b22,LINE_GDF_ATTRIBUTE7),HEADER_GDF_ATTR_CATEGORY=DECODE(:b23, NULL ,'JL.BR.ARXTWMAI.Additional Info',HEADER_GDF_ATTR_CATEGORY),LINE_GDF_ATTR_CATEGORY=DECODE(:b24, NULL ,'JL.BR.ARXTWMAI.Additional Info',LINE_GDF_ATTR_CATEGORY) WHERE ROWID = :b25
----- PL/SQL Call Stack -----
object line object
handle number name
ab577a1c 125 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-00080048-00023aa6 57 125 X 58 62 X
TX-0009002a-00024308 58 62 X 57 125 X
session 125: DID 0001-0039-00000002 session 62: DID 0001-003A-00000002
session 62: DID 0001-003A-00000002 session 125: DID 0001-0039-00000002
Rows waited on:
Session 62: obj - rowid = 00001435 - AAEUluADmAAAswlAAA
Session 125: obj - rowid = 00001435 - AAEUluACwAAAY83AAB



and we said...

You use the dbms_rowid package to extract this info, for example:

ops$tkyte@ORA8I.WORLD> select rowid from dual;

ROWID
------------------
AAAADCAABAAAAHQAAA

ops$tkyte@ORA8I.WORLD> select dbms_rowid.rowid_object( 'AAAADCAABAAAAHQAAA' ) from dual;

DBMS_ROWID.ROWID_OBJECT('AAAADCAABAAAAHQAAA')
---------------------------------------------
                                          194

ops$tkyte@ORA8I.WORLD> select object_name from all_objects where data_object_id = 194;

OBJECT_NAME
------------------------------
DUAL


shows that given a rowid from dual, I can find the object number and hence the object name.

There are various apis in the dbms_rowid pacakge to get all of the information you need.

also:

ops$tkyte@ORA8I.WORLD> select to_number( '00001435', 'xxxxxxxxxxx' ) from dual;

TO_NUMBER('00001435','XXXXXXXXXXX')
-----------------------------------
                               5173


tells us this is data object 5173 -- its in HEX. You are updating more then one row in RA_INTERFACE_LINES_ALL but must be getting them in a different order so one session updated 1 row, another row 5 (for example) then the first session tried to update row 5 (blocks) and the second session tried to update row 1 (deadlock)



Rating

  (90 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reader

Reader, October 02, 2001 - 10:59 am UTC

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

Thanks

Tom Kyte
October 02, 2001 - 12:15 pm UTC

the middle number is the process number in hex. The other two, i really don't know what they are

DeadLock analysys

atul, August 07, 2002 - 3:07 am UTC

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


Tom Kyte
August 07, 2002 - 12:37 pm UTC

I'm going to hypothesize that there is a unique index on some (at least one) of the columns being updated.

The locks are NOT on a row -- they are due to unique conflicts. Here is exactly how to simulate this:

--------------------- test.sql -------------------------
drop table t;
create table t ( x int primary key );
insert into t values ( 1 );
insert into t values ( 2 );
insert into t values ( 3 );
insert into t values ( 4 );
commit;
update t set x = 5 where x = 1;
REM in another session, run test2.sql
pause
update t set x = 6 where x = 2;
---------------------------------------------------------

-------------------- test2.sql ---------------------------
variable x1 number
variable x2 number
exec :x1 := 6; :x2 := 3;
update t set x = :x1 where x = :x2;
exec :x1 := 5; :x2 := 4;
update t set x = :x1 where x = :x2;
---------------------------------------------------------



Here, session 1 will get the row updated from 1 to 5 -- 5 will be "locked" in the index.

Session 2 will then update 3 to 6 (no conflict, but 6 is "locked" in the index)

Session 2 will then update 4 to 5 -- this'll be a conflict, unique key violation POSSIBLE at this point. Session 2 will block here.

Next, session 1 tries to update 2 to 6 -- that'll be another conflict with session 2, unique key violation POSSIBLE at this point. Session 1 will block and then one of the sessions will get the dead lock.

That is when this trace file will be produced:

*** SESSION ID:(8.3883) 2002-08-07 11:09:23.816
DEADLOCK DETECTED
Current SQL statement for this session:
update t set x = :x1 where x = :x2
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-0003003d-000011e2 14 8 X 8 7 S
TX-00020054-0000109e 8 7 X 14 8 S
session 8: DID 0001-000E-00000002 session 7: DID 0001-0008-00000002
session 7: DID 0001-0008-00000002 session 8: DID 0001-000E-00000002
Rows waited on:
Session 7: no row
Session 8: no row
===================================================


So, basically, you have two sessions doing this update (or a similar update) and they are bumping into each other with a unique index. Look for whats unique in this table.

It isn't a row lock issue -- rather, an index key collision that is happening.


Further down in the trace file, you should be seeing something like:


...
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 **********************
........

And that should help you ID where the problem is (you'll see the BR_NO and be able to identify the ROWS being modified)

No lock infor such as lock graph found in trace file

Mike, April 18, 2003 - 10:21 pm UTC

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

Tom Kyte
April 19, 2003 - 11:56 am UTC

why would there be a trace? that is a blocking lock -- not a deadlock. session 1 commits and all it well in the world.

you do not have a deadlock.

Mike, April 20, 2003 - 12:07 am UTC

Could you please elaborate blocking-lock va deadlock ?

Tom Kyte
April 20, 2003 - 8:57 am UTC

you didn't have a deadlock. a deadlock happens when both transactions are stuck blocking eachother.

Each

session 1 updates "bob's" record.
session 2 updates "mary's" record.
session 1 tries to update mary's record -- gets blocked by 2.

At this point, we have a simple "block". session 2 is free to continue. if session 2 commits or rollsback -- session 1 continues on.


session 2 tries to update bob's record -- gets blocked by 1.


Now we have a deadlock, neither session has a hope of completing naturally - they would be stuck in this situation forever waiting on eachother. So, we fail the last statement with an error -- that session can now decide to

a) do something else, commit and finish
b) rollback

in either case, the other session will block until a) or b) occurs.

How to understand alert log file for dedlock.

A reader, April 20, 2003 - 2:01 pm UTC

Tom,

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

Tom Kyte
April 20, 2003 - 2:57 pm UTC

metalink.oracle.com has some notes on it.

DEAD LOCK AMOUNG DDL

Nirmal, September 01, 2003 - 5:34 am UTC

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

find the cause of dead lock

Linda, February 19, 2004 - 1:30 pm UTC

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 Kyte
February 19, 2004 - 2:36 pm UTC

you need to look at what the resources here were -- forget about the row for a moment, look at what the other session had -- what they wanted, what you had and what you wanted -- that is the cause.

we know you were both going after the same row -- it doesn't matter if it was "id=55" or "id=50320"

Reading the trace file

Chris, May 12, 2004 - 3:19 pm UTC

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? 

Tom Kyte
May 13, 2004 - 9:14 am UTC

means the object does not exist anymore OR you don't have permission to see it (try dba_objects)

Another deadlock

Yogesh B, June 23, 2004 - 8:49 am UTC

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 ?

Tom Kyte
June 23, 2004 - 9:36 am UTC

the reasons for deadlocks should be well known

the other session had the row this update was trying to update locked already.

this session had some other rows locks that the other session was trying to lock.

therefore -- each session had something the other wanted, deadlock.



the sql statements are a dump of the state of the session that was chosen as the "victim", the one that get the ora-60.

ora -04020

s devarshi, August 03, 2004 - 5:52 am UTC

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

Tom Kyte
August 03, 2004 - 8:54 am UTC

describe to me the circumstances under which you get this error message.

ora 4020

S Devarshi, August 04, 2004 - 1:16 am UTC

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

Tom Kyte
August 04, 2004 - 9:34 am UTC

thats part of the standard upgrade process, yes.

ORA-4020

A reader, August 23, 2004 - 6:01 pm UTC

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

Tom Kyte
August 23, 2004 - 7:46 pm UTC

XXXX.XXXX

that is a strange name - but anyway, how do you know this is a self deadlock?!?

Self deadlock

A reader, August 23, 2004 - 7:52 pm UTC

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

Tom Kyte
August 23, 2004 - 7:58 pm UTC

won't be able to help without any supporting information, like the actual trace file info and anything else relevant you might think of..

Self deadlock

A reader, August 24, 2004 - 9:09 am UTC

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

Tom Kyte
August 24, 2004 - 10:12 am UTC

I'd need an example -- but recursive sql is run in the equivalent of an autonomous transaction, easy enough to self deadlock yourself.

Related Question

Sathish, September 17, 2004 - 3:20 pm UTC

*** 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 .

Tom Kyte
September 17, 2004 - 3:41 pm UTC

how about a little background info -- version, anything "special" (eg: MS transaction server, XA, whatever), OS

Sorry

Sathish, September 17, 2004 - 4:12 pm UTC

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 .

Tom Kyte
September 17, 2004 - 8:09 pm UTC

show me everthing about this table

$ exp userid=u/p tables=accounts rows=n
$ imp userid=u/p full=y show=y

will show me what I want.

accounts

Sathish, September 20, 2004 - 9:42 am UTC

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


Tom Kyte
September 20, 2004 - 10:45 am UTC

No secondary indexes, triggers or anything????

imp would not show me that create table, it would look like this:

given a table created:

create table t ( x int, y int, constraint t_check check (x >y ) )

imp would show:


"CREATE TABLE "T" ("X" NUMBER(*,0), "Y" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
") TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "T"

"ALTER TABLE "T" ADD CONSTRAINT "T_CHECK" CHECK (x >y ) ENABLE NOVALIDATE"
"ALTER TABLE "T" ENABLE CONSTRAINT "T_CHECK""
Import terminated successfully without warnings.


deadlock trace file

Sathish, September 21, 2004 - 2:20 pm UTC

*** 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.

Tom Kyte
September 21, 2004 - 2:39 pm UTC

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;
 

A reader, September 21, 2004 - 5:48 pm UTC


A reader, November 24, 2004 - 11:09 am UTC


DEADLOCK DETECTED

Branka, November 29, 2004 - 2:11 pm UTC

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.




Tom Kyte
November 29, 2004 - 3:51 pm UTC

is party a parent table in a parent child relationship?

DEADLOCK DETECTED

Branka, November 30, 2004 - 11:44 am UTC

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

Tom Kyte
November 30, 2004 - 11:54 am UTC

is the foreign key in the child table INDEXED?

DEADLOCK DETECTED

Branka, November 30, 2004 - 12:12 pm UTC

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.



Tom Kyte
November 30, 2004 - 12:33 pm UTC

this is totally due to unindexed foreign keys.

(and think about it -- you delete from T where fkey = :x -- full scan? not exactly what you probably want)


run this:



set echo on

drop table c;
drop table p;

create table p ( x int primary key );
create table c ( x references p );
REM create index c_idx on c(x);

insert into p select rownum from all_users where rownum <= 2;
insert into c select * from p;
commit;

delete from c where x = 1;
set echo off
prompt in another session:
prompt delete from c where x = 2;;
set echo on
pause
set echo off
prompt in another session:
prompt delete from p where x = 2;;
prompt right after this blocks...
delete from p where x = 1;

------------------------------------------------------------

Try it with the index on and off.

see also:
</code> http://asktom.oracle.com/~tkyte/unindex/index.html <code>




DEADLOCK DETECTED

Branka, December 01, 2004 - 10:19 am UTC


DEADLOCK DETECTED

Branka, December 01, 2004 - 1:04 pm UTC

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


Tom Kyte
December 01, 2004 - 2:41 pm UTC

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. 

dead locks

Reader, July 07, 2005 - 5:26 am UTC

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 Kyte
July 07, 2005 - 9:21 am UTC

why unique indexes - I see an update to a map_clbal column and it seems unlikely that it was uniquely indexed? is it?

dead locks

reader, July 12, 2005 - 7:34 am UTC

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 Kyte
July 12, 2005 - 5:05 pm UTC

I always start by understanding the table and indexes (and always being suspicious of unindexed foreign keys)

dead lock

reader, July 13, 2005 - 12:18 am UTC

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 Kyte
July 13, 2005 - 11:03 am UTC

what is the entire transaction though, does your transaction really only affect this one table? where/what is getting the rowid in the first place (and why isn't it locking the row when it gets it -- you do see you have two people trying to update the same row(s) simultaneously -- don't you think that might be "a problem")

Reading from trace file

Khalid, July 29, 2005 - 5:41 am UTC

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.

Tom Kyte
July 29, 2005 - 9:11 am UTC

</code> http://docs.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_rowid.htm#997154 <code>

rowid_object returns the DATA_OBJECT_ID, not the OBJECT_ID

This is a table you have truncated or moved about 1,607 times. The data object id and object id are different for it.

CURSOR information

Khalid, July 29, 2005 - 10:45 am UTC

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.


Tom Kyte
July 29, 2005 - 2:40 pm UTC

that is the session state dump, the server process "knows" what sql the current session had going.


... Is it possible to
identify the session which executed that cursor. ...

which cursor?

CURSOR information

A reader, July 30, 2005 - 5:24 am UTC

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.

Tom Kyte
July 30, 2005 - 8:57 am UTC

are there unindex foreign keys involved -- like manager_id pointing back to employee_id and manager_id is not indexed?

CURSOR information

Khalid, August 01, 2005 - 7:21 am UTC

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.

Tom Kyte
August 01, 2005 - 7:46 am UTC

an error log table that does updates, interesting.

well deadlocks on this table will happen if

a) I update "row 1"
b) you update "row 2"
c) I try to update "row 2" but cannot
d) you try to update "row 1" <<== deadlock.


that would be the most likely situation (unique constraints could cause it as well, working on entirely different rows all together)

virgile, August 01, 2005 - 8:48 am UTC

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


Tom Kyte
August 01, 2005 - 9:46 am UTC

absolutely, I would assume (i shouldn't, history proves that out) that no one would use them in an update environment..

Reading deadlock trace files

Senthil, August 11, 2005 - 5:25 am UTC

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 Kyte
August 11, 2005 - 9:58 am UTC

"it was an index", how so? was it a unique index?

getting some information from STATSPACK report

Khalid, August 11, 2005 - 8:20 am UTC

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 Kyte
August 11, 2005 - 10:07 am UTC

statspack is system wide, it won't be useful for deadlock resolution.

you want application traces.

Reading deadlock trace files

Senthil, August 16, 2005 - 12:46 pm UTC

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

Tom Kyte
August 17, 2005 - 11:55 am UTC

bitmap indexes are not appropriate for transactional systems, they are only for read only/read mostly system.

they are NOT for systems where you insert/update/delete frequently. Or where you do single row modifications.

Reading Deadlock tracle files

senthil, August 30, 2005 - 2:26 pm UTC

Tom,

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

Thanks,
Senthil.

Tom Kyte
August 30, 2005 - 2:46 pm UTC

bitmaps cause deadlock in a concurrent situation,

others do not.

Dead Lock trace file

vivek, September 29, 2005 - 11:59 am UTC

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 Kyte
September 30, 2005 - 8:21 am UTC

1) ul is user lock:

700000020bc9380 82 package body SYS.DBMS_LOCK
700000020bc9380 98 package body SYS.DBMS_LOCK
7000000209f6698 114 package body IBRPRD.LOCKS

you are creating your own.

2) do you have unindexed foreign keys?



Dead locks on insert. Can u please explain

Ranganatha Ramineni, January 10, 2006 - 4:59 pm UTC

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 Kyte
January 10, 2006 - 7:39 pm UTC

knowing the tables being waited on that are listed there - as well as their relationship to the table being inserted into would be useful

No other tables

Ranganatha Ramineni, January 10, 2006 - 11:57 pm UTC

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).


Tom Kyte
January 12, 2006 - 10:04 am UTC

there are other referenced objects in the trace file however.

bitmap indexes ?

lh, January 11, 2006 - 5:56 am UTC

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

ROWID oddity

Jonathan Lewis, January 11, 2006 - 6:09 am UTC

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 ?


No bitmap indexes

Ranganatha Ramineni, January 11, 2006 - 10:48 am UTC

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 Kyte
January 12, 2006 - 10:37 am UTC

no, it would not be unique constraint violated.

do this:

create table t ( x int primary key );


in two sessions do this:


session 1 session 2
insert into t values ( 1 );
insert into t values (2);
insert into t values ( 2 ); <<<=== blocks
insert into t values ( 1 ); <<== causes
deadlock


it would NOT cause a unique constraint violation.

dbms_Rowid.rowid_object_id returning data_object_id

Ranganatha Ramineni, January 12, 2006 - 10:59 am UTC

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 Kyte
January 12, 2006 - 11:13 am UTC

see above example, I have a feeling that is your issue.

we were talking about these ids:

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)


2955, 23760.

Clarification of OBJECT IDs

Ranganatha Ramineni, January 12, 2006 - 1:12 pm UTC

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.

 

Anomalies galore

Jonathan Lewis, January 13, 2006 - 7:04 am UTC

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.




Thank you very much

Ranganatha Ramineni, January 13, 2006 - 2:41 pm UTC

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.


Odd rowids

Jonathan Lewis, January 14, 2006 - 3:22 am UTC

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.

more fun with deadlocks & bitmap indexes

Alex, January 19, 2006 - 12:58 pm UTC

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!

Tom Kyte
January 19, 2006 - 1:55 pm UTC

got ddl? (simple ddl)

RE: more fun with deadlocks & bitmap indexes

Alex, January 19, 2006 - 5:40 pm UTC

-- 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);


Tom Kyte
January 20, 2006 - 8:41 am UTC

umm, so what is "t1", "t2", "t3" here - but basically, if you are inserting into the "parent tables" with these foreign keys and bitmaps - not sure that is going to be a "good idea". foreign keys use the indexes (locking them) as you modify parent tables.


I don't know what "t1", "t2", "t3" are - you say some of the tables are static, some are not - help us recreate your situation here - order of operation, what happens to what.

(having bitmaps on during single row loads = not good idea, might want to rethink that)

RE: more fun with deadlocks & bitmap indexes

Alex, January 23, 2006 - 2:51 am UTC

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 Kyte
January 23, 2006 - 10:11 am UTC

table upload - describe this table. You are not by any change removing rows as you process rows from this table are you?

Have you tried, just in sqlplus, using your knowledge of the application, just to play with some data and see what you see.

"So, you had an accident....."

Alexander the ok, January 23, 2006 - 10:37 am UTC

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.

Tom Kyte
January 23, 2006 - 10:48 am UTC

gotta love toad, don't you. I've never been a fan of

a) using sys
b) recompiling invalid objects (they sort of take care of themselves)

don't know which one I like less, probably a)

Is this a live production system? You do have your tar open right??? (because I am *not* support)


You compiled some stuff - toad might be "over compiling", invalidations cascaded to other objects. standard is out of wack - but standard is pretty important. dbms_application_info is trying to run, it needs standard by your standard is invalidated and trying to compile it wedges the system.


I'll ask you to work with support on this one. I'd rather not say "try this that or the other thing" as it could cause more problems than it fixes.


Alexander the ok, January 23, 2006 - 10:58 am UTC

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.

Tom Kyte
January 23, 2006 - 11:47 am UTC

well, don't contact them about the deadlock - contact them about the fact that your database is "broke"

As SYS you can hose lots of stuff.
As a DBA you can hose lots of stuff.
With Toad - it makes it possible at the touch of a button.

Give me root, I'll mess up stuff big time.


Not knowing exactly what you did makes it really hard. Can you connect as sysdba? Can you shutdown? Can you startup? if you connect as sysdba and compile standard - does it work (prepare to *wait*, if everything isn't already invalid - it will be after that)

if you get that to compile, just let everything compile by itself naturally over time as it is referenced.

Alexander, January 23, 2006 - 11:00 am UTC

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>

Tom Kyte
January 23, 2006 - 11:48 am UTC

No, I meant logging into toad and pushing buttons!

but maybe I'll ask him to change that - since it doesn't require sys access at all...

Alexander, January 23, 2006 - 12:23 pm UTC

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! 

Tom Kyte
January 23, 2006 - 12:57 pm UTC

can you disable that trigger, then compile, then enable that trigger.

standard is special.

SYS is really special and magical.

this is like logging in as root and wondering why a command that was "innocent" before kills the entire system.

it is deadlocked trying to recomiple stuff. in order to run something - standard must be there, but standard isn't there, so the thing running tries to ....

Alexander, January 23, 2006 - 1:24 pm UTC

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? 

Tom Kyte
January 23, 2006 - 10:58 pm UTC

if you are not using CDC (change data capture), it is a safe trigger to disable.



CDC

Mark J. Bobak, January 23, 2006 - 3:14 pm UTC

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

-Mark

rmcdc.sql

Mark J. Bobak, January 23, 2006 - 3:20 pm UTC

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

RE: more fun with deadlocks & bitmap indexes

Alex, January 23, 2006 - 7:29 pm UTC

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.

Alexander the ok, January 24, 2006 - 10:02 am UTC

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....)

Alexander the ok, January 24, 2006 - 11:55 am UTC

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.

How could a MV be involved in a deadlock?

A reader, April 20, 2006 - 6:06 pm UTC

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!


Deadlock within the same session?

Dusan, April 21, 2006 - 9:23 am UTC

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.


Tom Kyte
April 21, 2006 - 9:37 am UTC

ugh, cursor sharing is not exact - man oh man.... bummer number 1.

I'll guess "you are using autonomous transactions"

Deadlock - objects do not exists neither user_objects nor dba_objects

Frédéric Castelain, July 19, 2006 - 3:50 am UTC

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. 

Dead lock - missing sql in the trace

Kiran Ghanta, February 13, 2007 - 2:14 pm UTC

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

tkprof on a deallock.trc file

A reader, February 23, 2007 - 8:42 pm UTC

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?
Tom Kyte
February 26, 2007 - 12:48 pm UTC

metalink has many notes covering that.


tkprof is not used on them, no

Deadlock within SAME session???

Kim Anthonisen, March 27, 2007 - 5:35 pm UTC

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
===================================================

Tom Kyte
March 28, 2007 - 11:27 am UTC

do you use autonomous transactions.

Strange deadlock

Jacky, August 11, 2007 - 5:03 am UTC

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

strange deadlock

Jacky, August 11, 2007 - 5:08 am UTC

*** 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

Single resource deadlock

Vinu, November 02, 2007 - 6:47 am UTC

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

DeadLock Trace file 'No rows' followup

A reader, November 13, 2007 - 10:36 pm UTC

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)

Daniel, March 31, 2008 - 10:14 am UTC

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.

Tom Kyte
March 31, 2008 - 12:41 pm UTC

... A deadlock among DDL and parse locks is detected. ....

so, what are you doing to this table, a little context never hurts.

Reading Deadlock trace file

Kv, August 09, 2008 - 10:17 am UTC

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

Advice please

Kv, August 14, 2008 - 12:32 pm UTC

Hi Tom,

Can you please offer some advice o the above.

Thanks

Tom Kyte
August 18, 2008 - 10:10 am UTC

I cannot really read it easily (use code button)

but, looks like both sessions are trying to insert the same keys (logic error, what is generating the keys????)


set echo on

drop table t;

create table t ( x int primary key );
commit;

insert into t values ( 1 );
set echo off
prompt in another session:
prompt insert into t values (2);;
pause
prompt in another session:
prompt insert into t values (1);;
set echo on
insert into t values (2);


that'll illustrate the issue.

Siva, February 13, 2009 - 1:32 am UTC

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'

Tom Kyte
February 16, 2009 - 10:13 am UTC

8.1.7 - ugh.

so, give us the COMPLETE schema for the two objects involved (indexes, constraints, triggers - *everthing*)

and describe to us the transaction processing surrounding them.

no information in trace file

Anil, June 29, 2009 - 3:07 pm UTC

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

Tom Kyte
July 06, 2009 - 6:31 pm UTC

you seem to have pasted a trace file with information in it into this page.

so, why do you say "no information in trace file"?

buffer deadlock in trace file

bhaskar, August 10, 2010 - 3:19 am UTC

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
********************************************************************************

Tom Kyte
August 10, 2010 - 7:51 am UTC

buffer deadlock is when we are trying to get a buffer in a certain mode (probably current mode in your example) and cannot because someone else has it - so we yield briefly (stop trying to get it) in order to let them finish - and then try again.


locking issue

Bhaskar, September 09, 2010 - 12:17 am UTC

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?
Tom Kyte
September 09, 2010 - 8:54 pm UTC

what "buffer lock" issue?

or do you mean what we were talking about above - the buffer deadlock? the only way to "mitigate" that would be to decrease contention on blocks - cluster data, use partitioning, minimize the number of records per block, use reverse key indexes, etc etc etc - in other words "it depends". You'd have to know what was the cause of the contention to apply 'fix the contention'



as for the unique conflicts - you'd have to explain to me why you get them before we could come up with logic that would avoid them. for example:

I get unique conflicts because I try to generate my own primary keys using some tables I have and incrementing numbers in them. SOLUTION: use sequences or sys_guid()

so - tell me why you have unique conflicts on some attribute and we can discuss ways to modify your current processing/current approach to be more scalable and concurrent.

buffer deadlock

A reader, September 20, 2010 - 5:43 am UTC

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.
Tom Kyte
September 20, 2010 - 2:55 pm UTC

you are NOT waiting on that - did you see how long you waited on that in the tkprof? 0.00 seconds. There is nothing to fix as far as the buffer deadlock goes.

look at this:

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


the major waits are for IO - db file seq read, buffer busy waits. After that, you want to look at the locking issue (enqueues) - the buffer deadlock isn't on the radar screen *at all*

Dead lock issue

Reader, September 17, 2011 - 5:25 am UTC

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
Tom Kyte
September 17, 2011 - 11:17 am UTC

before I even look at trace files - I ask "do you have unindexed foreign keys or bitmap indexes in place"

http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteunindex.html

deadlock graph

Houri Mohamed, September 20, 2011 - 7:18 am UTC

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

Tom Kyte
September 20, 2011 - 6:44 pm UTC

I always start with those two - it has saved me thousands of hours of my own time. I just don't even look at them until they get back to me on that - because 99 times out of a hundred - it is one of them :)

trace file confusing for deadlock

Pintu Shah, December 12, 2011 - 8:18 pm UTC

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

Deadlock: can not reproduce

Ivan, May 14, 2012 - 9:18 am UTC

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?
Tom Kyte
May 14, 2012 - 12:41 pm UTC

Ok, here are the two sql's.

It should be pretty obvious/easy to figure this out from these two queries...


DELETE FROM my_table_name where LAST_UPDATE < :1

delete from SCHEMA1.my_table_name where column_name=:1



Suppose in the table, in ROWID ORDER, you have:


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


Now, If I access via an index on column_name, where column_name = X, I'll read those rows in order of

(x,100), (x,1)

since indexes are sorted by index_key,rowid.

If I access via an index on last_udate, where last_update < 200, we will access the records

(x,1), (x,100)

since 1 < 100 < 200...

So, all it would take is for those two deletes to be executed at about the same time. Deadlock.

It is pretty clear just from looking at the SQL that this would be entirely possible.



here is your example:


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





are you trying to delete rows you have not read out (lost update problems?)

why are you trying to have two separate transactions delete the same exact data? (they are you know...)



How to locate trace file for deadlock when there is no error in the alert log

Bangalorean, November 21, 2012 - 5:40 am UTC

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
Tom Kyte
November 21, 2012 - 9:35 am UTC

check all of your alert logs, these should be logged into the alert log.

you can always grep for it in the trace directories.

A note on the last post i saw

Antuan, March 05, 2013 - 9:41 am UTC

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"

Yep it was the Bitmap Index

Wendy H, April 17, 2013 - 3:02 pm UTC

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!

good

A reader, April 29, 2013 - 3:09 pm UTC

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

Can't figure out the reason of deadlock

Alex Olaf, October 23, 2013 - 2:40 pm UTC

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 I find the value in Bind Variable

Armin Hoefling, August 22, 2017 - 2:03 pm UTC

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
===================================================

Connor McDonald
August 23, 2017 - 7:33 am UTC

"Each session has a own range of Data, so there should not be a deadlock. "

Notice how your deadlock graph is

- holds X
- waits for S

If it was simply competing for the same rows you would see holds X, waits for X

Waiting for S means something along the lines of a lock required for a uniqueness check, or you have too many concurrent transactions occurring in a block (so an ITL shortage). For example, lets say I have a table with 10 rows, and session 1 will update rows 1=>5, and session 2 will do rows 6-10. Shouldn't be a problem right ?

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]



and eventually one of them gets:

ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

The reason is - we were competing for "potential" values for the unique key of y.

But in terms of binds, the best you can do (without explictly doing some tracing or code changes) is using the rowid's in the deadlock trace to get originating values.

Suggested update to original answer

Norman Dunbar, January 17, 2019 - 10:06 am UTC

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.


Connor McDonald
January 21, 2019 - 1:06 am UTC

Agreed. Thanks for letting us know.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library