when multiple users are viewing for resource locked by each other .. but this is only in case of updates or deleletes.. and does not apply to selects . Isnt it ?( because you never lock rows for selects).

Tom,

The deadlock topic from your book was very helpful
We have a vb application that many users use to update records from a database. I've noticed deadlocks and found that a few users are involved in that. I think they are opening the deadlocks are occuring when two simultaneous users have screens open without comitting. Is that because the application didn't use "select for no update"? Is it an application issue OR should the users be advised to commit promptly? Please suggest

Tom,

Some more information regarding above post

I ran the query (from your site). All the tables are fine except one table with unindexed fk. The deadlocks are producing trace files. However, that table is not involved in the deadlocks. There are only three tables (that have fks indexed) and 6-7 users involved and I believe that's from the vb screens.

Tom,

I forgot to mention, there are db links between two databases on two machines, the unindexed fk is on machine B which has 3 synonyms to machine A. The deadlocks are occuring on machine A and the tables are not related to the table in B.

Tom,

Can you please explain how Oracle cleans up multiple-way deadlocks. Does it detects the deadlocks in two-way and kill one process at a time to clean up multiple-way deadlocks or there is some other algoritm used to clear up multiple-way deadlocks.

Regards

Thanks Tom,

That means multiple way deadlocks are resolved by killing the transactions by oracle as if deadlocks are between two transactions until all the deadlocks are cleared.

Am I correct?

Regards

If my app has deadlocks often, do you know what that usually means? Are their certain poor practices that are common that lead to an application deadlocking a lot?

Hi Tom,

I have a couple of questions on the recurring deadlocks I'm seeing. In the trace file, it says

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE Approved SET Status = :1,
Version = 23,
TimeUpdated = to_date ('2006-10-25 13:37:33', 'YYYY-MM-DD HH24:MI:SS')
WHERE sysId = 'jjones.25' AND Version = 22
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.

1) How does it know who caused the deadlock? Can I trust this, does this mean it's in the application code?

2) If the values in the statement show up as literals in the trace file does that mean the developers are not using binds?

Thanks!

Tom,

Do you have a query laying around to query the shared pool to determine which statements are not using binds that does not require ddl changes so I can run it on production?

So far I found this on metalink (while looking up the ORA-4031's we're getting)

SQL> SELECT SUBSTR(sql_text, 1, 40) "SQL",
  2  COUNT(*),
  3  SUM(executions) "TotExecs"
  4  FROM v$sqlarea
  5  WHERE executions < 5
  6  GROUP BY SUBSTR(sql_text, 1, 40) HAVING COUNT(*) > 30
  7  ORDER BY 2
  8  /

SQL                                        COUNT(*)   TotExecs
---------------------------------------- ---------- ----------
DELETE FROM NTH_FLUX_RUNTIME_DATA_MAP WH         32         32
SELECT DISTINCT BGROUP FROM hyp.BRIOSECG         37         46
SELECT DISTINCT SRCDB, SRCOWNER, SRCTBL,         38         47
SELECT A.* FROM NTH_AR_ORG_UNITS A WHERE         50         95
SELECT DISTINCT A.* FROM NTH_VIEW_CONTRO        104        175
SELECT * FROM NTH_USERS WHERE USER_ID IN        197        406
SELECT * FROM NTH_STEP_INSTANCE_ISSUES W        211        362
 SELECT "NTH_RPT_GLOBAL_CONTROLS"."FREQU        255        514

8 rows selected.

The soft parse % is 93, which you are probably going to tell me is bad. 

version: oracle 9206 rac on Solaris 5.9

Memory: 16GB
CPU: 8CPU

Dear Tom:

We're encountering many of "Global Enqueue Services Deadlock detected"
messages in the alert log of one of our database instances (two node
RAC database) together with quite big global cache cr request waits in
STATSPACK report. The problem is that the only other available
information is trace file that looks like:

*** 2006-12-24 14:32:08.153
Global Wait-For-Graph(WFG) at ddTS[0.0] :
BLOCKED 70000016f956fd8 5 [0x2b90011][0x5f20],[TX] [131321,1285] 1
BLOCKER 70000016b5ff288 5 [0x2b90011][0x5f20],[TX] [131317,1159] 1
BLOCKED 70000016b1c8440 5 [0x3fe001d][0x1a1d],[TX] [131317,1159] 1
BLOCKER 700000199574ea0 5 [0x3fe001d][0x1a1d],[TX] [131321,1285] 1

