Yves -- Thanks for the question regarding "INITRANS Cause of deadlock", version 9.2.0.1.0

Wouldn't have thought to look there   October 21, 2002 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Cool, now I just have to explain to the developers that it WASN'T their code.

 

deadlock   April 28, 2004 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: jack from Columbus, OH USA

Tom,

Ignoring about the bitmap indexes, can INITTRANS be cause of a deadlock for concurrent operations. 
please explain in detail with a example.

Thanks 

In session #1

ops$tkyte@ORA9IR2> create table t1 ( x int ) maxtrans 2;
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( x int ) maxtrans 2;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 2 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 3 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t2 values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 2 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 3 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update t1 set x=x where x=1;
 
1 row updated.
 
ops$tkyte@ORA9IR2> update t2 set x=x where x=1;
 
1 row updated.


Now in session 2

 
       ops$tkyte@ORA9IR2> update t1 set x=x where x=2;
 
       1 row updated.


and then in session 3

               ops$tkyte@ORA9IR2> update t2 set x=x where x=3;
 
               1 row updated.


back to session 2


       ops$tkyte@ORA9IR2> update t2 set x=x where x=2;


that'll block.  T2 has a lock by session 1 and session 3 -- maxtrans (max conccurrent 
transactions) on that block is limited to two, so session 2 must wait


back to session 3


                       ops$tkyte@ORA9IR2> update t1 set x=x where x=3;


that'll block as well.  If the planets have aligned correctly, then session 2 will recieve


ops$tkyte@ORA9IR2> update t2 set x=x where x=2;
update t2 set x=x where x=2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource




 
 

initrans related deadlock or not   July 6, 2004 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Deepak Samant from INDIA

UPDATE BASIC_LOGIN_domain
        set    LD_NUMBER_USING=LD_NUMBER_USING-1
        where  LD_DOMAIN=:b1


you are not doing your own sequence are you??????


I see that this sesssion executed that SQL, the other session is trying to execute this same SQL -- 
probably with the same inputs.

I've a feeling this is much simplier -- you have a classic deadlock -- this session has locked the 
row, blocking all other sessions.  Is it possible that the other session that is blocked has rows 
locked in this table:

UPDATE basic_realm
          set RL_ALLOCATED_SESSIONS = (RL_ALLOCATED_SESSIONS - 1),
              RL_ALLOCATED_BITRATE = (RL_ALLOCATED_BITRATE - :b2),
              RL_ALLOCATED_DYNAMIC = (RL_ALLOCATED_DYNAMIC - 1),
              RL_MOD_DATE = sysdate
          where RL_NAME = :b1


what you should do is write out on a whiteboard the sequence of SQL it took for both sessions to 
get where they are -- then see what you see.  


and if you are doing your own sequence -- good gosh, stop doing that, use a sequence. 

Whether it is due to low initrans or bitmap index   July 6, 2004 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Anto from US

Hi,

Resource Name          process session holds waits  process session holds waits
TX-00050029-0000783b        15      10     X             12      14           S
TX-000a001d-00007466        12      14     X             15      10           S

Based on the type of lock held(as shown above,copied and pasted from the first post), can we 
distinguish whether the problem is due to low initrans/freelists on the table or due to bitmap 
indexes on the table

thanks
Anto 

it has a signature "similar" to the inittrans -- but the first post was due to a bitmap index, not 
initrans.


The poster right above, with all of the X locks, has a traditional "deadlock caused by application 
design" -- not an initrans issue. 

Distinguishing waits due to INITRANS   July 6, 2004 - 4pm Central time zone

Bookmark | Bottom | Top

Reviewer: Mark J. Bobak from Ann Arbor, MI

Note that when you see 'S' mode waits, it's not automatically
ITL slots, and, in fact, if the statement that caught the 
ORA-60 is an INSERT, it's almost certainly NOT an ITL wait.
Oracle will not wait on ITL slots for inserts, it will simply
go to the next block on the freelist.  It's *possible*, but
in my experience unlikely, (I've never seen it), that the 
wait is due to ITL slot on an INDEX block, but it's impossible
for it to be on the table block.  It's far more likely to be
something else, such as the bitmap index problem mentioned
above.

-Mark 

Distinguish bettween waits due to ITL and bitmap index   July 6, 2004 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Thanks Tom and Mark for the quick response.

Mark answered what I was looking for. 

Need help understanding this deadlock   June 2, 2005 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jupiter from LA, USA

Can you help me understand this deadlock graph. 

Questions:
1) Although the deadlock due to insert statement on table t1, the dictionary objn - 12374 is the 
child table which has a LOB.
2) What is causing the deadlock? It does not appear to be initrans (ITL) nor bitmap index.

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
System name:    AIX
*** 2005-05-31 11:26:05.459
*** SERVICE NAME:(SYS$USERS) 2005-05-31 11:26:05.459
*** SESSION ID:(2177.16749) 2005-05-31 11:26:05.459
DEADLOCK DETECTED
Current SQL statement for this session:
insert into t1 (rn, cd, last_mod_by, last_mod_date, major_revision_flag, content_location, 
storage_type, rev_sbid) values (1, 62470,'scott',sysdate,'','','CLOB','SB111755316232091910')
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-00050044-000001ca        53    2177     X             42    2109           S
TX-00040009-00000b58        42    2109     X             53    2177           S
session 2177: DID 0001-0035-000002FB    session 2109: DID 0001-002A-0000017A
session 2109: DID 0001-002A-0000017A    session 2177: DID 0001-0035-000002FB
Rows waited on:
Session 2109: obj - rowid = 00003056 - AAADBWAARAAAAAAAAA
  (dictionary objn - 12374, file - 17, block - 0, slot - 0)
