A reader, September 23, 2011 - 12:37 pm UTC
Thanks for your help. I will create indexes on foreign keys.
Index the FK
Enrique Aviles, September 23, 2011 - 2:45 pm UTC
Reader,
Notice Tom said to index THE foreign key, not to index all foreign keys. Your reply seems to imply you will index all FKs which might not be necessary if your application doesn't delete or update all parent tables.
deadlock
Jack, October 07, 2011 - 10:16 am UTC
Deadlock trace file copied below
ORACLE_HOME = /opt/oracle/server/10.2.0
System name: SunOS
Release: 5.10
Version: Generic_141445-09
Machine: i86pc
Redo thread mounted by this instance: 1
Oracle process number: 334
*** 2011-10-07 09:38:24.798
*** ACTION NAME:() 2011-10-07 09:38:24.697
*** MODULE NAME:(JDBC Thin Client) 2011-10-07 09:38:24.697
*** SERVICE NAME:(SYS$USERS) 2011-10-07 09:38:24.697
*** SESSION ID:(297.9717) 2011-10-07 09:38:24.697
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-00ad001d-0001c355 334 297 X 148 1040 S
TX-00840046-000296b8 148 1040 X 334 297 S
session 297: DID 0001-014E-000000FA session 1040: DID 0001-0094-00000173
session 1040: DID 0001-0094-00000173 session 297: DID 0001-014E-000000FA
Rows waited on:
Session 1040: obj - rowid = 00064D4D - AAGsZqABaAAHAvVAAA
(dictionary objn - 413005, file - 90, block - 1838037, slot - 0)
Session 297: obj - rowid = 00064D4D - AAGsZqABRAAGSJ1AAA
(dictionary objn - 413005, file - 81, block - 1647221, slot - 0)
Information on the OTHER waiting sessions:
Session 1040:
pid=148 serial=12927 audsid=133451942 user: 48/anad
O/S info: user: jboss, term: unknown, ospid: 1234, machine: app1-live-arch1
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
insert into ARC_OUT_MESSAGE (MESSAGE, PRIORITY, PAYLOAD, SENDER, STATUS_ID, APPLICATION_INSTANCE_ID, CONSUMER_ID, PARTNER_ID, ROUTE_ID, DESTINATION, MESSAGE_TYPE_ID, TARIFF_ID, CREATED_TIME, SENT_TIME, OUT_MESSAGE_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
insert into ARC_OUT_MESSAGE (MESSAGE, PRIORITY, PAYLOAD, SENDER, STATUS_ID, APPLICATION_INSTANCE_ID, CONSUMER_ID, PARTNER_ID, ROUTE_ID, DESTINATION, MESSAGE_TYPE_ID, TARIFF_ID, CREATED_TIME, SENT_TIME, OUT_MESSAGE_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)
===================================================
PROCESS STATE
-------------
Process global information:
process: 16d70290b0, call: 16ddb84cb0, xact: 16dd4a37b8, curses: 16d7245590, usrses: 16d7245590
----------------------------------------
SO: 16d70290b0, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=334, calls cur/top: 16ddb84cb0/16ddb84cb0, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
----
Tom - Any idea what is causing the deadlock here during insert. Object 00064D4D is a non-unique btree index on the table. There is no bitmap index on the table, but there is a unique index on this table. Table is a partitioned table.
thanks
Jack
October 07, 2011 - 2:13 pm UTC
any chance this is happening in your logic?
set echo on
drop table t;
create table t ( x int primary key, y int );
create index t_idx on t(y);
insert into t values ( 1, 1 );
set echo off
prompt in another session issue:
prompt insert into t values ( 2, 2 );;
pause
prompt in another session issue:
prompt insert into t values ( 1, 1 );;
insert into t values ( 2, 2 );
deadlock
JACK, October 07, 2011 - 8:37 pm UTC
Object 00064D4D is a non-unique
btree index on the table. Why is this Bree index shown on the deadlock graph istead of the unique primary key ?
October 08, 2011 - 6:54 am UTC
give me a full up schema to reproduce with
and ... answer what I've asked ;)
deadlock
Jack, October 08, 2011 - 8:43 am UTC
It is happening with inserts like you mentioned but the deadlock graph is misleading.
October 09, 2011 - 11:07 am UTC
well, when I see two inserts like that - and the existence of a unique constraint - that is my very very very very first thought. I've seen it perhaps a million times before.
DEADLOCK DETECTED
A reader, July 23, 2012 - 7:28 pm UTC
Hi Tom, I'm encountering the following error:
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-00230010-0003bc15 192 780 X 152 765 X
TX-002b0011-00012d60 152 765 X 192 780 X
session 780: DID 0001-00C0-0000003B session 765: DID 0001-0098-000001B8
session 765: DID 0001-0098-000001B8 session 780: DID 0001-00C0-0000003B
Rows waited on:
Session 780: obj - rowid = 0000389F - AAADifAF1AAAPixAAK
(dictionary objn - 14495, file - 373, block - 63665, slot - 10)
Session 765: obj - rowid = 0000389F - AAADifAF1AAAPixAAP
(dictionary objn - 14495, file - 373, block - 63665, slot - 15)
----- Information for the OTHER waiting sessions -----
Session 765:
sid: 765 ser: 33 audsid: 58369041 user: 58/APPS flags: 0x8000041
pid: 152 O/S info: user: oracle, term: UNKNOWN, ospid: 9395
image: oracle@odprdabc
client details:
O/S info: user: applmgr, term: , ospid: 15708
machine: oaprdabc program: rwrun@oaprdabc (TNS V1-V3)
client info: 541
application name: APXIIMPT, hash value=2529902159
action name: Concurrent Request, hash value=1021472160
current SQL:
UPDATE PO_LINE_LOCATIONS_ALL PLL SET QUANTITY_BILLED = DECODE(:B11 , NULL, QUANTITY_BILLED , NVL(QUANTITY_BILLED, 0) + :B11 ), AMOUNT_BILLED = DECODE(:B10 , NULL, AMOUNT_BILLED , NVL(AMOUNT_BILLED, 0) + :B10 ), QUANTITY_FINANCED = DECODE(:B9 , NULL, QUANTITY_FINANCED , NVL(QUANTITY_FINANCED, 0) + :B9 ), AMOUNT_FINANCED = DECODE(:B8 , NULL, AMOUNT_FINANCED , NVL(AMOUNT_FINANCED, 0) + :B8 ), QUANTITY_RECOUPED = DECODE(:B7 , NULL, QUANTITY_RECOUPED , NVL(QUANTITY_RECOUPED, 0) + :B7 ), AMOUNT_RECOUPED = DECODE(:B6 , NULL, AMOUNT_RECOUPED , NVL(AMOUNT_RECOUPED, 0) + :B6 ), RETAINAGE_WITHHELD_AMOUNT = DECODE(:B5 , NULL, RETAINAGE_WITHHELD_AMOUNT , NVL(RETAINAGE_WITHHELD_AMOUNT, 0) + :B5 ), RETAINAGE_RELEASED_AMOUNT = DECODE(:B4 , NULL, RETAINAGE_RELEASED_AMOUNT , NVL(RETAINAGE_RELEASED_AMOUNT,
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=b3ccn206ys2w1) -----
UPDATE PO_LINE_LOCATIONS_ALL PLL SET QUANTITY_BILLED = DECODE(:B11 , NULL, QUANTITY_BILLED , NVL(QUANTITY_BILLED, 0) + :B11 ), AMOUNT_BILLED = DECODE(:B10 , NULL, AMOUNT_BILLED , NVL(AMOUNT_BILLED, 0) + :B10 ), QUANTITY_FINANCED = DECODE(:B9 , NULL, QUANTITY_FINANCED , NVL(QUANTITY_FINANCED, 0) + :B9 ), AMOUNT_FINANCED = DECODE(:B8 , NULL, AMOUNT_FINANCED , NVL(AMOUNT_FINANCED, 0) + :B8 ), QUANTITY_RECOUPED = DECODE(:B7 , NULL, QUANTITY_RECOUPED , NVL(QUANTITY_RECOUPED, 0) + :B7 ), AMOUNT_RECOUPED = DECODE(:B6 , NULL, AMOUNT_RECOUPED , NVL(AMOUNT_RECOUPED, 0) + :B6 ), RETAINAGE_WITHHELD_AMOUNT = DECODE(:B5 , NULL, RETAINAGE_WITHHELD_AMOUNT , NVL(RETAINAGE_WITHHELD_AMOUNT, 0) + :B5 ), RETAINAGE_RELEASED_AMOUNT = DECODE(:B4 , NULL, RETAINAGE_RELEASED_AMOUNT , NVL(RETAINAGE_RELEASED_AMOUNT, 0) + :B4 ), LAST_UPDATE_LOGIN = NVL(:B3 , LAST_UPDATE_LOGIN), REQUEST_ID = NVL(:B2 , REQUEST_ID) WHERE PLL.LINE_LOCATION_ID = :B1 OR (PLL.SHIPMENT_TYPE = 'PLANNED' AND PLL.LINE_LOCATION_ID = (SELECT PLL2.SOURCE_SHIPMENT_ID FROM PO_LINE_LOCATIONS PLL2 WHERE PLL2.SHIPMENT_TYPE = 'SCHEDULED' AND PLL2.LINE_LOCATION_ID = :B1 ) )
The error occured when i run a specific custom report and it never happened before also we run the report while gather schema are running.
does gather schema might causing a deadlock ??
if it isn't because of gather schema
what should i do to troubleshoot it ??
Thx
Hendra
July 30, 2012 - 9:07 am UTC
does gather schema might causing a deadlock ??
no, it would not.
do these two updates touch the same row or rows?
Deadlock and TM locks
GPU, August 14, 2013 - 3:09 pm UTC
Hello Tom,
We are using
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
We are getting deadlock error very often in production due to below design.
TableB is child table of TableA
When insert/update/delete happens on TableB we need to sum the amount(amt) and then update it in TableA.total_amt column. Please use below script to replicate the deadlock scenario
drop table tableA;
drop table tableB;
drop package global_pkg;
create table tableA (pk_id number primary key, total_amt number);
create table tableB (pk_id number primary key, fk_id number references tableA(pk_id) not null, amt number);
CREATE OR REPLACE PACKAGE global_pkg
IS
fk_id tableA.pk_id%TYPE;
END global_pkg;
CREATE OR REPLACE TRIGGER tableB_ROW_TRG
BEFORE INSERT OR UPDATE OR DELETE
ON tableB
FOR EACH ROW
BEGIN
IF INSERTING OR UPDATING
THEN
global_pkg.fk_id := :new.fk_id;
ELSE
global_pkg.fk_id := :old.fk_id;
END IF;
END tableB_ROW_TRG;
CREATE OR REPLACE TRIGGER tableB_ST_trg
AFTER INSERT OR UPDATE OR DELETE
ON tableB
BEGIN
IF UPDATING OR INSERTING
THEN
UPDATE tableA
SET total_amt =
(SELECT SUM (amt)
FROM tableB
WHERE fk_id = global_pkg.fk_id)
WHERE pk_id = global_pkg.fk_id;
ELSE
UPDATE tableA
SET total_amt =
(SELECT SUM (amt)
FROM tableB
WHERE fk_id = global_pkg.fk_id)
WHERE pk_id = global_pkg.fk_id;
END IF;
END tableB_ST_trg;
insert into tableA values (1, 0);
insert into tableA values (2, 0);
insert into tableB values (123, 1, 100);
insert into tableB values (456,1, 200);
insert into tableB values (789, 1, 100);
insert into tableB values (1011, 2, 50);
insert into tableB values (1213,2, 150);
insert into tableB values (1415, 2, 50);
Commit;
select * from tableA;
pk_id total_amt
------ ------------
1 400
2 250
delete tableB where pk_id = 1415;
-- Check the locks -- TM lock on tableA
SELECT sid,
(SELECT username
FROM v$session s
WHERE s.sid = v$lock.sid)
uname,
TYPE,
id1,
id2,
(SELECT object_name
FROM user_objects
WHERE object_id = v$lock.id1)
nm
FROM v$lock
WHERE sid IN (SELECT sid
FROM v$session
WHERE username IN (USER));
-- Connect to another session run below statement
delete tableB where pk_id = 1213;
-- You can see the blocking sessions
SELECT (SELECT username
FROM v$session
WHERE sid = a.sid)
blocker,
a.sid,
' is blocking ',
(SELECT username
FROM v$session
WHERE sid = b.sid)
blockee,
b.sid
FROM v$lock a, v$lock b
WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;
-- Execute below statement in session1
delete tableB where pk_id = 1213;
-- Oracle is throwing deadlock error as below in session2
[Error] Execution (3: 1): ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TABLEB_ST_TRG", line 11
ORA-04088: error during execution of trigger 'TABLEB_ST_TRG'
Few times in production we are facing deadlock errors due to above scenarios.
What is causing this issue? I learnt from your book TM locks are to prevent structural change(DDL- TM lock on TableA) but I am not sure why session2 is getting block from session1 when both are trying to delete independent rows in TableB.
My point is I know this is bad design but my client required that tableA.total_amt column updated in real time whenever someone do DML operations on tableB. Do we have any solution other than triggers?
I also proposed to have a job(Oracle job which runs every ten minutes) to do summation in near realtime to update the tableA but users are not ready to accept it.
Please advise
Thanks,
GPU
August 14, 2013 - 3:50 pm UTC
man, do I hate triggers or what:
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html you do know that this implementation is limited to single row inserts, updates and deletes. if ANYONE or ANYTHING ever does an insert/update/delete that touches more than one row - your data is screwed up right? in other works, this implementation is totally wrong.
why are you using a trigger at all? encapsulate your transactional logic in stored procedures and do it *straight forward*. Just right in the code, no automagic - FLAWED - logic.
and why do you care if it throws a deadlock - you obviously have lost update conditions in your code. If two sessions are simultaneously trying to delete the same exact row - well, umm, your logic there is questionable AT BEST.
and why doesn't your code detect and handle errors it considers recoverable?
in short, the first delete where pk_id = 1415 locks that row in tableB and locks the row in tableA where a.pk_id = 2 (your after trigger does that, it updated that row in A)
then, the other session deletes where pk_id = 1213 in tableB. thus it locks the row for pk_id = 1213 in tableB and in the after trigger it gets blocked trying to update tableA. so session1 is blocking it on the update - but IT HAS tableb.pk_id = 1213 LOCKED - it is in the process of deleting that record.
then session 1 tries to delete the row session 2 is trying to process (SERIOUSLY??? how can you let that happen, what sort of logic do you have whereby two sessions *blindly* try to modify the same data - without any sort of coordination????). It gets blocked trying to lock tableb.pk_id = 1213.
we then notice that session 1 is waiting on session 2 and session 2 is waiting on session 1.
bamm - deadlock.
In any case - we can at least correct one of your issues - that of data inconsistency. I don't know why you went with a package + two triggers - just one would have done it:
ops$tkyte%ORA11GR2> create table tableA (pk_id number primary key, total_amt number);
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table tableB (pk_id number primary key, fk_id number references tableA(pk_id) not null, amt number);
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE OR REPLACE TRIGGER tableB_ROW_TRG
2 BEFORE INSERT OR UPDATE OR DELETE
3 ON tableB
4 FOR EACH ROW
5 BEGIN
6 IF INSERTING OR UPDATING
7 THEN
8 update tablea set total_amt = total_amt + nvl(:new.amt,0) where pk_id = :new.fk_id;
9 end if;
10
11 if updating or deleting
12 then
13 update tablea set total_amt = total_amt - nvl(:old.amt,0) where pk_id = :old.fk_id;
14 end if;
15 END tableB_ROW_TRG;
16 /
Trigger created.
ops$tkyte%ORA11GR2> insert into tableA values (1, 0);
1 row created.
ops$tkyte%ORA11GR2> insert into tableA values (2, 0);
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into tableB values (123, 1, 100);
1 row created.
ops$tkyte%ORA11GR2> insert into tableB values (456,1, 200);
1 row created.
ops$tkyte%ORA11GR2> insert into tableB values (789, 1, 100);
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into tableB values (1011, 2, 50);
1 row created.
ops$tkyte%ORA11GR2> insert into tableB values (1213,2, 150);
1 row created.
ops$tkyte%ORA11GR2> insert into tableB select 1415, 2, 50 from dual;
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> Commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from tablea;
PK_ID TOTAL_AMT
---------- ----------
1 400
2 250
ops$tkyte%ORA11GR2> update tableb set amt = amt + 1;
6 rows updated.
ops$tkyte%ORA11GR2> select * from tablea;
PK_ID TOTAL_AMT
---------- ----------
1 403
2 253
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> delete from tableb;
6 rows deleted.
ops$tkyte%ORA11GR2> select * from tablea;
PK_ID TOTAL_AMT
---------- ----------
1 0
2 0
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
ops$tkyte%ORA11GR2> select * from tablea;
PK_ID TOTAL_AMT
---------- ----------
1 403
2 253
but you'll STILL have deadlocks in your code unless and until you fix your "lost update" bug.
if you have access to expert oracle database architecture (a book i wrote), I have a long write up of what lost updates are and how to avoid them. If not, google it up, it is one of the most basic issues developers have to think about when designing their transactions.
there is no way two sessions should be trying to delete the same record from tableB without some sort of coordination.
At the very least, each session should be doing a select for update on the row they are trying to delete. If they can get it - they can delete it. If no data is returned - then they know that the row they were trying to delete was deleted by someone else while they were not looking and - bamm - they should probably rollback and restart (just like they would if they caught the deadlock!!!!!!)
delete stuck even foreign key index is present
abhishek, August 23, 2013 - 6:23 am UTC
hi tom,
i am a big fan of yours.your posts has always helped me. this is my first post on your blog :). pleasee guide.
scenario:
1.) table A (child table)
2.) table B (parent table)
3.) performing delete.
in my scenario i have an index on the foreign key.
the index is not even in the UNUSABLE state.
the order of columns is also correct.
the sequence i am following is as follows:
1.)delete from the child table.
2.)then delete from the parent table.
but the delete from parent table is getting stuck indefinately even the index on foreign key is present and is USABLE.
it only works when i REBUILD the foreign key index on child table.
question:
is stucking of query because of delete performed on the child table and the corresponding index(or its stats) not being updated accordingly?
please put some light on this
August 28, 2013 - 6:19 pm UTC
give the code for this, don't make me generate create table statements and so on.
tell me step by step and give me the step by step code
Reproducing a Deadlock
Tonatiuh, August 25, 2013 - 4:01 pm UTC
Using the information of the Deadlock Trace file, is it possible to reproduce the same Deadlock?
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
UL-40085693-00000000 321 1091 X 407 730 X
TM-000120a1-00000000 407 730 SX 75 3892 S
TM-000120a1-00000000 75 3892 S 321 1091 SX
session 1091: DID 0001-0115-00000604 session 730: DID 0001-00E1-00000D14
session 730: DID 0001-00E1-00000D14 session 3892: DID 0001-003B-00004455
session 3892: DID 0001-003B-00004455 session 1091: DID 0001-0115-00000604
Rows waited on:
Session 1091: obj - rowid = 000120a1 - AAAAAAAAAAAAAAAAAA
(dictionary objn - 73889, file - 0, block - 0, slot - 0)
Session 730: no row
Session 3892: obj - rowid = 000120a1 - AAAAAAAAAAAAAAAAAA
(dictionary objn - 73889, file - 0, block - 0, slot - 0)
----- Information for the OTHER waiting sessions -----
Session 730:
sid: 730 ser: 13917 audsid: 2348098 user: 307/JTESH
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 407 O/S info: user: norjob, term: UNKNOWN, ospid: 17832
image: oracle@custdb01 (TNS V1-V3)
client details:
O/S info: user: norjob, term: , ospid: 17831
machine: custdb01 program: MRUAFL@custdb01 (TNS V1-V3)
application name: MRUAFL, hash value=3576485517
action name: MRU_DEFAULT, hash value=2415775335
current SQL:
begin SFKFEES . P_MRUINTJOB ( :in_term , :stud , TO_DATE ( :assesment , G$_DATE . GET_NLS_DATE_FORMAT ) , TO_DATE ( :refund , G$_DATE . GET_NLS_DATE_FORMAT ) , :rule , :create_ind , :name , :commit , :date , 'N' , :return_status:Ind_01 ) ; END ;
Session 3892:
sid: 3892 ser: 43240 audsid: 3123548 user: 1208/EHARP
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 75 O/S info: user: oracle, term: UNKNOWN, ospid: 22734
image: oracle@custdb01
client details:
O/S info: user: oracle, term: , ospid: 89023
machine: custinb01 program: frmweb@custinb01 (TNS V1-V3)
application name: MRUEMM, hash value=2551670113
action name: MRU_DEFAULT, hash value=2715222117
current SQL:
DELETE FROM RZBEVA WHERE ( RZBEVA_PIDM , RZBEVA_TERM_CODE , RZBEVA_SRCE_CODE , RZBEVA_ACTIVITY_DATE , RZBEVA_AMOUNT , RZBEVA_CROSSREF_PIDM , RZBEVA_CROSSREF_NUMBER , NVL(RZBEVA_CROSSREF_DETAIL_CODE , '""""' ) ) IN (SELECT RZBEVA_CROSSREF_PIDM , :b1 , 'C' , TO_DATE (:b2 , '' || G$_DATE.GET_NLS_DATE_FORMAT || 'HH24:MI:SS' ) , RZBEVA_AMOUNT , :b3 , RZBEVA_CROSSREF_NUMBER , NVL(RZBEVA_CROSSREF_DETAIL_CODE , '""""' ) FROM RZBEVA WHERE RZBEVA_SRCE_CODE = 'C' AND RZBEVA_TRAN_NUMBER > :b4 AND RZBEVA_TERM_CODE = :b1 AND RZBEVA_PIDM = :b3 )
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=bpva86cw27mgh) -----
INSERT INTO RZBEVA VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 ,:B48 ,:B49 ,:B50 ,:B51 ,:B52 ,:B53 ,:B54 ,:B55 ,:B56 ,:B57 ,:B58 ,:B75 ) RETURNING ROWID INTO :O0
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
23bc321d0 89 package body CGMS.DML_RZBEVA
25ce3c701 1211 package body CGMS.YUJUMON
August 28, 2013 - 6:46 pm UTC
No, because you don't have the preceding statements that led up to the deadlock here. You just have the two deadlocked statements.
using your knowledge of the flow of the application - you should be able to reproduce though. I see some user defined locks in there (dbms_lock) as well as normal transaction locks.
Deadlock on MERGE
A reader, October 16, 2013 - 8:01 am UTC
I'm getting deadlock doing a MERGE into table from a global temporary table.
create table tbl(col_a varchar2(10), col_b number, col_c date);
create unique index tbl_uk on tbl (nls_upper(col_a), col_b);
No child tables exist (so no unindexed foreign keys), no bitmap indexes, no autonomous transaction.
Just a MERGE, with either an UPDATE to COL_C (non-unique, not indexed), or INSERT into the tbl.
merge into tbl
using (select /*+ no_eliminate_oby */
distinct tt.col_b
from tt
-- order by 1
) tt
on ( nls_upper(col_a) = :b1
and tbl.col_b = tt.col_b
)
when matched then update set col_c = sysdate
when not matched then
insert (col_a,col_b,col_c)
(:b1, tt.col_b, sysdate);
I'm a bit stuck on ideas and have been thinking it may be a bug with the MERGE, so have tried an ORDER by with a HINT (commented out above), but still getting deadlock.
We're on 11.2.0.3.
Could you please advise if there are any other causes of deadlocks?
November 01, 2013 - 6:57 pm UTC
who or what are you deadlocking with and what are they doing when you deadlock with them
ANY modification can deadlock, you sort of need to understand what was going on in the rest of the world when they did in order to comment on "why"
Deadlock on same session
Jana, January 15, 2014 - 4:22 pm UTC
I went through your explanation on deadlocks and they seem to have problem with 2 sessions involved in deadlock. But my situation is same session is blocking and waiting which is wierd.
A procedure is called to insert the data and I have given the procedure code below. The SQL involved in this session is a insert statement to a GLOBAL temporary table. I understand that GTT is session specific and how could a deadlock happen in that.
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0004001b-00148e18 10 194 X 10 194 S
session 194: DID 0001-000A-0064290E session 194: DID 0001-000A-0064290E
Rows waited on:
Session 194: no row
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
BEGIN
select 1 into v_cnt
from dual
where exists (select 1 from stgmgr.global_stg_loc_hierarchy);
exception
when no_data_found then
INSERT INTO STGMGR.GLOBAL_STG_LOC_HIERARCHY ( LEVEL_1_ID, LEVEL_1_CODE, LEVEL_1_DESC, LEVEL_1_LABEL_ID, LEVEL_1_LABEL_DESC, LEVEL_2_ID, LEVEL_2_CODE, LEVEL_2_DESC, LEVEL_2_LABEL_ID, LEVEL_2_LABEL_DESC, LEVEL_3_ID, LEVEL_3_CODE, LEVEL_3_DESC, LEVEL_3_LABEL_ID, LEVEL_3_LABEL_DESC, LEVEL_4_ID, LEVEL_4_CODE, LEVEL_4_DESC, LEVEL_4_LABEL_ID, LEVEL_4_LABEL_DESC, LEVEL_5_ID, LEVEL_5_CODE, LEVEL_5_DESC, LEVEL_5_LABEL_ID, LEVEL_5_LABEL_DESC, LEVEL_6_ID, LEVEL_6_CODE, LEVEL_6_DESC, LEVEL_6_LABEL_ID, LEVEL_6_LABEL_DESC, LEVEL_7_ID, LEVEL_7_CODE, LEVEL_7_DESC, LEVEL_7_LABEL_ID, LEVEL_7_LABEL_DESC, LEVEL_8_ID, LEVEL_8_CODE, LEVEL_8_DESC, LEVEL_8_LABEL_ID, LEVEL_8_LABEL_DESC, LEVEL_9_ID, LEVEL_9_CODE, LEVEL_9_DESC, LEVEL_9_LABEL_ID, LEVEL_9_LABEL_DESC, LEVEL_10_ID, LEVEL_10_CODE, LEVEL_10_DESC, LEVEL_10_LABEL_ID, LEVEL_10_LABEL_DESC, LOC_TYPE_ID, LOC_TYPE_CODE, LOC_TYPE_DESC, LOC_DISTR_TYPE_ID, LOC_DISTR_TYPE_CODE, LOC_DISTR_TYPE_DESC, HOME_CURRENCY, INTL_LOC, FISCAL_CAL_ID, FISCAL_CAL_DESC, FIN_ACCTNG_MTHS_4_4_5, RECORD_FLAG, DELETE_FLAG, FORECAST_MONTH, HISTORICAL_MONTHS, GL_ENTITY, BUSINESS_GROUP, BUSINESS_GROUP_DESC, DIVISION, DIVISION_DESC, AREA, AREA_DESC, SUB_AREA, SUB_AREA_DESC, REGION, REGION_DESC, SUB_REGION, SUB_REGION_DESC, INTL_DOMESTIC, INTL_DOMESTIC_DESC, MFG_DISTRIBUTION, MFG_DISTRIBUTION_DESC, LOCATION_TYPE, LOCATION_TYPE_DESC, AFFILIATE, AFFILIATE_DESC, SUB_AFFILIATE, SUB_AFFILIATE_DESC, LOC, LOC_DESC, OTHER, OTHER_DESC, CREATED_BY, CREATION_DATE, LAST_UPD_BY, LAST_UPD_DATE, DATAMART_LAST_UPD_BY, DATAMART_LAST_UPD_DATE, LOH_ADJUSTMENT, WH_LEVEL_PLNG_FLAG, SENDING_SCHD_SHIP_FLAG, ALLOW_ICB_METHOD_FOR_INTRANSIT, MAJOR_SOURCE_SYSTEM, REPORTING_AFFILIATE_DEFAULT, GL_SALES_DIVISION_DEFAULT, GL_INV_PERFORMANCE_DEFAULT, GL_SALES_PERFORMANCE_DEFAULT, INVOICE_LOCATION_DEFAULT, ALTER_PLANT_DEFAULT, WIP_COST_METHOD , INV_RPTG_CURRENCY ) (SELECT LEVEL_1_ID, LEVEL_1_CODE, LEVEL_1_DESC, LEVEL_1_LABEL_ID, LEVEL_1_LABEL_DESC, LEVEL_2_ID, LEVEL_2_CODE, LEVEL_2_DESC, LEVEL_2_LABEL_ID, LEVEL_2_LABEL_DESC, LEVEL_3_ID, LEVEL_3_CODE, LEVEL_3_DESC, LEVEL_3_LABEL_ID, LEVEL_3_LABEL_DESC, LEVEL_4_ID, LEVEL_4_CODE, LEVEL_4_DESC, LEVEL_4_LABEL_ID, LEVEL_4_LABEL_DESC, LEVEL_5_ID, LEVEL_5_CODE, LEVEL_5_DESC, LEVEL_5_LABEL_ID, LEVEL_5_LABEL_DESC, LEVEL_6_ID, LEVEL_6_CODE, LEVEL_6_DESC, LEVEL_6_LABEL_ID, LEVEL_6_LABEL_DESC, LEVEL_7_ID, LEVEL_7_CODE, LEVEL_7_DESC, LEVEL_7_LABEL_ID, LEVEL_7_LABEL_DESC, LEVEL_8_ID, LEVEL_8_CODE, LEVEL_8_DESC, LEVEL_8_LABEL_ID, LEVEL_8_LABEL_DESC, LEVEL_9_ID, LEVEL_9_CODE, LEVEL_9_DESC, LEVEL_9_LABEL_ID, LEVEL_9_LABEL_DESC, LEVEL_10_ID, LEVEL_10_CODE, LEVEL_10_DESC, LEVEL_10_LABEL_ID, LEVEL_10_LABEL_DESC, LOC_TYPE_ID, LOC_TYPE_CODE, LOC_TYPE_DESC, LOC_DISTR_TYPE_ID, LOC_DISTR_TYPE_CODE, LOC_DISTR_TYPE_DESC, HOME_CURRENCY, INTL_LOC, FISCAL_CAL_ID, FISCAL_CAL_DESC, FIN_ACCTNG_MTHS_4_4_5, RECORD_FLAG, DELETE_FLAG, FORECAST_MONTH, HISTORICAL_MONTHS, GL_ENTITY, BUSINESS_GROUP, BUSINESS_GROUP_DESC, DIVISION, DIVISION_DESC, AREA, AREA_DESC, SUB_AREA, SUB_AREA_DESC, REGION, REGION_DESC, SUB_REGION, SUB_REGION_DESC, INTL_DOMESTIC, INTL_DOMESTIC_DESC, MFG_DISTRIBUTION, MFG_DISTRIBUTION_DESC, LOCATION_TYPE, LOCATION_TYPE_DESC, AFFILIATE, AFFILIATE_DESC, SUB_AFFILIATE, SUB_AFFILIATE_DESC, LOC, LOC_DESC, OTHER, OTHER_DESC, CREATED_BY, CREATION_DATE, LAST_UPD_BY, LAST_UPD_DATE, DATAMART_LAST_UPD_BY, DATAMART_LAST_UPD_DATE, LOH_ADJUSTMENT, WH_LEVEL_PLNG_FLAG, SENDING_SCHD_SHIP_FLAG, ALLOW_ICB_METHOD_FOR_INTRANSIT, MAJOR_SOURCE_SYSTEM, REPORTING_AFFILIATE_DEFAULT, GL_SALES_DIVISION_DEFAULT, GL_INV_PERFORMANCE_DEFAULT, GL_SALES_PERFORMANCE_DEFAULT, INVOICE_LOCATION_DEFAULT, ALTER_PLANT_DEFAULT, WIP_COST_METHOD , INV_RPTG_CURRENCY FROM STGMGR.STG_LOC_HIERARCHY);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(substr('Error while populating GLOBAL_STG_LOC_HIERARCHY '||SQLCODE||'-'||SQLERRM,1,250));
RAISE;
end;
Table definition has all these columns with "ON COMMIT PRESERVE ROWS NOCACHE" and with the below index
CREATE UNIQUE INDEX GLOBAL_STG_LOC_HIERARCHY_PK ON GLOBAL_STG_LOC_HIERARCHY
(DIVISION, LOC);
Eagerly expecting your response on this. Thanks.
January 15, 2014 - 9:12 pm UTC
very easy to recreate, somewhere you are likely using an autonomous transaction, for example:
ops$tkyte%ORA11GR2> create global temporary table gtt ( x int ) on commit preserve rows;
Table created.
ops$tkyte%ORA11GR2> create unique index t_idx on gtt(x);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into gtt values ( 1 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 begin
4 insert into gtt values ( 1 );
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
...
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0d95001c-0000e5de 20 68 X 20 68 S
session 68: DID 0001-0014-0000708A session 68: DID 0001-0014-0000708A
Rows waited on:
Session 68: no row
.....
deadlock from update
taj, May 01, 2014 - 3:49 pm UTC
Tom, this is my first post. Can you give direction to where we should look to resolve the deadlock issue. its happenning frequently. Is it a inittran issue or application design issue.
trace file contents
*** 2014-04-24 17:00:10.191
*** SESSION ID:(833.14399) 2014-04-24 17:00:10.191
*** CLIENT ID:(THURUMA:1755612465949901) 2014-04-24 17:00:10.191
*** SERVICE NAME:(DG_SALEPROD) 2014-04-24 17:00:10.191
*** MODULE NAME:(SFA/APEX:APP 200) 2014-04-24 17:00:10.191
*** ACTION NAME:(PAGE 600) 2014-04-24 17:00:10.191
*** 2014-04-24 17:00:10.192
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-00140014-003d915f 475 833 X 523 827 X
TX-00170005-002d31cb 523 827 X 475 833 X
session 833: DID 0001-01DB-002D3499 session 827: DID 0001-020B-001BDCE6
session 827: DID 0001-020B-001BDCE6 session 833: DID 0001-01DB-002D3499
Rows waited on:
Session 833: obj - rowid = 00033888 - AAAziIAACAAA6WjAAI
(dictionary objn - 211080, file - 2, block - 239011, slot - 8)
Session 827: obj - rowid = 00033888 - AAAziIAACAAA6WjAAN
(dictionary objn - 211080, file - 2, block - 239011, slot - 13)
----- Information for the OTHER waiting sessions -----
Session 827:
sid: 827 ser: 47882 audsid: 15037563 user: 613/<none>
flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 523 O/S info: user: oracle, term: UNKNOWN, ospid: 16602
image: oracle@smprd001
client details:
O/S info: user: tomcat, term: unknown, ospid: 1234
machine: prjbs001 program: APEX Listener
client info: THURUMA:734305062768486
application name: SFA/APEX:APP 200, hash value=4168807911
action name: Processes - point: BEFORE_BOX_BODY, hash value=1685858885
current SQL:
UPDATE WWV_FLOW_DATA SET ITEM_VALUE = :B6 || ':' || :B5 || ':' || :B4 || ':' || :B3 WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=70rnf8na61nju) -----
DELETE FROM WWV_FLOW_DATA WHERE FLOW_INSTANCE = :B1 AND ITEM_ID IN (SELECT ID FROM WWV_FLOW_PAGE_PLUGS WHERE FLOW_ID = :B3 AND PAGE_ID = :B2 AND PLUG_SOURCE_TYPE IN ( 'SIMPLE_CHART', 'UPDATABLE_SQL
_QUERY', 'DBMSSQL_CURSOR', 'FUNCTION_RETURNING_DBMSSQL_CURSOR', 'FUNCTION_RETURNING_SQL_QUERY', 'STRUCTURED_QUERY', 'SQL_QUERY', 'DYNAMIC_QUERY'))
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x11f827b58 417 package body APEX_040100.WWV_FLOW_DISP_PAGE_PLUGS
0x11bd04990 7620 package body APEX_040100.WWV_FLOW
0x119da19e8 249 procedure APEX_040100.F
0x14b569d78 2 anonymous block
===================================================
PROCESS STATE
-------------
Process global information:
process: 0x3aa03dda0, call: 0x3a927a840, xact: 0x3a7f6b080, curses: 0x3aa52f8c0, usrses: 0x3aa52f8c0
in_exception_handler: no
----------------------------------------
SO: 0x3aa03dda0, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x3aa03dda0, name=process, file=ksu.h LINE:12451, pg=1
(process) Oracle pid:475, ser:73, calls cur/top: 0x3a927a840/0x3a91e7538
flags : (0x0) -
flags2: (0x0), flags3: (0x0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 136 0 2
last post received-location: ksl2.h LINE:2293 ID:kslpsr
last process to post me: 3aa003120 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:282 ID:ksasnd
last process posted by me: 3aa003120 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x728f8530
O/S info: user: oracle, term: UNKNOWN, ospid: 1197
OSD pid info: Unix process pid: 1197, image: oracle@smprd001
answer was Partial
somasundaram, September 07, 2017 - 2:23 pm UTC
same thing occur to me ... in your it does not talk about Rows waited on: kindly give me exact example..