Yi Liu

Tom,

I'm seeing deadlocking behavior that surprised me, and I want to get your opinion of whether or not I should expect to receive an ORA-00060 error in this case. All non-row contention deadlocking scenarios aside (e.g. ITL contention), should I expect to see deadlocking occur when two UPDATE or SELECT FOR UPDATE statements execute simultaneously, simply as a result of the query execution plan being used?

I've frequently encountered situations where two sessions issue record locking (UPDATE or SELECT FOR UPDATE) statements against a table simultaneously, and these statements repeatedly and predictably result in a deadlock. The deadlock seems to be due to the query execution plan Oracle selected for servicing the statements. These are the ONLY statements issued during each respective session.

The trace files generated seem to indicate that the deadlock occurs on row lock contention. I tried this on 8i and 10g, with real-world and clean room scenarios, and the same pattern appears repeatedly.

DEADLOCK DETECTED
Current SQL statement for this session:
SELECT XXX FROM YYY WHERE ZZZ LIKE 'AAA%' FOR UPDATE

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00440042-0003d104        49     143     X             47      43           X
TX-00430015-0003d192        47      43     X             49     143           X
session 143: DID 0001-0031-00000002 session 43: DID 0001-002F-00000002
session 43: DID 0001-002F-00000002 session 143: DID 0001-0031-00000002
Rows waited on:
Session 43: obj - rowid = 0000CE33 - AAANCHAArAAAAOmAAM
Session 143: obj - rowid = 0000CE31 - AAANCFAApAAACcCAAZ

10g provides the additional information showing the last wait events:

    application name: SQL*Plus, hash value=3669949024
    last wait for 'enq: TX - row lock contention' blocking sess=0x33968B60 seq=4150 wait_time=2999941 seconds since wait started=4
                name|mode=54580006, usn<<16 | slot=10011, sequence=da
    Dumping Session Wait History
     for 'enq: TX - row lock contention' count=1 wait_time=2999941
                name|mode=54580006, usn<<16 | slot=10011, sequence=da
     for 'buffer busy waits' count=1 wait_time=6892
                file#=4, block#=db76, class#=1
     for 'db file sequential read' count=1 wait_time=80046
                file#=4, block#=15b52, blocks=1
     for 'db file sequential read' count=1 wait_time=1344
                file#=4, block#=15b51, blocks=1
     for 'read by other session' count=1 wait_time=62
                file#=4, block#=da92, class#=1
     for 'read by other session' count=1 wait_time=9
                file#=4, block#=da92, class#=1
     for 'db file sequential read' count=1 wait_time=25973
                file#=4, block#=db81, blocks=1
     for 'db file sequential read' count=1 wait_time=15682
                file#=4, block#=da93, blocks=1
     for 'db file sequential read' count=1 wait_time=2183
                file#=4, block#=15b50, blocks=1
     for 'db file sequential read' count=1 wait_time=2421
                file#=4, block#=15b4f, blocks=1

Given a table of reasonable size, I can predictably reproduce this deadlocking by hinting the desired execution plan to use:

-- Session A
select /*+ index_asc (customer) */
*
from customer
where gender = 'M'
for update;

-- Session B
select /*+ index_desc (customer) */
*
from customer
where gender = 'M'
for update;

These two queries lock records in a conflicting order, "colliding" somewhere in the middle of the results. Oracle rolls back one query, logs a 400KB+ trace file, and returns an ORA-00060 error to the application.

The behavior of these queries conflicts with how I would expect Oracle to resolve this situation. In your August 2005 blog series on UPDATE restarts ( http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html ), you mention that "Oracle will silently roll back your update and restart it." That's the behavior that I expected to see: e.g. the statement with the higher SCN would be rolled back by Oracle, yielding to the statement with the lower SCN. Instead, I get the ORA-00060.

So, based these symptoms:

1) Should I expect to see a deadlock in this situation, or do you think it is a symptom of another underlying problem?

2) If I can expect simultaneously executing UPDATE statements to deadlock simply because of conflicting execution plans, what are my options for working around this problem?

The current workaround--a normal SELECT/ORDER BY (not FOR UPDATE) in a cursor loop that subsequently issues "slow-by-slow" SELECT FOR UPDATE statements--avoids the deadlocking issue, albeit in a less-than-desirable implementation.