Session 2177: obj - rowid = 00003056 - AAADBWAARAAAAAAAAA
  (dictionary objn - 12374, file - 17, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 2109:
  pid=42 serial=5043 audsid=39901 user: 30/SCOTT
  O/S info: user: oracle, term: unknown, ospid: , machine: server1
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=0
  Current SQL Statement:
  insert into t1 (rn, cd, last_mod_by, last_mod_date, major_revision_flag, content_location, 
storage_type, rev_sbid) values (1, 62470,'scott',sysdate,'','','CLOB','SB111755316234691911')
End of information on OTHER waiting sessions.
===================================================

Thanks in advance for your input


 

definition of t1 is? 

  June 2, 2005 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jupiter from LA, USA

t1 is actually CONTENT_REVISION. There are many tables involved t1-->t2-->t3... and so on. In this 
deadlock.

Parent Table:
CREATE TABLE "CFWIRE"."CONTENT_REVISION"
   (    "CONTENT_ID" NUMBER NOT NULL ENABLE,
        "REVISION_NUMBER" NUMBER NOT NULL ENABLE,
        "LAST_MOD_BY" VARCHAR2(8) NOT NULL ENABLE,
        "LAST_MOD_DATE" DATE NOT NULL ENABLE,
        "MAJOR_REVISION_FLAG" CHAR(1),
        "CONTENT_LOCATION" VARCHAR2(20),
        "STORAGE_TYPE" VARCHAR2(20),
        "REV_SBID" VARCHAR2(32) NOT NULL ENABLE,
         PRIMARY KEY ("CONTENT_ID", "REVISION_NUMBER")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CFWIRE_INDX"  ENABLE,
         FOREIGN KEY ("LAST_MOD_BY")
          REFERENCES "CFWIRE"."CF_USER" ("USER_ID") ENABLE,
         FOREIGN KEY ("CONTENT_ID")
          REFERENCES "CFWIRE"."CONTENT" ("CONTENT_ID") ON DELETE CASCADE ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CFWIRE";

Child table:
  CREATE TABLE "CFWIRE"."CONTENT_DOCUMENT"
   (    "CONTENT_DOC" CLOB,
        "REVISION_NUMBER" NUMBER NOT NULL ENABLE,
        "CONTENT_ID" NUMBER NOT NULL ENABLE,
         PRIMARY KEY ("REVISION_NUMBER", "CONTENT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CFWIRE_INDX"  ENABLE,
         FOREIGN KEY ("CONTENT_ID", "REVISION_NUMBER")
          REFERENCES "CFWIRE"."CONTENT_REVISION" ("CONTENT_ID", "REVISION_NUMBER") ON D
ELETE CASCADE ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CFWIRE"
 LOB ("CONTENT_DOC") STORE AS (
  TABLESPACE "WIRE_ARTLOB" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 20
  NOCACHE
  STORAGE(INITIAL 49152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

Please let me know if you need any additional information.

Thank You. 

well, column names don't match up.

simple question:



insert into t1 (rn, cd, last_mod_by, last_mod_date, major_revision_flag, 
content_location, storage_type, rev_sbid) values (1, 
62470,'scott',sysdate,'','','CLOB','SB111755316232091910')

  insert into t1 (rn, cd, last_mod_by, last_mod_date, major_revision_flag, 
content_location, storage_type, rev_sbid) values (1, 
62470,'scott',sysdate,'','','CLOB','SB111755316234691911')


how sanitized is that, what is the primary key of "t1" 

  June 2, 2005 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: Jupiter from LA, USA

Sorry for the confusion. As I indicated, there are many tables involved with the ref. intergrity, 
but in this particular deadlock, the following tables are involved.

Parent Table:
CREATE TABLE CONTENT_REVISION
   (    "CONTENT_ID" NUMBER NOT NULL ENABLE,
        "REVISION_NUMBER" NUMBER NOT NULL ENABLE,
        "LAST_MOD_BY" VARCHAR2(8) NOT NULL ENABLE,
        "LAST_MOD_DATE" DATE NOT NULL ENABLE,
        "MAJOR_REVISION_FLAG" CHAR(1),
        "CONTENT_LOCATION" VARCHAR2(20),
        "STORAGE_TYPE" VARCHAR2(20),
        "REV_SBID" VARCHAR2(32) NOT NULL ENABLE,
         PRIMARY KEY ("CONTENT_ID", "REVISION_NUMBER")
         USING INDEX ENABLE,
         FOREIGN KEY ("LAST_MOD_BY")
          REFERENCES "CFWIRE"."CF_USER" ("USER_ID") ENABLE,
         FOREIGN KEY ("CONTENT_ID")
          REFERENCES "CFWIRE"."CONTENT" ("CONTENT_ID") ON DELETE CASCADE ENABLE);

Child table:
  CREATE TABLE CONTENT_DOCUMENT
   (    "CONTENT_DOC" CLOB,
        "REVISION_NUMBER" NUMBER NOT NULL ENABLE,
        "CONTENT_ID" NUMBER NOT NULL ENABLE,
         PRIMARY KEY ("REVISION_NUMBER", "CONTENT_ID")
  USING INDEX ENABLE,
         FOREIGN KEY ("CONTENT_ID", "REVISION_NUMBER")
          REFERENCES "CFWIRE"."CONTENT_REVISION" ("CONTENT_ID", "REVISION_NUMBER") 
          ON DELETE CASCADE ENABLE
   ) 
 LOB ("CONTENT_DOC") STORE AS (
   ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 20 NOCACHE);

From the trace file (created due to the deadlock):
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
*** 2005-05-31 11:26:05.459
*** SERVICE NAME:(SYS$USERS) 2005-05-31 11:26:05.459
*** SESSION ID:(2177.16749) 2005-05-31 11:26:05.459
DEADLOCK DETECTED
Current SQL statement for this session:
insert into content_revision (revision_number, content_id, last_mod_by, last_mod_date, 
major_revision_flag, content_location, storage_type, rev_sbid) values (1, 
62470,'carlsn01',sysdate,'','','CLOB','SB111755316232091910')
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-00050044-000001ca        53    2177     X             42    2109           S
TX-00040009-00000b58        42    2109     X             53    2177           S
session 2177: DID 0001-0035-000002FB    session 2109: DID 0001-002A-0000017A
session 2109: DID 0001-002A-0000017A    session 2177: DID 0001-0035-000002FB
Rows waited on:
Session 2109: obj - rowid = 00003056 - AAADBWAARAAAAAAAAA
  (dictionary objn - 12374, file - 17, block - 0, slot - 0)
Session 2177: obj - rowid = 00003056 - AAADBWAARAAAAAAAAA
  (dictionary objn - 12374, file - 17, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 2109:
  pid=42 serial=5043 audsid=39901 user: 30/CFWIRE
  O/S info: user: oracle, term: unknown, ospid: , machine: serv1
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=0
  Current SQL Statement:
  insert into content_revision (revision_number, content_id, last_mod_by, last_mod_date, 
major_revision_flag, content_location, storage_type, rev_sbid) values (1, 
62470,'carlsn01',sysdate,'','','CLOB','SB111755316234691911')
End of information on OTHER waiting sessions.
===================================================

1) Although the deadlock is due to insert statements on the content_revision table, the dictionary 
objn - 12374 is the child table (content_document) which has a LOB. From logminer I found that 
there was an insert to the content_document table with the same value (fk columns) and there were 
two deletes on the content_revision table during this period.

2) What is causing the deadlock? It does not appear to be due to initrans (ITL) nor bitmap index. 

Thank You. 

you do realize you have two threads

a) NOT USING BIND VARIABLES
b) inserting duplicate entries

so, tell us how this (poorly written) application (that won't scale beyond like 2 users) processes 
this transaction and what the child table looks like.  Give us the details.


a) is the worst thing in the world, if you don't fix that, might as well just erase the code now 
and save yourself lots of time. 

Overlapping primary keys....   June 3, 2005 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: Mark J. Bobak from Belleville, MI

Here's the defintion of the CONTENT_REVISION table:
CREATE TABLE CONTENT_REVISION
   (    "CONTENT_ID" NUMBER NOT NULL ENABLE,
        "REVISION_NUMBER" NUMBER NOT NULL ENABLE,
        "LAST_MOD_BY" VARCHAR2(8) NOT NULL ENABLE,
        "LAST_MOD_DATE" DATE NOT NULL ENABLE,
        "MAJOR_REVISION_FLAG" CHAR(1),
        "CONTENT_LOCATION" VARCHAR2(20),
        "STORAGE_TYPE" VARCHAR2(20),
        "REV_SBID" VARCHAR2(32) NOT NULL ENABLE,
         PRIMARY KEY ("CONTENT_ID", "REVISION_NUMBER")
         USING INDEX ENABLE,
         FOREIGN KEY ("LAST_MOD_BY")
          REFERENCES "CFWIRE"."CF_USER" ("USER_ID") ENABLE,
         FOREIGN KEY ("CONTENT_ID")
          REFERENCES "CFWIRE"."CONTENT" ("CONTENT_ID") ON DELETE CASCADE 
ENABLE);

Which tells us the primary key is (content_id, revision_number).

Ok, that's pretty straighforward.  Forget the child table,
it's not relevant to my point.

Now, look at the statement that encountered the deadlock:
insert into content_revision (revision_number, content_id, last_mod_by, 
last_mod_date, major_revision_flag, content_location, storage_type, rev_sbid) 
values (1, 62470,'carlsn01',sysdate,'','','CLOB','SB111755316232091910')

Note that the primary key being inserted here is:
(62740,1) which is the first two columns and values specified
above.

Now, the other statement involved in the deadlock:
  insert into content_revision (revision_number, content_id, last_mod_by, 
last_mod_date, major_revision_flag, content_location, storage_type, rev_sbid) 
values (1, 62470,'carlsn01',sysdate,'','','CLOB','SB111755316234691911')

Same primary key, (62740,1).  So, that's it.  You've got
two sessions overlapping, both trying to insert the same
row, same PK.  I think you need to revisit your application
logic and data flow.

Hope that helps,

-Mark 

(don't forget the bind variable issue, that is the first bug to fix) 

Oh yeah, minor detail of bind variables.....   June 3, 2005 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: Mark j. Bobak from Bellevile, MI

Good point, Tom.  I got so focused on the deadlock,
the lack of bind variables didn't even dawn on me.
(I know there's a forest around here somewhere, but all
these darn trees are in my way!)

(Of course, the lack of bind variables sure made spotting
the overlapping primary keys a lot easier! ;-))

But you're right of course, the lack of bind variables is
a much greater threat to concurrency than any other issues.

-Mark 

(and it is my pet peeve!) 

  June 3, 2005 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Jupiter from LA, USA

Tom/Mark,

1) "a) NOT USING BIND VARIABLES". This may be poor application design/causes excessive parsing due 
to literals,etc. How is this relevant to a deadlock?

2) Two sessions insert the same row (same PK - bad application design, I agree), BUT this does not 
cause a DEADLOCK

session1:
create table t1 ( x int primary key );
create table t2 (y references t1);
insert into t1 values (1);
1 row created.
-- don't commit, go to session2
session2:
insert into t1 values (1);
HANGS due to lock from session1.

3) Still cannot explain why the deadlock occurred?

