Reader
October 2, 2001 - 10am Central time zone
Reviewer: Reader from USA
The TX is structured <rbs><slot><wrap>
Could you tell us how DID is structured
Thanks
Followup October 2, 2001 - 12pm Central time zone:
the middle number is the process number in hex. The other two, i really don't know what they are
DeadLock analysys
August 7, 2002 - 3am Central time zone
Reviewer: atul from India
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
Followup August 7, 2002 - 12pm Central time zone:
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
April 18, 2003 - 10pm Central time zone
Reviewer: Mike from TX
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
Followup April 19, 2003 - 11am Central time zone:
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.
April 20, 2003 - 12am Central time zone
Reviewer: Mike from TX
Could you please elaborate blocking-lock va deadlock ?
Followup April 20, 2003 - 8am Central time zone:
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.
April 20, 2003 - 2pm Central time zone
Reviewer: A reader
Tom,
Where can I find document to understand the output in alert log file.
Followup April 20, 2003 - 2pm Central time zone:
metalink.oracle.com has some notes on it.
DEAD LOCK AMOUNG DDL
September 1, 2003 - 5am Central time zone
Reviewer: Nirmal from Qatar
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
February 19, 2004 - 1pm Central time zone
Reviewer: Linda from USA
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....
Followup February 19, 2004 - 2pm Central time zone:
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
May 12, 2004 - 3pm Central time zone
Reviewer: Chris from Louisville, KY USA
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?
Followup May 13, 2004 - 9am Central time zone:
means the object does not exist anymore OR you don't have permission to see it (try dba_objects)
Another deadlock
June 23, 2004 - 8am Central time zone
Reviewer: Yogesh B from Pune, India
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 ?
Followup June 23, 2004 - 9am Central time zone:
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
August 3, 2004 - 5am Central time zone
Reviewer: s devarshi from Orissa,India
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
Followup August 3, 2004 - 8am Central time zone:
describe to me the circumstances under which you get this error message.
ora 4020
August 4, 2004 - 1am Central time zone
Reviewer: S Devarshi from Orissa,India
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
Followup August 4, 2004 - 9am Central time zone:
thats part of the standard upgrade process, yes.
ORA-4020
August 23, 2004 - 6pm Central time zone
Reviewer: A reader
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
Followup August 23, 2004 - 7pm Central time zone:
XXXX.XXXX
that is a strange name - but anyway, how do you know this is a self deadlock?!?
Self deadlock
August 23, 2004 - 7pm Central time zone
Reviewer: A reader
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
Followup August 23, 2004 - 7pm Central time zone:
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
August 24, 2004 - 9am Central time zone
Reviewer: A reader
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
Followup August 24, 2004 - 10am Central time zone:
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
September 17, 2004 - 3pm Central time zone
Reviewer: Sathish from Va USA
*** 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 .
Followup September 17, 2004 - 3pm Central time zone:
how about a little background info -- version, anything "special" (eg: MS transaction server, XA,
whatever), OS
Sorry
September 17, 2004 - 4pm Central time zone
Reviewer: Sathish from Va USA
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 .
Followup September 17, 2004 - 8pm Central time zone:
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
September 20, 2004 - 9am Central time zone
Reviewer: Sathish from Va USA
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
Followup September 20, 2004 - 10am Central time zone:
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
September 21, 2004 - 2pm Central time zone
Reviewer: Sathish from va
*** 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.
Followup September 21, 2004 - 2pm Central time zone:
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;
September 21, 2004 - 5pm Central time zone
Reviewer: A reader
November 24, 2004 - 11am Central time zone
Reviewer: A reader
DEADLOCK DETECTED
November 29, 2004 - 2pm Central time zone
Reviewer: Branka from VA, USA
In following case how to find out objects involved in deadlock. It's showing no rows and problem
is with delete, not with update.
Also, what does it mean when you have 2 same Resource Name?
I know that second package call first one and delete statement is in that first one. Is resource
name name of that Stored Procedure?
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM PARTY WHERE PARTY_ID = :b1
----- PL/SQL Call Stack -----
object line object
handle number name
3c38b45b8 6184 package body PROD_USR.PARTY_CHG_PKG
3cf9d0ff0 1554 package body PROD_USR.IG_PKG
3d5d6de10 3 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-0000743c-00000000 49 356 SX SSX 87 97 SX SSX
TM-0000743c-00000000 87 97 SX SSX 49 356 SX SSX
session 356: DID 0001-0031-0000441C session 97: DID 0001-0057-00002C3E
session 97: DID 0001-0057-00002C3E session 356: DID 0001-0031-0000441C
Rows waited on:
Session 97: no row
Session 356: no row
Information on the OTHER waiting sessions:
Session 97:
pid=87 serial=55565 audsid=8033546 user: 60/PROD_USR
O/S info: user: nobody, term: , ospid: 14814, machine: web3.att.teetimes.com
program: 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.
Followup November 29, 2004 - 3pm Central time zone:
is party a parent table in a parent child relationship?
DEADLOCK DETECTED
November 30, 2004 - 11am Central time zone
Reviewer: Branka from VA, USA
yes it is.
That is last statement in the Stored Procedure. Before it, all child records are deleted.
Followup November 30, 2004 - 11am Central time zone:
is the foreign key in the child table INDEXED?
DEADLOCK DETECTED
November 30, 2004 - 12pm Central time zone
Reviewer: Branka from VA, USA
Only one of child tables has index on foreign key column (PARTY_ID). Party table has 5 child
tables.
I find one thing, but I am not sure is that relevant (I changed it any way).
At the beginning of the Stored procedure I have:
SELECT NVL(MIN(COUPON_ID),0) INTO N_TARGET
FROM COUPON_PLAY
WHERE PARTY_ID = nParty_id;
IF N_TARGET > 0 THEN
DELETE FROM COUPON_PLAY
WHERE COUPON_ID = N_TARGET
AND PARTY_ID = nParty_id;
END IF;
And than on the end:
DELETE FROM COUPON_PLAY
WHERE PARTY_ID = nParty_id;
I commented out first part, because it does not seem to have any reason for that.
This is not table with index on foreign key.
Followup November 30, 2004 - 12pm Central time zone:
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:
http://asktom.oracle.com/~tkyte/unindex/index.html
DEADLOCK DETECTED
December 1, 2004 - 10am Central time zone
Reviewer: Branka from VA, USA
DEADLOCK DETECTED
December 1, 2004 - 1pm Central time zone
Reviewer: Branka from VA, USA
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
Followup December 1, 2004 - 2pm Central time zone:
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
July 7, 2005 - 5am Central time zone
Reviewer: Reader from India
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...
Followup July 7, 2005 - 9am Central time zone:
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
July 12, 2005 - 7am Central time zone
Reviewer: reader from India
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
Followup July 12, 2005 - 5pm Central time zone:
I always start by understanding the table and indexes (and always being suspicious of unindexed
foreign keys)
dead lock
July 13, 2005 - 12am Central time zone
Reviewer: reader from India
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
Followup July 13, 2005 - 11am Central time zone:
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
July 29, 2005 - 5am Central time zone
Reviewer: Khalid from Bangalore
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.
CURSOR information
July 29, 2005 - 10am Central time zone
Reviewer: Khalid from Bangalore
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.
Followup July 29, 2005 - 2pm Central time zone:
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
July 30, 2005 - 5am Central time zone
Reviewer: A reader
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.
Followup July 30, 2005 - 8am Central time zone:
are there unindex foreign keys involved -- like manager_id pointing back to employee_id and
manager_id is not indexed?
CURSOR information
August 1, 2005 - 7am Central time zone
Reviewer: Khalid from Bangalore
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.
Followup August 1, 2005 - 7am Central time zone:
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)
August 1, 2005 - 8am Central time zone
Reviewer: virgile from France, paris
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
Followup August 1, 2005 - 9am Central time zone:
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
August 11, 2005 - 5am Central time zone
Reviewer: Senthil from Bangalore,India
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.
Followup August 11, 2005 - 9am Central time zone:
"it was an index", how so? was it a unique index?
getting some information from STATSPACK report
August 11, 2005 - 8am Central time zone
Reviewer: Khalid from Bangalore
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
Followup August 11, 2005 - 10am Central time zone:
statspack is system wide, it won't be useful for deadlock resolution.
you want application traces.
Reading deadlock trace files
August 16, 2005 - 12pm Central time zone
Reviewer: Senthil from Bangalore,India
Tom,
yes the deadlock occured in the index.It was bitmap index.
Nonunique. Do i need to do any design changes?
Followup August 17, 2005 - 11am Central time zone:
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
August 30, 2005 - 2pm Central time zone
Reviewer: senthil
Tom,
What other type index will avoid the reoccurance of deadlock.?is it better to drop the index.
Thanks,
Senthil.
Followup August 30, 2005 - 2pm Central time zone:
bitmaps cause deadlock in a concurrent situation,
others do not.
Dead Lock trace file
September 29, 2005 - 11am Central time zone
Reviewer: vivek from UK
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
Followup September 30, 2005 - 8am Central time zone:
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
January 10, 2006 - 4pm Central time zone
Reviewer: Ranganatha Ramineni from Charlotte, NC USA
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.
Followup January 10, 2006 - 7pm Central time zone:
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
January 10, 2006 - 11pm Central time zone
Reviewer: Ranganatha Ramineni from Charlotte, NC USA
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).
Followup January 12, 2006 - 10am Central time zone:
there are other referenced objects in the trace file however.
bitmap indexes ?
January 11, 2006 - 5am Central time zone
Reviewer: lh from finnland
Do you have bitmap indexes on this table. They do lock many rows and can easily cause deadlock
situations during concurrent inserts.
ROWID oddity
January 11, 2006 - 6am Central time zone
Reviewer: Jonathan Lewis from UK
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
January 11, 2006 - 10am Central time zone
Reviewer: Ranganatha Ramineni from Charlotte, NC USA
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.
Followup January 12, 2006 - 10am Central time zone:
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
January 12, 2006 - 10am Central time zone
Reviewer: Ranganatha Ramineni from Charlotte, NC USA
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.
Followup January 12, 2006 - 11am Central time zone:
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
January 12, 2006 - 1pm Central time zone
Reviewer: Ranganatha Ramineni from Charlotte NC USA
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
January 13, 2006 - 7am Central time zone
Reviewer: Jonathan Lewis from UK
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
January 13, 2006 - 2pm Central time zone
Reviewer: Ranganatha Ramineni from Charlotte, NC USA
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
January 14, 2006 - 3am Central time zone
Reviewer: Jonathan Lewis from UK
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
January 19, 2006 - 12pm Central time zone
Reviewer: Alex from Mountain View, CA
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!
Followup January 19, 2006 - 1pm Central time zone:
got ddl? (simple ddl)
RE: more fun with deadlocks & bitmap indexes
January 19, 2006 - 5pm Central time zone
Reviewer: Alex from Mountain View, CA
-- 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);
Followup January 20, 2006 - 8am Central time zone:
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
January 23, 2006 - 2am Central time zone
Reviewer: Alex from Mountain View, CA
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.
Followup January 23, 2006 - 10am Central time zone:
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....."
January 23, 2006 - 10am Central time zone
Reviewer: Alexander the ok
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.
Followup January 23, 2006 - 10am Central time zone:
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.
January 23, 2006 - 10am Central time zone
Reviewer: Alexander the ok
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.
Followup January 23, 2006 - 11am Central time zone:
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.
January 23, 2006 - 11am Central time zone
Reviewer: Alexander
Oh and by the way, I ran that in sys because Jon's article said to:
http://www.jlcomp.demon.co.uk/oerr.html
Followup January 23, 2006 - 11am Central time zone:
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...
January 23, 2006 - 12pm Central time zone
Reviewer: Alexander
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!
Followup January 23, 2006 - 12pm Central time zone:
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 ....
January 23, 2006 - 1pm Central time zone
Reviewer: Alexander
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?
Followup January 23, 2006 - 10pm Central time zone:
if you are not using CDC (change data capture), it is a safe trigger to disable.
CDC
January 23, 2006 - 3pm Central time zone
Reviewer: Mark J. Bobak
CDC is Change Data Capture. Are you using that feature?
-Mark
rmcdc.sql
January 23, 2006 - 3pm Central time zone
Reviewer: Mark J. Bobak from Ann Arbor, MI
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
January 23, 2006 - 7pm Central time zone
Reviewer: Alex from Mountain View, CA
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.
January 24, 2006 - 10am Central time zone
Reviewer: Alexander the ok
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....)
January 24, 2006 - 11am Central time zone
Reviewer: Alexander the ok
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?
April 20, 2006 - 6pm Central time zone
Reviewer: A reader
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?
April 21, 2006 - 9am Central time zone
Reviewer: Dusan from Czech republic
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.
Followup April 21, 2006 - 9am Central time zone:
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
July 19, 2006 - 3am Central time zone
Reviewer: Frédéric Castelain
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
February 13, 2007 - 2pm Central time zone
Reviewer: Kiran Ghanta from Richmond, VA
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
February 23, 2007 - 8pm Central time zone
Reviewer: A reader
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?
Followup February 26, 2007 - 12pm Central time zone:
metalink has many notes covering that.
tkprof is not used on them, no
Deadlock within SAME session???
March 27, 2007 - 5pm Central time zone
Reviewer: Kim Anthonisen from Denmark
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
===================================================
Followup March 28, 2007 - 11am Central time zone:
do you use autonomous transactions.
Strange deadlock
August 11, 2007 - 5am Central time zone
Reviewer: Jacky from CHINA
---------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
August 11, 2007 - 5am Central time zone
Reviewer: Jacky
*** 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
November 2, 2007 - 6am Central time zone
Reviewer: Vinu from Bangalore India
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
November 13, 2007 - 10pm Central time zone
Reviewer: A reader
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)
March 31, 2008 - 10am Central time zone
Reviewer: Daniel from BELGIUM
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.
Followup March 31, 2008 - 12pm Central time zone:
... 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
August 9, 2008 - 10am Central time zone
Reviewer: Kv from INDIA
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
August 14, 2008 - 12pm Central time zone
Reviewer: Kv from INDIA
Hi Tom,
Can you please offer some advice o the above.
Thanks
Followup August 18, 2008 - 10am Central time zone:
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.
February 13, 2009 - 1am Central time zone
Reviewer: Siva from India
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'
Followup February 16, 2009 - 10am Central time zone:
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
June 29, 2009 - 3pm Central time zone
Reviewer: Anil from USA
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_NOVA
LUE]: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_NOVA
LUE]:0x2 < 0x10001
No Indormation in trace file? May I know why?
regards
Anil
Followup July 6, 2009 - 6pm Central time zone:
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"?
|