By the way, kudos to the work that you've put into this site! This is the first question I've had where I haven't found an answer in your archived material. I've learned so much about Oracle from your site and books, and I can't count the number of times that your materials have benefited my understanding and utilization of Oracle. Thank you!!!

- Curtis

Tom,

thanks for al the good hints and help you give us. I found this article very interresing and also quite helpfull for my understanding of Deadlocks. From my understanding now, it is not 100% avaiodable to have a deadlock at all. In my work I somtimes run into deadlocks and it is hard if not almost impossible to remove any reasons for it as I have to work with software from other people I can not always reprogram at will.
But I could chatch the Exception im my process and handle this situation very well. The only problem is, I do not know if the Deadlock really will be escalated to my session or the other. Is there a way how I could control this? Can I tell Oracle smoehow : "If you dedect a deadlock with this session and any other session, please escalate the deadlock to my side"? I searched this site, Internet and books, but did not find such a thing.

regards

Joachim

Hi Tom, Following is good Analogy of deadlock.
Posting here as couldn't locate other related URL for this...

Boss said to secretary: For a week we will go abroad,
so make arrangement.

Secretary make call to Husband: For a week my boss and
I will be going abroad, you look after yourself.

Husband make call to secret lover: My wife is going
abroad for a week, so lets spend the week together.

Secret lover make call to small boy whom she is giving
private tution: I have work for a week, so you need
not come for class.

Small boy make call to his grandfather: Grandpa, for a
week I don't have class 'coz my teacher is busy. Lets
spend the week together.

Grandpa(the 1st boss ;) ) make call to his secretary: This week I am
spending my time with my grandson. We cannot attend
that meeting.

Secretary make call to her husband: This week my boss
has some work, we cancelled our trip.

Husband make call to secret lover: We cannot spend
this week together, my wife has cancelled her trip.

Secret lover make call to small boy whom she is giving
private tution: This week we will have class as usual.

Small boy make call to his grandfather: Grandpa, my
teacher said this week I have to attend class. Sorry I
can't give you company.

Grandpa make call to his secretary: Don't worry this
week we will attend that meeting, so make arrangement .

Tom,
This is a strange question but has me confused so I will ask. Can a session deadlock itself? If so, how? Oracle 10gr2.

Thanks

Hi Tom,
We have an application which is loading data from SIEBEL source system into an Oracle table. This target table has got a BITMAP index. The data loading program when trying to update this target table with multiple parallel sessions, it is throwing Deadlock error.

Now, my question is why BITMAP indexed table falls into Deadlock situation while updating it with parallel multiple sessions.

Thanks,
SomD

ops$tkyte%ORA10GR2> create table emp
  2  as
  3  select *
  4    from scott.emp;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create bitmap index job_idx on emp(job);

Index created.

<b> if you were to look at the raw index, you would find something like this:</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column bits format a30
ops$tkyte%ORA10GR2> clear screen

ops$tkyte%ORA10GR2> with jobs as (select distinct job from emp),
  2       emps as (select job, row_number() over (order by rowid) rn from emp),
  3       cnt  as (select count(*) cnt from emp)
  4  select job,
  5         ltrim(sys_connect_by_path( bit, '-' ),'-') bits
  6    from (
  7  select jobs.job,
  8         case when jobs.job = emps.job then '1' else '0' end bit,
  9             emps.rn
 10    from jobs, emps
 11         )
 12   where level = (select cnt from cnt)
 13   start with rn=1
 14   connect by prior job = job and prior rn = rn-1
 15  /

JOB       BITS
--------- ------------------------------
ANALYST   0-0-0-0-0-0-0-1-0-0-0-0-1-0
CLERK     1-0-0-0-0-0-0-0-0-0-1-1-0-1
MANAGER   0-0-0-1-0-1-1-0-0-0-0-0-0-0
PRESIDENT 0-0-0-0-0-0-0-0-1-0-0-0-0-0
SALESMAN  0-1-1-0-1-0-0-0-0-1-0-0-0-0

<b>on analyze key, pointing to up to 14 rows (all of them), with a 1 when a row exists with that key and a 0 otherwise.

Now, if someone works with this data:</b>

ops$tkyte%ORA10GR2> select ename, job from emp where ename like 'S%';

ENAME      JOB
---------- ---------
SMITH      CLERK
SCOTT      ANALYST