Thanks again.
 

1) it isn't, it is the worst bug you have though.  the deadlock, not a big deal. 

lack of bind variables -- killer.


2) the one session is blocked on the other session and the deadlock happened as a result of them 
processing the same "primary key" over here.

but without the ddl, without the transcation -- no going further on this.  triggers, other code -- 
all relevant here.

you've got a mess of a transaction and we sort of need to weed it out.  


do you have unindexed foreign keys?
do you have modifications happening to the parent table (deletes from parent, updates to parent 
primary key)? 

Deadlocks on GTTs   October 14, 2005 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: Martin from UK

Hi Tom,

might be an obvious question this, but have you ever heard of a deadlock on a GTT from the same 
session? It's confusing me this, and it's quite intermittent, leading me to believe it may be an 
Oracle bug (?), however, here's the snippet from the trace file :

*** 2005-10-13 14:21:12.390
*** SESSION ID:(59.248) 2005-10-13 14:21:12.360
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM OPS_TEMP WHERE OPS_REFNO = :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-00070012-000090b7        39      59     X             39      59           X
session 59: DID 0001-0027-00000030      session 59: DID 0001-0027-00000030
Rows waited on:
Session 59: obj - rowid = 0001857C - AAQbOJAABAAAbOKAAB
  (dictionary objn - 99708, file - 1, block - 111498, slot - 1)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

Apologies if I've missed anything out here

Thanks 

are you using autonomous transactions?


ops$tkyte@ORA10G> create global temporary table gtt ( x int primary key ) on commit preserve rows;
 
Table created.
 
ops$tkyte@ORA10G> insert into gtt values ( 1 );
 
1 row created.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> update gtt set x = 1;
 
1 row updated.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2          pragma autonomous_transaction;
  3  begin
  4          delete from gtt where x = 1;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 



Current SQL statement for this session:
DELETE FROM GTT WHERE X = 1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x8a233a3c         4  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-0001002a-000050b9        10     272     X             10     272           X
session 272: DID 0001-000A-0000001E session 272: DID 0001-000A-0000001E
Rows waited on:
Session 272: obj - rowid = 00018620 - AAQSUJAABAAASUKAAA
  (dictionary objn - 99872, file - 1, block - 75018, slot - 0)
 

Forest and trees   October 14, 2005 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Martin from UK

You know, sometimes, I surprise myself with the amount of stuff I don't remember at the correct 
time! ;-)

Thanks a lot Tom, it was that.

 

initrans maxtrans, index and table   October 17, 2005 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: pinguman 

Hi

Does initrans and maxtrans have same meaning for both indexes and table?
For example, in your example above where you set maxtrans 2 to the table then update in 3 sessions 
and cause a deadlock, will this happen if your table mas maxtrans 100 but one of indexes has 
maxtrans 2?

 

they have the same meaning - but don't forget, an index is a complex data structure, you'd have to 
ensure all three inserts hit the SAME index block! (eg: an index on a monitomically increasing 
value...)

and in 10g - maxtrans doesn't count anymore, big change! 

Would you agree with the following?   February 27, 2006 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: David Hull from St. Petersburg, FL USA

Our oltp vendor has the following in one of their notes:

"In most cases, the deadlock is caused by a problem in the application. It can be traced back to a 
programming error.

Under certain conditions, a deadlock may also be triggered by Oracle.

This deadlock has the following cause:

If a data record is locked in the database, this lock information is written to the block. The 
space provided in the block header is used for this. The space is defined when the table is created 
with the parameters INITRANS and maxtrans. If a block becomes too full, the database cannot 
allocate any more space to store this block and must wait for memory space in the corresponding 
block. As a result, row level locking can become block level locking.

If some parallel scripts now lock a large number of data records that are in the same block, two or 
more of the scripts may sometimes cause a deadlock, not because they lock the same data record, but 
because no additional memory space can be allocated for the lock.

To find out whether this is a deadlock in Oracle, you need to examine the trace file written by 
Oracle in a lot more detail. The file is usually stored in the /oracle/<SID>/saptrace/usertrace 
directory. In addition, the exact file name/directory can usually be determined from the ORA-00060 
error message in /oracle/<SID>/saptace/background/alert<SID>.log.

Open the file - the 'deadlock graph' appears on the first two pages.

The deadlock graph is as follows:

---------Blocker(s)--------  ---------Waiter(s)-----

Resource Name      process sess. holds waits  process sess. hold waits
TX-00090004-00011000   43   39    X             35     46          S
TX-0006001a-0001397d   35   46    X             43     39          S

Here, the last column that specifies the Waits as type 'S' is important. If an 'X' is displayed 
instead in the graph, it is NOT an Oracle deadlock."

In other words, what they are saying is that if the last column is an 'X', then the issue should be 
resolved in the application code.  If the last column is an 'S' however, the issue is in the 
database.  And the fix, according to them, is to increase initrans to a value such as 20.

I would like your thoughts on this advice.  I always hesitate when I see such blanket statements 
myself, but we have run into this recently, and our developers are all over us to implement this 
"fix" since it comes from the vendor.  Our table in question has initrans 1 and maxtrans 255.  
We're on 9.2.0.6 on Tru64 Unix.  The table definition is as follows:
CREATE TABLE "SAPR3"."HRPY_RGDIR" 
    ("MANDT"   VARCHAR2(3 byte)  DEFAULT '000'      NOT NULL, 
    "PERNR"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL,
    "SEQNR"    VARCHAR2(5 byte)  DEFAULT '00000'    NOT NULL, 
    "ABKRS"    VARCHAR2(2 byte)  DEFAULT ' '        NOT NULL, 
    "FPPER"    VARCHAR2(6 byte)  DEFAULT ' '        NOT NULL, 
    "FPBEG"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "FPEND"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "IABKRS"   VARCHAR2(2 byte)  DEFAULT ' '        NOT NULL, 
    "IPERM"    VARCHAR2(2 byte)  DEFAULT '00'       NOT NULL, 
    "INPER"    VARCHAR2(6 byte)  DEFAULT ' '        NOT NULL, 
    "IPEND"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "JUPER"    VARCHAR2(4 byte)  DEFAULT ' '        NOT NULL, 
    "SRTZA"    VARCHAR2(1 byte)  DEFAULT ' '        NOT NULL, 
    "PAYTY"    VARCHAR2(1 byte)  DEFAULT ' '        NOT NULL, 
    "PAYID"    VARCHAR2(1 byte)  DEFAULT ' '        NOT NULL, 
    "VOID"     VARCHAR2(1 byte)  DEFAULT ' '        NOT NULL, 
    "BONDT"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "RUNDT"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "RUNTM"    VARCHAR2(6 byte)  DEFAULT '000000'   NOT NULL, 
    "PERMO"    VARCHAR2(2 byte)  DEFAULT '00'       NOT NULL, 
    "VOIDD"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "VOIDT"    VARCHAR2(6 byte)  DEFAULT '000000'   NOT NULL, 
    "VOIDR"    VARCHAR2(2 byte)  DEFAULT ' '        NOT NULL, 
    "PAYDT"    VARCHAR2(8 byte)  DEFAULT '00000000' NOT NULL, 
    "INPTY"    VARCHAR2(1 byte)  DEFAULT ' '        NOT NULL, 
    "INPID"    VARCHAR2(1 byte)  DEFAULT ' '        NOT NULL, 
    "OCRSN"    VARCHAR2(4 byte)  DEFAULT ' '        NOT NULL, 
    "INOCR"    VARCHAR2(4 byte)  DEFAULT ' '        NOT NULL, 
    "OCCAT"    VARCHAR2(2 byte)  DEFAULT ' '        NOT NULL, 
    "INOCC"    VARCHAR2(2 byte)  DEFAULT ' '        NOT NULL, 
    "REVERSAL" VARCHAR2(1 byte)  DEFAULT ' '      NOT NULL, 
    "OUTOFSEQ" VARCHAR2(1 byte), 
    "OOSDATE"  VARCHAR2(8 byte), 
    "ARC_GROUP" VARCHAR2(6 byte), 
    "CALCD"    VARCHAR2(1 byte), 
    "MOLGA"    VARCHAR2(2 byte), 
    "PERSON"   VARCHAR2(8 byte), 
    "PERSDATA" VARCHAR2(8 byte), 
    "BRACKET"  VARCHAR2(5 byte))

The trace file shows:
*** 2006-02-22 21:08:47.221
*** SESSION ID:(1078.62117) 2006-02-22 21:08:47.219
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE "HRPY_RGDIR" SET "ABKRS" = :A0 , "FPPER" = :A1 , "FPBEG" = :A2 , "FPEND" = :A3 , "IABKRS" = 
:A4 , "IPERM" = :A5 , "INPER" = :A6 , "IPEND" = :A7 , "JUPER" = :A8 , "SRTZA" = :A9 , "PAYTY" = 
:A10 , "PAYID" = :A11 , "VOID" = :A12 , "BONDT" = :A13 , "RUNDT" = :A14 , "RUNTM" = :A15 , "PERMO" 
= :A16 , "VOIDD" = :A17 , "VOIDT" = :A18 , "VOIDR" = :A19 , "PAYDT" = :A20 , "INPTY" = :A21 , 
"INPID" = :A22 , "OCRSN" = :A23 , "INOCR" = :A24 , "OCCAT" = :A25 , "INOCC" = :A26 , "REVERSAL" = 
:A27 , "OUTOFSEQ" = :A28 , "OOSDATE" = :A29 , "ARC_GROUP" = :A30 , "CALCD" = :A31 , "MOLGA" = :A32 
, "PERSON" = :A33 , "PERSDATA" = :A34 , "BRACKET" = :A35 WHERE "MANDT" = :A36 AND "PERNR" = :A37 
AND "SEQNR" = :A38
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-00380027-001bebd9        48    1078     X           1000    1060           S
TX-00620022-00038a99      1000    1060     X             48    1078           S
session 1078: DID 0001-0030-00000030    session 1060: DID 0001-03E8-00000277
session 1060: DID 0001-03E8-00000277    session 1078: DID 0001-0030-00000030
Rows waited on:
Session 1060: obj - rowid = 0001D617 - AAAdYXAFCAAAAAAAAA
  (dictionary objn - 120343, file - 322, block - 0, slot - 0)