<b>and decides to transfer SCOTT</b>
ops$tkyte%ORA10GR2> update emp set job = 'CLERK' where ename = 'SCOTT';

1 row updated.

<b>that will up two keys in the bitmap index - the ANALYST key (to turn a 1 into a 0) and the CLERK key (to turn a 0 into a 1).

If another session does this;</b>

ops$tkyte%ORA10GR2> declare
  2      pragma autonomous_transaction;
  3  begin
  4      update emp set job = 'ANALYST' where ename = 'SMITH';
  5      commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

<b>it would block (normally, i used an autonomous transaction in a single session to demonstrate with).  It would block until the first transaction commits.  If this second session was in turn holding some locks that the first session wanted - bamm - it would deadlock of course.

Further, a simple INSERT is blocked as well</b>



ops$tkyte%ORA10GR2> declare
  2      pragma autonomous_transaction;
  3  begin
  4      insert into emp(empno,job) values (1234,'CLERK');
  5      commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

<b>the first transaction has the clerk key locked - we cannot add this new row to that key, it would block</b>

ops$tkyte%ORA10GR2> rollback;

Rollback complete.

I recently got a deadlock failure on a "by-hand parallel" process structured as follows (with :p1 and :p2 specifying ranges of hash-values that divide the workload equally), and am at wits-end trying to find a problem (or prove that the code is not the problem).

Basic logic structure that (I believe) highlights all conceivable pertinent points:

CREATE OR REPLACE PROCEDURE RUN_ME_IN_PARALLEL(P1 IN NUMBER, P2 IN NUMBER) IS
BEGIN
FOR L_CURSOR_ROW IN (
SELECT REC_ID, ....
FROM T1
WHERE ORA_HASH(C11||chr(127)||C12) BETWEEN :P1 AND :P2
)
LOOP
DBMS_OUTPUT.PUT_LINE('Processing '||L_CURSOR_ROW.REC_ID);
-- ...
-- COMPLEX PL/SQL + SQL LOGIC CONSTITUTING A L.U.W. FOR A GIVEN
-- ROW. THIS INCLUDES A QUERY INVOLVING TABLE "T3" BUT NO UPDATES
-- TO THAT TABLE (WILL EXPLAIN SIGNIFICANCE OF T3 BELOW).
-- ...
--
IF <COND1> THEN
INSERT INTO T2 (REC_ID, C21,C22,C23,C24) VALUES (myseq.nextval, ....);
END IF;
----
-- This is the only SQL that shows up in deadlock graph:
UPDATE T2
SET C23=VAR1, C21=L_CURSOR_ROW.C11, C22=L_CURSOR_ROW.C12, C24=C23
WHERE REC_ID = L_CURSOR_ROW.REC_ID;
--
IF MOD(REC_COUNT,1000)=0 THEN COMMIT; END IF; -- Not sure if this matters, but its there
--
END LOOP;
COMMIT;
END;


pertinent DDL:

CREATE TABLE T1 (REC_ID NUMBER PRIMARY KEY, C11 VARCHAR2(10), C12 VARCHAR2(10))
/
CREATE TABLE T2 (REC_ID NUMBER PRIMARY KEY, C21 VARCHAR2(10), C22 VARCHAR2(10), C23 VARCHAR2(10), C24 VARCHAR2(10))
/
CREATE TABLE T3 (C31 VARCHAR2(10), C32 VARCHAR2(10), C33 VARCHAR2(10))
/
CREATE TABLE T4 (REC_ID NUMBER, C41 VARCHAR2(10), CHG_DATE DATE)
/
CREATE OR REPLACE TRIGGER T2_TRIG1
after update of C23 ON T2
FOR EACH ROW WHEN ( new.C23 <> 'ERR' )
BEGIN insert into T4 values (:new.REC_ID,:new.C23,sysdate);
end;
/


Some points:
> No tables are IOT.
> No tables have bit-mapped indexes.
> No code (including trigger) uses AUTONOMOUS TRANSACTION.
> Review of inputs of deadlock run confirms that p1 / p2 were always non-overlapping ranges for all 10 concurrent processes, eg. 0-5, 6-9, 10-16, ...
> Grep of log files of ORA-00060 run for 'Processing #####' strings confirms that no REC_ID was processed by more than one job, nor more than once per job.
> Code has run problem-free for months on end, usually running as 10 concurrent programs.