Session 1078: obj - rowid = 0001D617 - AAAdYXAFGAAAAAAAAA
  (dictionary objn - 120343, file - 326, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 1060:
  pid=1000 serial=8060 audsid=10342713 user: 16/SAPR3
  O/S info: user: prdadm, term: , ospid: 10392, machine: alfpa33
            program: dw.sapPRD_D33@alfpa33 (TNS V1-V3)
  client info: 0
  application name: SAPLHRTIM00ABSENCE                      , hash value=3288724333
  action name: 1463, hash value=585735545
  Current SQL Statement:
  
UPDATE "HRPY_RGDIR" SET "ABKRS" = :A0 , "FPPER" = :A1 , "FPBEG" = :A2 , "FPEND" = :A3 , "IABKRS" = 
:A4 , "IPERM" = :A5 , "INPER" = :A6 , "IPEND" = :A7 , "JUPER" = :A8 , "SRTZA" = :A9 , "PAYTY" = 
:A10 , "PAYID" = :A11 , "VOID" = :A12 , "BONDT" = :A13 , "RUNDT" = :A14 , "RUNTM" = :A15 , "PERMO" 
= :A16 , "VOIDD" = :A17 , "VOIDT" = :A18 , "VOIDR" = :A19 , "PAYDT" = :A20 , "INPTY" = :A21 , 
"INPID" = :A22 , "OCRSN" = :A23 , "INOCR" = :A24 , "OCCAT" = :A25 , "INOCC" = :A26 , "REVERSAL" = 
:A27 , "OUTOFSEQ" = :A28 , "OOSDATE" = :A29 , "ARC_GROUP" = :A30 , "CALCD" = :A31 , "MOLGA" = :A32 
, "PERSON" = :A33 , "PERSDATA" = :A34 , "BRACKET" = :A35 WHERE "MANDT" = :A36 AND "PERNR" = :A37 
AND "SEQNR" = :A38
End of information on OTHER waiting sessions.

Thanks very much,
David. 

you can monitor ITL waits in current releases as well - but yes, this can happen. 

Probably not an ITL wait.....   February 27, 2006 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Mark J. Bobak from Ann Arbor, MI

Since the rows waited on *IS* populated, this is almost certainly NOT an ITL wait....unless Oracle 
changed something again....;-)

Question:
Does HRPY_RGDIR have any bitmap indexes?


-Mark 

this is true, I did not read the trace, just the "advice" - ITL waits/deadlocks can happen.

look for bitmaps
unindexed foreign keys
or a true application deadlock 

Not unindexed FKs......   February 28, 2006 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Mark J. Bobak from Belleville, MI

It's not unindexed FKs because unindexed FKs would be waiting and holding a TM enqueue.

I've been thinking about this, cause I'm working on my HotSos presentation on this very subject, 
"How to interpret deadlocks".

It's late, and I'm tired, and all my materials are at the office, but the other guess I'd make, off 
the top of my head, is that the table is an IOT, but, I'd need to check my materials.

The whole point of my paper is that it demonstrates which causes of deadlocks are characterized by 
what info in the trace file, to help you quickly determine the root cause.  The paper will be 
published next week, at the HotSos conference.

Hope that helps,

-Mark 

INITRANS vs MAXTRANS   March 6, 2006 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: Robert from Memphis, USA

Tom,

In your example near the top of this thread, you showed how to easily demo a MAXTRANS deadlock.

But the question posed by the original poster referred to INITRANS not maxtrans.

Would a table with INITRANS=1 MAXTRANS=255 being updated by 20 parallel processes *by rowid*, be 
possible to get a deadlock caused by a low INITRANS?

And if so... Why would INITRANS=1 have any bearing on the problem, since MAXTRANS is set to 255?

Thanks,

Robert.
 

yes, I used maxtrans (obsolete now, example won't work in 10g) to demonstrate what can happen if 
initrans is set low AND we cannot grow the ITL's.  I used maxtrans to make it as if we had run out 
of room to grow up the initrans.


initrans will be 2 at least since 9i (that changed too).


Yes, if you have 20 sessions updating random rows in a table and we cannot grow the ITL's on some 
blocks (because they are full - which the maxtrans was being used to simulate) - you could get an 
ITL deadlock.

The problem would arise because we cannot GROW the ITL's - we don't have space.  So we cannot ever 
get close to the maxtrans of 255 (which would take some 5k of the block!!)

 

INITRANS and ITL growth   March 8, 2006 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: Robert from Memphis, USA

Tom,

(8.1.7.4 and 9.2.0.7)

Do I remember correctly that each ITL slot requires 24 bytes per transaction?
Therefore can I use this to estimate the state of my blocks for a given table? (e.g. to see if 
there may be enough free space to support additional ITL slots/transactions?

Thanks,

Robert. 

23 or 24 - something like that, yes.

but - you have no idea how much space is "free" on a given block, nor how many itl's already exist.


you have table wide "averages" for free space but that is it. 

Thanks!!!   March 9, 2006 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Robert from Memphis, USA.

Armed with this info I think I will be invincible against deadlock problems among our many manual 
parallel (20 or so parallel processes) jobs, both in troubleshooting and design! 

ORA-00060: deadlock detected while waiting for resource   July 27, 2006 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Cherin from India

Problem :
When  more than one user is trying to delete different set of records from the same table below 
error is occurring.

Error:SQL Code:-60-SQL Error:ORA-00060: deadlock detected while waiting for resource

Inside the procedure we are trying to delete the records as following sequence

Delete from ChildTable 1

Delete from ChildTable 2

Delete from Master Table 

In our case while deleting the Master table only we are getting errors when more than one person 
try to access the same procedure.

Error Description

2006-07-03 15:07:31,013 <ERROR> <ExecuteThread: '14' for queue: 'weblogic.kernel.Default'> 
<com.gm.mfd.des.sdc.dao.Estimate234DAO> <Error--Procedure 
Name:DCE_DIE_COMP_INPUT_PKG.tool_comp_var_maint_sp-SQL Error:SQL Code:-60-SQL Error:ORA-00060: 
deadlock detected while waiting for resource-Procedure 
Name:DCE_DIE_COMP_INPUT_PKG.del_tool_comp_sp-Input Parameter:User Id:qz9db3-Tool Id:2357-Automation 
Chart Flag:-Cam Detail Flag:>


The below are the options that we have tried out to solve this problem:

Changes tried out in DB side:

SELECT ... FROM table_name FOR UPDATE. 
Lock table in ROW EXCLUSIVE mode. 
INDEXING all child tables of the Master table (that is being locked). 
Increasing INIT / MIN / MAX TRANS value set for PCTREE. 
Specifying INDEX hint while deleting. 
COMMIT ing after each delete and handling ROLLBACK through code. 
SAVEPOINT checks at each Procedure / Function level to avoid resource utilization 
IMPLICIT cursors to EXPLICIT cursors. 

Changes tried out in Java:

Synchronizing code block having calls to SP. 
Recalling SP when deadlock occurs. 
Recalling SP for a specified number of times from within a counter when deadlock occurs. 
Moving certain part of code from SP to Java. 
Forcing delay using sleep. 



 

do you have unindexed foreign keys? 

  July 27, 2006 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

ORA-00060: deadlock detected while waiting for resource   July 28, 2006 - 9am Central time zone

Bookmark | Bottom | Top

Reviewer: cherin 

Yes, Index created. I Have used the script of yours.
Still we are unable find out the reason. 

does it always reproduce?  if so, set up test case for us. 

double check the indexes please 

INI_TRANS on INDEXES as well?   August 23, 2006 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Robert from Memphis, USA

Tom,

When increasing size of INI_TRANS to solve problems with deadlocks in tables... do we also need to 
increase size of INI_TRANS on any affected INDEXES as well?

Thanks,

Robert 

"it depends" - but "probably"


depends on the scattering of the index key values - lets say your "hot table block" is the "last 
block" in the table (lots of concurrent inserts - going to the "end").

say you have an index on LAST_NAME.  


The index values would presumably be all over the place (allen,kyte,zebrowski), but the inserted 
rows would all be colocated (hot block).

But the index on the presumed surrogate key would be "hot" as well (sequence.nextval) as they are 
all colocated. 

  November 22, 2006 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Is there a way of getting the PL/SQL Call Stack for the "other session"? While we can trace the 
sequence of calls in the session that was rolled back, how do we figure it out for the other 
session?

eg in this log, we know what the steps were: an anonymous block called something in line 986 in 
UBASIC.HGS which in turn called something in line 70 in UBASIC.HGS. Any way of what the other 
session was going through?

*** 2004-07-02 13:43:47.151
*** SESSION ID:(82.1034) 2004-07-02 13:43:47.149
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE basic_realm
          set RL_ALLOCATED_SESSIONS = (RL_ALLOCATED_SESSIONS - 1),
              RL_ALLOCATED_BITRATE = (RL_ALLOCATED_BITRATE - :b2),
              RL_ALLOCATED_DYNAMIC = (RL_ALLOCATED_DYNAMIC - 1),
              RL_MOD_DATE = sysdate
          where RL_NAME = :b1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
39af72f20        70  package body UBASIC.HGS
39af72f20       986  package body UBASIC.HGS
39cbdd4b8         1  anonymous block
 

[Transaction Deadlock]
Current SQL statement for this session:
update t set x = 2 where x = 2
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-00020003-00001565        15     145     X             16     146           X
TX-00040029-00001818        16     146     X             15     145           X
session 145: DID 0001-000F-00000330 session 146: DID 0001-0010-000000CD
session 146: DID 0001-0010-000000CD session 145: DID 0001-000F-00000330
Rows waited on:
Session 146: obj - rowid = 0002079E - AAAgeeAAEAAAAqMAAA
  (dictionary objn - 133022, file - 4, block - 2700, slot - 0)
Session 145: obj - rowid = 0002079E - AAAgeeAAEAAAAqMAAB
  (dictionary objn - 133022, file - 4, block - 2700, slot - 1)
Information on the OTHER waiting sessions:
Session 146:
  pid=16 serial=1623 audsid=5446 user: 208/OPS$TKYTE
  O/S info: user: tkyte, term: pts/4, ospid: 20162, machine: dellpe
            program: sqlplus@dellpe (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
   update t set x = 1 where x = 1
End of information on OTHER waiting sessions.
===================================================
 

Method to determine high DML tables   March 15, 2007 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from Singapore

permanent solution   November 20, 2007 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Goh from Malaysia

Hi Tom,

   What is the best approach to solve the deadlock issue that due to lack of ITLs ? Is it increase 
the Intrans value or redesign the applications if applicable (eg : especially for multistreaming 
batch jobs)? Your advice is highly appreciated. Thanks

Rgds
Goh

Restrict the number of concurrent session   November 22, 2007 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: shgoh41 from Malaysia

Hi Tom,

   Thanks for the advice. Reduce the rows per block can reduce the chance of hitting deadlock might 
not be a permanent solution. Would it be better if we can restrict the number of multistreaming 
jobs that DML on the same table concurrently and increase the number of ITLs to the number of 
concurrent session. Assuming no other online activity during the multistreaming jobs.
   
   If I understand correctly, one session would only occupy one ITL/block. Max 5 ITLs required for 
5 active session updating to the same block. Is it correct ?

   Thanks

Rgds
Goh

Another deadlock solution   November 23, 2007 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Allan F. Barr from Lenexa KS (Suburb of Kansas City)

Back in version 6 we had a problem of intemittent deadlocks due to not having room in the block to 
add a transaction. We had two possible solutions. 1) increase init trans which would have reduced 
the space available in each block or 2) increase the number of free lists. We increased the free 
lists to 7 and had no more problems.

INITRANS Deadlock?   January 2, 2008 - 11pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from India

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-000c005c-00439c73       271     120     X            238     209           S

TX-0003005d-0055a640       238     209     X            271     120           S

session 120: DID 0001-010F-000139BF     session 209: DID 0001-00EE-00029F50

session 209: DID 0001-00EE-00029F50     session 120: DID 0001-010F-000139BF

Rows waited on:

Session 209: obj - rowid = 00003B31 - AACWAiAl8AAAAAAAAA

  (dictionary objn - 15153, file - 2428, block - 0, slot - 0)

Session 120: obj - rowid = 000039D4 - AADP6bAX4AAAAAAAAA

  (dictionary objn - 14804, file - 1528, block - 0, slot - 0)

Information on the OTHER waiting sessions:

Session 209:

  pid=238 serial=18951 audsid=53533263 user: 36/TBMS_VOIP

  O/S info: user: tbmsadm, term: , ospid: 27960, machine: ganges

            program: schedule_bill@ganges (TNS V1-V3)

  application name: schedule_bill@ganges (TNS V1-V3), hash value=0

  Current SQL Statement:

  UPDATE SUBSCRIBER_MASTER SET 
PM_LAST_INCL_BILL=:b1,PM_BILL_COUNT=DECODE(NVL(:b2,'N'),'Y',PM_BILL_COUNT,NVL(PM_BILL_COUNT,0) + 1 
) WHERE PM_SUBS_ACT_NO = :b3

End of information on OTHER waiting sessions.

Current SQL statement for this session:

UPDATE SUBSCRIBER_MASTER SET 
PM_LAST_INCL_BILL=:b1,PM_BILL_COUNT=DECODE(NVL(:b2,'N'),'Y',PM_BILL_COUNT,NVL(PM_BILL_COUNT,0) + 1 
) WHERE PM_SUBS_ACT_NO = :b3

----- PL/SQL Call Stack -----

Rows waited on:

Session 209: obj - rowid = 00003B31 - AACWAiAl8AAAAAAAAA

  (dictionary objn - 15153, file - 2428, block - 0, slot - 0)

Session 120: obj - rowid = 000039D4 - AADP6bAX4AAAAAAAAA

  (dictionary objn - 14804, file - 1528, block - 0, slot - 0)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010001-000007da        20     141     X             16     143           X
TX-00040023-00000862        16     143     X             20     141           S
session 141: DID 0001-0014-00000026 session 143: DID 0001-0010-000000EB
session 143: DID 0001-0010-000000EB session 141: DID 0001-0014-00000026
Rows waited on:
Session 143: obj - rowid = 0000F60C - AAAPYMAAEAAAAn9AAA
  (dictionary objn - 62988, file - 4, block - 2557, slot - 0)
Session 141: no row
Information on the OTHER waiting sessions:
Session 143:
  pid=16 serial=257 audsid=8699 user: 97/OPS$TKYTE
  O/S info: user: tkyte, term: pts/5, ospid: 6601, machine: localhost.localdomain
            program: sqlplus@localhost.localdomain (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  update t2 set y = 'x'
End of information on OTHER waiting sessions.

drop table t;
drop table t2;

create table t2 ( x int, y varchar2(5) );
insert into t2 values ( 1, null );

create table t ( x int primary key, y char(1000) default 'x', z varchar2(4000) ) pctfree 0 
tablespace test_drop;
insert into t values ( 1, 'x', rpad( 'x', 1678, 'x' ) );
insert into t values ( 2, 'x', rpad( 'x', 1679, 'x' ) );
insert into t values ( 3, 'x', rpad( 'x', 1679, 'x' ) );
commit;

select x, dbms_rowid.rowid_block_number(rowid) from t;

update t set y = 'x' where x = 1;
pause
update t2 set y = 'x';

ops$tkyte%ORA10GR2> update t set y = 'x' where x = 2;

1 row updated.

ops$tkyte%ORA10GR2> update t2 set y = 'x';

1 row updated.

ops$tkyte%ORA10GR2> update t set y = 'x' where x = 3;

ITL Waits   February 24, 2008 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: Matt from England

We're running 10gR2 with Automatic Segment Management.

We are seeing a few segments with ITL Waits, as I understand it (from your excellent book) default 
for INITRANS is 2, with MAXTRANS being ignored (ASM) and a value of 255 being used, and if there is 
space available in the block INITRANS can dynamically grow.

How do we know if there is space available for INITRANS to dynamically grow, and will the size of 
INITRANS be reflected in the data dictionary anywhere?

Deadlock due to a committed transaction!!!   February 28, 2008 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Ramesh from India

Hi Tom,
In our application we are getting deadlock error due to a committed transaction.
I have a table CO_JOBS. We insert data from staging table into this table. And then perform many 
updates on the table. Our application contains many PL/SQL scripts which get executed by wrapper 
script. 
In this case:
  wrapper.sh insert.sql
  wrapper.sh update1.sql
  wrapper.sh update2.sql
  wrapper.sh update3.sql
  wrapper.sh update4.sql
  wrapper.sh update5.sql
  wrapper.sh update6.sql

Each sql script commits at the end of the script. Now upadte3.sql,upadte4.sql,update5.sql are done. 
update6.sql is throwing the dead lock error due to session corresponding to update3.sql. I am 
unable to understand how it can happen as update3.sql initiated a transaction and successfully 
closed it. We dont have any bitmap index. CO_JOBS is a range partitioned table with few million 
records per partition. Our db is 8i. INI_TRANS:1 MAX_TRANS:255. Is this problem because of low 
INI_TRANS!? Please advise.

I am copying part of the trace file below:
Dump file /oracle/app/oracle/admin/digte/udump/ora_43398_digte.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
ORACLE_HOME = /oracle/app/oracle/product/8.1.7
System name:    AIX
Node name:    cmps08
Release:    3
Version:    4
Machine:    0008BD5A4C00
Instance name: digte
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 43398, image: oracle@cmps08 (TNS V1-V3)

*** 2008-02-26 11:44:56.971
*** SESSION ID:(18.61822) 2008-02-26 11:44:56.879
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE CO_JOBS SET 
FTTP='Y',MASTER_ORDER_NUMBER=:b1,FTTP_ORDER_NUMBER=:b2,FIBER_RELATED_ORDER_NUMBER=:b3 WHERE RUNDATE 
= :b4  AND AWAS = :b5  AND LMOS_TTN = :b6
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000002dfe26f0        58  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-00080041-0001afc0        16      18     X             18      25           X
TX-000a0049-0001b696        18      25     X             16      18           X
session 18: DID 0001-0010-00000002    session 25: DID 0001-0012-00000002
session 25: DID 0001-0012-00000002    session 18: DID 0001-0010-00000002
Rows waited on:
Session 25: obj - rowid = 0003939E - AAA5OeAB8AADg9VAAF
Session 18: obj - rowid = 0003939E - AAA5OeAB8AADg70AAL
===================================================
PROCESS STATE
-------------
Process global information:
     process: 70000002bc90870, call: 70000002bcf0868, xact: 70000002d2be2f0, curses: 
70000002bcb5868, usrses: 70000002bcb5868
  ----------------------------------------
  SO: 70000002bc90870, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=16, calls cur/top: 70000002bcf0868/70000002d45f710, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 85 0 4
              last post received-location: kslpsr
              last process to post me: 70000002bc8d630 1 2
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=10
      holding     700000000006bf8 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 0x70000002BC8C210 to 0x70000002BC8C220
2BC8C210 00000000 00000000 00000000 00000000  [................]
    Process Group: DEFAULT, pseudo proc: 70000002bca6890
    O/S info: user: digte, term: UNKNOWN, ospid: 43398
    OSD pid info: 43398
    ----------------------------------------
    SO: 70000002bcb5868, type: 3, owner: 70000002bc90870, pt: 0, flag: INIT/-/-/0x00
    (session) trans: 70000002d2be2f0, creator: 70000002bc90870, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-0010-00000002, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 6, prv: 0, user: 19/DIGTE
    O/S info: user: digte, term: , ospid: 58938, machine: cmps08
              program: sqlplus@cmps08 (TNS V1-V3)
    application name: 01@ /apps/digte/daily/sql/upd2fttp_flag.sql, hash value=2391757251
    last wait for 'enqueue' blocking sess=0x2bcb9ae8 seq=468 wait_time=307
                name|mode=54580006, id1=a0049, id2=1b696
      ----------------------------------------
      SO: 70000002d390988, type: 36, owner: 70000002bcb5868, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=70000002d390988 handle=70000002daeadd8 mode=S lock=70000002d38eba8
      user=70000002bcb5868 session=70000002bcb5868 count=1 mask=0041 savepoint=430 flags=[00]
      ----------------------------------------
      SO: 70000002d38eba8, type: 35, owner: 70000002bcb5868, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=70000002d38eba8 handle=70000002daeadd8 mode=N
      call pin=0 session pin=70000002d390988
      user=70000002bcb5868 session=70000002bcb5868 count=1 flags=PNS/[08] savepoint=47
      LIBRARY OBJECT HANDLE: handle=70000002daeadd8
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0041-0041 lock=N pin=S latch=0
      lwt=70000002daeae08[70000002daeae08,70000002daeae08] 
ltm=70000002daeae18[70000002daeae18,70000002daeae18]
      pwt=70000002daeae38[70000002daeae38,70000002daeae38] 
ptm=70000002daeaec8[70000002daeaec8,70000002daeaec8]
      ref=70000002daeade8[700000031f75830,700000031f75830] 
lnd=70000002daeaee0[70000002daeaee0,70000002daeaee0]
        LIBRARY OBJECT: object=70000003166e590
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        DEPENDENCIES: count=1 size=16
        AUTHORIZATIONS: count=1 size=16 minimum entrysize=27
        ACCESSES: count=1 size=16
        TRANSLATIONS: count=1 size=16
        DATA BLOCKS:

MAXTRANS Limit in Oracle 10g R2   August 29, 2008 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Dulal from Bangladesh

Hi Tom,
Thanks in advance for your helps. I have a question about MAXTRANS.
I am going to create a table on Oracle 10g R2 (64 bit), O/S
Redhat Enterprise Linux 4 (64 Bit) for online transactions.
This table will concurrently be use by thousands of user for DML.
So, what will be the INITTRANS and MAXTRANS value?
Please help.

George   September 11, 2008 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Tom,
From the trace file how can one identify if the contention is due to the bitmap index?

Thanks
George

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

Table created.

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

Index created.

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

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

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update emp set job = 'CLERK' where ename = 'SCOTT';

1 row updated.

ops$tkyte%ORA10GR2> @trace

Session altered.

ops$tkyte%ORA10GR2> update emp set job = 'ANALYST' where ename = 'SMITH';

update emp set job = 'ANALYST'
where
 ename = 'SMITH'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00      51.42          0          9         16           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00      51.43          0         10         16           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 414

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  EMP (cr=9 pr=0 pw=0 time=51429175 us)
      1   TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=181 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: TX - row lock contention                  18        2.93         51.41

Deadlocks   October 20, 2008 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from Australia

Hi Tom

I am facing a deadlock issue on my 2 node RAC. 

Extract from the trace file
procp            : 0x727fcc34
pid              : 0
proc version     : 0
oprocp           : (nil)
opid             : 0
group lock owner : (nil)
xid              : 0000-0000-00000000
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : N
lock_state       : GRANTED
Open Options     :  KJUSERNO_XID
Convert options  : KJUSERGETVALUE
History          : 0x77d497ad
Msg_Seq          : 0x1
res_seq          : 14
valblk           : 0x00000000000000000000000000000000 .
user session for deadlock lock 0x72d07578
  pid=152 serial=9821 audsid=17385876 user: 333/<none>
  O/S info: user: SYSTEM, term: HJSPROD02-NEW, ospid: 6296:4904, machine: SWADS\HJSPROD02-NEW
            program: ProcessPoolMgr.EXE
  client info: N07RF_32
  application name: ProcessPoolMgr.EXE, hash value=0
  Current SQL Statement:
  UPDATE T_PICK_DETAIL SET PLANNED_QUANTITY = :B2 WHERE PICK_ID = :B1
Global Wait-For-Graph(WFG) at ddTS[0.60] :
BLOCKED 0x72d0dd7c 5 [0x180022][0x72362],[TX] [24000-0002-0000318B] 1
BLOCKER 0x72bc3bd0 5 [0x180022][0x72362],[TX] [98000-0001-000000E3] 0
BLOCKED 0x72d07578 5 [0x1d001b][0x44920],[TX] [98000-0001-000000E3] 0
BLOCKER 0x72c12428 5 [0x1d001b][0x44920],[TX] [24000-0002-0000318B] 1
*** 2008-10-20 21:01:25.556
* kjdrchkdrm: found an RM request in the request queue
  Transfer pkey 104314 to node 0
*** 2008-10-20 21:01:25.636
Begin DRM(135) - transfer pkey 104314 to 0 oscan 1.1
 ftd received from node 1 (4/0.30.0)
 all ftds received
 syncr inc 4 lvl 4297 from 1 rcvd (my inc,lvl: 4, 4296) (4/0.31.0)
 ftd received from node 1 (4/0.34.0)
 all ftds received
 syncr inc 4 lvl 4298 from 1 rcvd (my inc,lvl: 4, 4297) (4/0.34.0)
 ftd received from node 1 (4/0.36.0)
 all ftds received
 syncr inc 4 lvl 4299 from 1 rcvd (my inc,lvl: 4, 4298) (4/0.36.0)
 ftd received from node 1 (4/0.38.0)
 all ftds received
 syncr inc 4 lvl 4300 from 1 rcvd (my inc,lvl: 4, 4299) (4/0.38.0)
 ftd received from node 1 (4/0.30.0)
 all ftds received
 syncr inc 4 lvl 4301 from 1 rcvd (my inc,lvl: 4, 4300) (4/0.31.0)
 ftd received from node 1 (4/0.34.0)
 all ftds received
 syncr inc 4 lvl 4302 from 1 rcvd (my inc,lvl: 4, 4301) (4/0.34.0)
 ftd received from node 1 (4/0.36.0)
 all ftds received
 syncr inc 4 lvl 4303 from 1 rcvd (my inc,lvl: 4, 4302) (4/0.36.0)
 ftd received from node 1 (4/0.38.0)
 all ftds received

When this happens enq: TX - row lock contention is reported in the Top 5 timed events. 

Would this be related to inittrans parameter set up wrongly?

Deadlock problem with INITTRANS=20   January 5, 2009 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Thanks a lot   April 29, 2009 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader 

Thanks a lot, Thomas. It has resolved my deadlocks problems.