Out of the blue, the job failed with deadlock issue in a test environment. 2 of the 10 jobs failed. I've put both deadlock graphs at the end of this message.

2 questions:

1) The only query that shows up in the deadlock graph is the UPDATE of table T2. If my statements before are accurate (e.g. no BMIs, no ATs, no IOTs, etc), and no two concurrent programs could've possibly been updating the same T2.rec_id, then what could be causing the deadlock? Do I have a case that it isn't my code - or do you see something I could be overlooking (so hard to prove a negative!)

2) Weird anomaly. In the SECOND DEADLOCK GRAPH below, per DBMS_ROWID, the ROWID AAZXSiAO8AAAfccAAA actually maps to no OBJECT_ID in DBA_OBJECTS, but does map to DATA_OBJECT_ID for table T3 in the code above. My code selects from but does absolutely no DML w.r.t. T3, and T3 has no FKs defined on it. Any idea what this reference means?


---------------- START FIRST DEADLOCK GRAPH ------------------------------------------

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0006002d-0031b81c 280 23519 X 136 23904 S
TX-000c0012-002b8074 136 23904 X 91 23951 S
TX-000d0018-002b8fbf 91 23951 X 280 23519 S
session 23519: DID 0001-0118-0000006F session 23904: DID 0001-0088-0000017F
session 23904: DID 0001-0088-0000017F session 23951: DID 0001-005B-00000321
session 23951: DID 0001-005B-00000321 session 23519: DID 0001-0118-0000006F
Rows waited on:
Session 23904: obj - rowid = 00000000 - D/////AJ7AAAHtrAAA
(dictionary objn - 0, file - 635, block - 31595, slot - 0)
Session 23951: obj - rowid = 00000000 - D/////AJ+AAAEgUAAA
(dictionary objn - 0, file - 638, block - 18452, slot - 0)
Session 23519: obj - rowid = 00000000 - D/////ANPAAAJ0CAAA
(dictionary objn - 0, file - 847, block - 40194, slot - 0)
Information on the OTHER waiting sessions:
Session 23904:
pid=136 serial=11327 audsid=197958878 user: 174/APPS
O/S info: user: applmgr, term: , ospid: 2009, machine: chp057c1
program: FASTD01@chp057c1 (TNS V1-V3)
client info: 42 0
application name: MY_APPLICATION, hash value=1551313236
action name: Concurrent Request, hash value=1021472160
Current SQL Statement:

UPDATE T2 SET C23= :B6, C21= :B5 , C22= :B1 , C24= C23 WHERE REC_ID = :B2
Session 23951:
pid=91 serial=26477 audsid=197958872 user: 174/APPS
O/S info: user: applmgr, term: , ospid: 2034, machine: chp057c1
program: FASTD01@chp057c1 (TNS V1-V3)
client info: 42 0
application name: MY_APPLICATION, hash value=1551313236
action name: Concurrent Request, hash value=1021472160
Current SQL Statement:

UPDATE T2 SET C23= :B6, C21= :B5 , C22= :B1 , C24= C23 WHERE REC_ID = :B2
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE T2 SET C23= :B6, C21= :B5 , C22= :B1 , C24= C23 WHERE REC_ID = :B2
---------------- END FIRST DEADLOCK GRAPH ------------------------------------------

---------------- START SECOND DEADLOCK GRAPH ------------------------------------------
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000d0018-002b8fbf 91 23951 X 136 23904 S
TX-000c0012-002b8074 136 23904 X 91 23951 S
session 23951: DID 0001-005B-00000321 session 23904: DID 0001-0088-0000017F
session 23904: DID 0001-0088-0000017F session 23951: DID 0001-005B-00000321
Rows waited on:
Session 23904: obj - rowid = 00656C7A - AAZXSiAO8AAAfccAAA
(dictionary objn - 6646906, file - 956, block - 128796, slot - 0)
Session 23951: obj - rowid = 00000000 - D/////AJ+AAAEgUAAA
(dictionary objn - 0, file - 638, block - 18452, slot - 0)
Information on the OTHER waiting sessions:
Session 23904:
pid=136 serial=11327 audsid=197958878 user: 174/APPS
O/S info: user: applmgr, term: , ospid: 2009, machine: chp057c1
program: FASTD01@chp057c1 (TNS V1-V3)
client info: 42 0
application name: MY_APPLICATION, hash value=1551313236
action name: Concurrent Request, hash value=1021472160
Current SQL Statement:

UPDATE T2 SET C23= :B6, C21= :B5 , C22= :B1 , C24= C23 WHERE REC_ID = :B2
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE T2 SET C23= :B6, C21= :B5 , C22= :B1 , C24= C23 WHERE REC_ID = :B2


---------------- END SECOND DEADLOCK GRAPH ------------------------------------------

Hi Tom,

As I understand, in case of a Deadlock Oracle would Cancel either of the two statements to resolve it. However, I wish to understand on the Decisive Checkpoints that Oracle uses to decide for Cancelling either of the statement.

Thanks.

Greeting!

Database is on 19.11.

Materialized view refresh some times fails with message:

MVRF: Concurrent scheduler exception: ORA-30439: refresh of 'GES.RM_SDR_MAT' failed because of ORA-12008: error in materialized view or zonemap refresh path
ORA-00060: deadlock detected while waiting for resource.

Current SQL in the trace shows "insert into col$(obj#.."


Any suggestions?
1) what's the cause of the deadlocks
2) How to rectify this in the application.

Please the deadlock graph details below-

e========================================================================
Global Wait-For-Graph(WFG) for GES Deadlock ID=[27_2_1]
------------------------------------------------------------------------
Victim : (instance=3, lock=0x209f884f8)
Start (master) Instance : 3
Number of Locks involved : 4
Number of Sessions involved : 2

User session identified by:
{
User Name : oracle
User Machine : p2m-pr-isx-u3.idt.com
OS Terminal Name : UNKNOWN
OS Process ID : 13841
OS Program Name : oracle@p2m-pr-isx-u3.idt.com (J003)
Application Name : DBMS_SCHEDULERnt-02 (TNS V1-V3)com (TNS V1-V3)
Action Name : MV_RF$J_235341_4_V1314ESSIONnces
Current SQL : insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3,evaledition#,unusablebefore#,unusablebeginning#,collid,collintcol#,acdrrescol#, spare7, spare9, spare10)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20,decode(:21,1,null,:21),decode(:22,0,null,:22),decode(:23,0,null,:23),decode(:24,0,null,:24),decode(:25,0,null,:25),decode(:26,0,null,:26),:27,:28, :29)
Session Number : 18
Session Serial Number : 17281
Server Process ORAPID : 352
Server Process OSPID : 13841
Instance : 3
}
waiting for Lock 0x209f884f8 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x5d0001f.0x39479(ext 0x0,0x5)
GES Transaction ID : 160000-0003-00001E9D
}
which is blocked by Lock 0x209d09528 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x5d0001f.0x39479(ext 0x0,0x5)
GES Transaction ID : D6000-0003-0000276A
}
owned by the
User session identified by:
{
User Name : oracle
User Machine : p2m-pr-isx-u3.idt.com
OS Terminal Name : UNKNOWN
OS Process ID : 13839
OS Program Name : oracle@p2m-pr-isx-u3.idt.com (J002)
Application Name : DBMS_SCHEDULERntdatabasentdata.com (TNS V1-V3)
Action Name : MV_RF$J_235341_2IN_JOB_V13slotor
Current SQL : insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3,evaledition#,unusablebefore#,unusablebeginning#,collid,collintcol#,acdrrescol#, spare7, spare9, spare10)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20,decode(:21,1,null,:21),decode(:22,0,null,:22),decode(:23,0,null,:23),decode(:24,0,null,:24),decode(:25,0,null,:25),decode(:26,0,null,:26),:27,:28, :29)
Session Number : 4272
Session Serial Number : 41857
Server Process ORAPID : 214
Server Process OSPID : 13839
Instance : 3
}
waiting for Lock 0x20d0d70e0 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x6ac0013.0x7bf6(ext 0x0,0x5)
GES Transaction ID : D6000-0003-0000276A
}
which is blocked by Lock 0x20d1acf88 (Transaction):
{
Lock Level : KJUSERPR
Resource Name : TX 0x6ac0013.0x7bf6(ext 0x0,0x5)
GES Transaction ID : 160000-0003-00001E9D
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
End of Global WFG for GES Deadlock ID=[27_2_1]
========================================================================

kjddprg: Transaction Deadlock added to the fixed table.
* Cancel deadlock victim lockp 0x209f884f8