The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question, Yves.

Asked: October 18, 2002 - 2:01 pm UTC

Last updated: January 04, 2013 - 10:40 am UTC

Version: 9.2.0.1.0

Viewed 50K+ times! This question is

You Asked

Hi Tom.

I am getting the following errors in my alert log:

ORA-000060: Deadlock detected. More info in file d:\oracle\admin\vndb\udump\vndb_ora_1620.trc.

The first few lines of the above trace file are as follows:
Dump file d:\oracle\admin\vndb\udump\vndb_ora_1312.trc
Thu Oct 17 20:03:58 2002
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 1, CPU type 586
Instance name: vndb

Redo thread mounted by this instance: 1

Oracle process number: 15

Windows thread id: 1312, image: ORACLE.EXE

*** 2002-10-17 20:03:58.000
*** SESSION ID:(10.1301) 2002-10-17 20:03:58.000
DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO vn_valuation (VALUATION_ID,
GROUP_ID,
TASK_NO,
PRIORITY,
HOST,
REQUEST,
SERVER_NO,
EXECUTION_TIME,
VERSION,
INSTR_ID,
MODEL_ID,
CALC_ID,
MARKET_ID,
EXCEPTION) values (
:1 ,
:2 ,
:3 ,
:4 ,
:5 ,
:6 ,
:7 ,
:8 ,
:9 ,
:10 ,
:11 ,
:12 ,
:13 ,
:14 )
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-00050029-0000783b 15 10 X 12 14 S
TX-000a001d-00007466 12 14 X 15 10 S
session 10: DID 0001-000F-00000002 session 14: DID 0001-000C-00000002
session 14: DID 0001-000C-00000002 session 10: DID 0001-000F-00000002
Rows waited on:
Session 14: no row
Session 10: no row
Information on the OTHER waiting sessions:
Session 14:
pid=12 serial=1012 audsid=7504 user: 68/VNUSER
O/S info: user: vndbap, term: , ospid: 11476, machine: dfin03
program: java@dfin03 (TNS V1-V3)
Current SQL Statement:

INSERT INTO vn_valuation (VALUATION_ID,
GROUP_ID,
TASK_NO,
PRIORITY,
HOST,
REQUEST,
SERVER_NO,
EXECUTION_TIME,
VERSION,
INSTR_ID,
MODEL_ID,
CALC_ID,
MARKET_ID,
EXCEPTION) values (
:1 ,
:2 ,
:3 ,
:4 ,
:5 ,
:6 ,
:7 ,
:8 ,
:9 ,
:10 ,
:11 ,
:12 ,
:13 ,
:14 )
End of information on OTHER waiting sessions.
===================================================

The deadlock seems to occur when I have concurrent threads inserting into the same table. There are no updates involved here at all.

The table in question has 1 primary key which is a sequence number, and one hash index. This table has an initrans of 1 and a maxtrans of 256.
According through Oracle support (through a TAR I have opened), the dealock is probably caused by a the lack of room in the row header.

I have increased initrans to 100, and when I have two concurrent threads running, at some point, one thread goes in sleep state (my own characterization) and only continues when the other thread has completed. I am no longer getting the ora-60 deadlock errors in the alert log.

My questions are: does this all make sense, and what should values of inittrans and maxtrans be based on? I have only simulated two threads, but if there are multiple threads, should initrans be like 1000, and maxtrans be like 2000?

Please advise.

Thanks.

No the table does not have a hash cluster. I meant to say it has a bitmap index on a field that is non allways unique and sometimes may be null. There are no foreign keys on this table.

and Tom said...

it is not initrans then -- it is the bitmap index.

bitmap indexes are not appropriate for tables that have lots of single row DML operations (your inserts) and especially -- i mean ESPECIALLY -- concurrent single row DML operations.

The bitmap index is the cause of your heartburn and is something you cannot afford to have one a table that you feed in the manner you are. You need to get rid of that bitmap index!

Think about how they work:

KEY_VALUE1 row1 row5 row10 row11 row12 row100 row102 ......
KEY_VALUE2 row2 row3 row4 row14 row22 row99 row103 ......

each entry in the bitmap index points to many many many rows.

Suppose thread 1 inserts key_value1. It'll lock that entry in the bitmap index.

Now thread 2 inserts key_value2. It'll lock THAT entry in the bitmap index.

Now, thread 1 inserts key_value2 -- it'll block, waiting for thread 2 to commit.

Now, thread 2 inserts key_value1 -- it'll block -- waiting for thread 1 to commit.

DEADLOCK. Bam -- game over player 1 -- you lose.

Not only that but bitmap indexes deteriorate faster then you can spell Oracle in single row DML situations.

Bitmaps are appropriate in read only/ read mostly environments (data warehouses). Bitmaps should generally be disabled and rebuilt before/after loads into these systems. They are a special purpose index that is not at all appropriate for the type of environment you describe.

Simple test for you:

---------------- test1.sql ---------------------------------
set echo on

drop table t;
create table t ( x varchar2(20) );
insert into t select decode( mod(rownum,2), 0, 'HI', 'BYE' ) from all_objects;

create bitmap index t_idx on t(x);

insert into t values ( 'HI' );
prompt goto another session and @test2.sql
pause

prompt goto the other session and hit enter
insert into t values ( 'BYE' );
----------------------------------------------------------------------

----------------------- test2.sql ------------------------------------
set echo on

insert into t values ( 'BYE' );
prompt goto window1 and hit enter
pause
insert into t values ( 'HI' );
-----------------------------------------------------------------------

results of test1.sql on my system:

ops$tkyte@ORA920.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x varchar2(20) );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t select decode( mod(rownum,2), 0, 'HI', 'BYE' ) from all_objects;

29311 rows created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create bitmap index t_idx on t(x);

Index created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( 'HI' );

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> prompt goto another session and @test2.sql
goto another session and @test2.sql
ops$tkyte@ORA920.US.ORACLE.COM> pause

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> prompt goto the other session and hit enter
goto the other session and hit enter
ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( 'BYE' );
insert into t values ( 'BYE' )
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

you could set initrans to as high as you wanted -- won't solve the issue -- the issue here is the bitmap index!!!

Rating

(43 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Wouldn't have thought to look there

A reader, October 21, 2002 - 10:42 am UTC

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

deadlock

jack, April 28, 2004 - 8:38 pm UTC

Tom,

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

Thanks

April 29, 2004 - 7:09 am UTC

<b>In session #1</b>

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.


<b>Now in session 2</b>

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


<b>and then in session 3</b>

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


<b>back to session 2</b>


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


<b>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</b>


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


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


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

Deepak Samant, July 06, 2004 - 11:17 am UTC

Hi Tom,
we are using a web server which calls a PL/SQL package for order booking.I got a deadlock scenario many of the times & then i found the following trace file.
Could you please look into it & suggest me whether is it a initrans related deadlock or not.If we are testing with 2 or 3 simultaneous sessions, we are not facing any error, but when this is accessed in Live, some times deadlock is occuring.
I'm enclosing some part of the trace file.

Thanks in advance.
Regards,
Deepak

/shd/u01/app/oracle/admin/shd/udump/shd1_ora_10456.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /shd/u01/app/oracle/product/9.2
System name: SunOS
Node name: rioosp02-ukbr
Release: 5.8
Version: Generic_108528-22
Machine: sun4u
Instance name: shd1
Redo thread mounted by this instance: 1
Oracle process number: 79
Unix process pid: 10456, image: oracle@rioosp02-ukbr (TNS V1-V3)

*** 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
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-000a0027-000005ea 79 82 X 81 64 X
TX-0006001f-000005d5 81 64 X 79 82 X
session 82: DID 0001-004F-0002180D session 64: DID 0001-0051-0001F3F6
session 64: DID 0001-0051-0001F3F6 session 82: DID 0001-004F-0002180D
Rows waited on:
Session 64: obj - rowid = 00001DCC - AAAB3MAAGAAAAOKAAO
(dictionary objn - 7628, file - 6, block - 906, slot - 14)
Session 82: obj - rowid = 00001DD3 - AAAB3TAAGAAAAeKAAD
(dictionary objn - 7635, file - 6, block - 1930, slot - 3)
Information on the OTHER waiting sessions:
Session 64:
pid=81 serial=22807 audsid=234677 user: 40/UBASIC
O/S info: user: crpwls, term: unknown, ospid: , machine: PRTWLS21
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=0
Current SQL Statement:
UPDATE BASIC_LOGIN_DOMAIN
set LD_NUMBER_USING = (LD_NUMBER_USING + 1)
where LD_DOMAIN = :b1
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 39546cb10, call: 39739e4a8, xact: 396707d38, curses: 3954ee0a8, usrses: 3954ee0a8
----------------------------------------
SO: 39546cb10, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=79, calls cur/top: 39739e4a8/3975f57c8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 20
last post received-location: ksqrcl
last process to post me: 395479958 101 0
last post sent: 0 0 20
last post sent-location: ksqrcl
last process posted by me: 39546d520 218 0
(latch info) wait_event=0 bits=10
holding 38000a1a8 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 0x0000000395453C70 to 0x0000000395453C80
395453C70 00000000 00000000 00000000 00000000 [................]
Process Group: DEFAULT, pseudo proc: 3954b24c0
O/S info: user: oracle, term: UNKNOWN, ospid: 10456
OSD pid info: Unix process pid: 10456, image: oracle@rioosp02-ukbr (TNS V1-V3)
----------------------------------------
SO: 3954ee0a8, type: 4, owner: 39546cb10, flag: INIT/-/-/0x00
(session) trans: 396707d38, creator: 39546cb10, flag: (8100041) USR/- BSY/-/-/-/-/-
DID: 0001-004F-0002180D, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, sql: 39961b930, psql: 39961b930, user: 40/UBASIC
O/S info: user: crpwls, term: unknown, ospid: , machine: PRTWLS22
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=0
last wait for 'enqueue' blocking sess=0x954e2288 seq=57 wait_time=3002794
name|mode=54580006, id1=6001f, id2=5d5
temporary object counter: 0
----------------------------------------
SO: 397b97a88, type: 52, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=397b97a88 handle=39a3193c8 mode=S lock=397aaaea0
user=3954ee0a8 session=3954ee0a8 count=1 mask=0041 savepoint=123 flags=[00]
----------------------------------------
SO: 397aaaea0, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=397aaaea0 handle=39a3193c8 mode=N
call pin=0 session pin=397b97a88
htl=397aaaf10[3973d65f0,3973d65f0] htb=3973d65f0
user=3954ee0a8 session=3954ee0a8 count=1 flags=PNS/[08] savepoint=123
LIBRARY OBJECT HANDLE: handle=39a3193c8
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=S latch#=5
lwt=39a3193f8[39a3193f8,39a3193f8] ltm=39a319408[39a319408,39a319408]
pwt=39a319428[39a319428,39a319428] ptm=39a3194b8[39a3194b8,39a3194b8]
ref=39a3193d8[39adf13b8, 39adf13b8] lnd=39a3194d0[39a3194d0,39a3194d0]
LIBRARY OBJECT: object=3a0136c00
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=17
ACCESSES: count=1 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 3a0136ee0 3a01369b0 I/P/A 0 NONE
6 3a0136d20 3a2a1d9f0 I/P/A 1 NONE
----------------------------------------
SO: 39799f270, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=39799f270 handle=39961b930 mode=N
call pin=3979999f0 session pin=0
htl=39799f2e0[3973d5e40,3973d5e40] htb=3973d5e40
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=122
LIBRARY OBJECT HANDLE: handle=39961b930
name=
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
hash=b43bd722 timestamp=04-01-2004 22:56:37
namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=5
lwt=39961b960[39961b960,39961b960] ltm=39961b970[39961b970,39961b970]
pwt=39961b990[39961b990,39961b990] ptm=39961ba20[39961ba20,39961ba20]
ref=39961b940[39961b940, 39961b940] lnd=39961ba38[399618ce8,39961c200]
LIBRARY OBJECT: object=39a23a068
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 39ae5ef60 39adf13b8 39a3193c8
1 39ae5ef60 39ae5ed48 39bb8f138
2 39ae5ef60 39ae5edb0 39a2410b8
3 39ae5ef60 39ae5ee18 39f723018
4 39ae5ef60 39ae5ee80 39f91fbc8
5 39ae5ef60 39ae5eee8 39f8ba4d8
6 39ae5ef60 3a0fe7718 3a0c14f70
7 39ae5ef60 3a0fe7780 3a1fba958
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 39a368798 39a23a160 I/P/A 0 NONE
----------------------------------------
SO: 396707d38, type: 38, owner: 3954ee0a8, flag: INIT/-/-/0x00
(trans) bsn = 115, flg = 0x1e03, flg2 = 0x00, prx = 0x0, ros = 2147483647, spn = 128
efd = 14
parent xid: 0x0000.000.00000000
env: (scn: 0x0532.c41d65cc xid: 0x000a.027.000005ea uba: 0x00800088.02b5.03 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0532.c41d65cc 0sch: scn: 0x0000.00000000)
cev: (spc = 7808 arsp = 39679d6d0 ubk tsn: 1 rdba: 0x00800088 useg tsn: 1 rdba: 0x00800099
hwm uba: 0x00800088.02b5.03 col uba: 0x00000000.0000.00
num bl: 1 bk list: 0x3966a1bd0)
(enqueue) TX-000A0027-000005EA DID: 0001-004F-0002180D
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res: 3956abec0, mode: X, prv: 3956abed0, sess: 3954ee0a8, proc: 39546cb10
xga: 0x0, heap: UGA
----------------------------------------
SO: 396656df8, type: 34, owner: 396707d38, flag: INIT/-/-/0x00
DML LOCK: tab=7635 flg=01 chi=0
his[0]: mod=3 spn=125
(enqueue) TM-00001DD3-00000000 DID: 0001-004F-0002180D
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res: 3956c4540, mode: SX, prv: 396656fb0, sess: 3954ee0a8, proc: 39546cb10
----------------------------------------
SO: 3966a1bd0, type: 37, owner: 396707d38, flag: -/-/-/0x00
(List of Blocks) next index = 1
index savepoint itli buffer hint rdba
--------------------------------------------------
0 118 3 0x388ffb500 0x180038a
----------------------------------------
SO: 3966576f8, type: 34, owner: 396707d38, flag: INIT/-/-/0x00
DML LOCK: tab=7628 flg=01 chi=0
his[0]: mod=3 spn=115
(enqueue) TM-00001DCC-00000000 DID: 0001-004F-0002180D
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
res: 3956ba560, mode: SX, prv: 396656ef0, sess: 3954ee0a8, proc: 39546cb10
----------------------------------------
SO: 3974fd8d8, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=3974fd8d8 handle=39c405a78 mode=N
call pin=0 session pin=397503740
htl=3974fd948[3973d61a0,3973d61a0] htb=3973d61a0
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=112
LIBRARY OBJECT HANDLE: handle=39c405a78
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=2
lwt=39c405aa8[39c405aa8,39c405aa8] ltm=39c405ab8[39c405ab8,39c405ab8]
pwt=39c405ad8[39c405ad8,39c405ad8] ptm=39c405b68[39c405b68,39c405b68]
ref=39c405a88[39bbab120, 39bbab120] lnd=39c405b80[39c405b80,39c405b80]
LIBRARY OBJECT: object=3a0138678
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
ACCESSES: count=1 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 3a0138958 3a0138338 I/P/A 0 NONE
6 3a0138798 3a1148810 I/-/A 0 NONE
----------------------------------------
SO: 3973f45b0, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=3973f45b0 handle=399616700 mode=N
call pin=397408b98 session pin=0
htl=3973f4620[3973d6320,3973d6320] htb=3973d6320
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=111
LIBRARY OBJECT HANDLE: handle=399616700
name=UPDATE BASIC_LOGIN_domain
set LD_NUMBER_USING=LD_NUMBER_USING-1
where LD_DOMAIN=:b1
hash=5acf9b70 timestamp=04-01-2004 23:50:16
namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=2
lwt=399616730[399616730,399616730] ltm=399616740[399616740,399616740]
pwt=399616760[399616760,399616760] ptm=3996167f0[3996167f0,3996167f0]
ref=399616710[399616710, 399616710] lnd=399616808[399616570,399616aa0]
LIBRARY OBJECT: object=39bbac0a0
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 39bbab338 39bbabfd0 39bbabbd8
1 39bbab338 39bbab120 39c405a78
2 39bbab338 39bbab188 39c6dfd80
3 39bbab338 39bbab1f0 39f89bd90
4 39bbab338 39bbab258 39f7ac350
5 39bbab338 39bbab2c0 39f783ae0
6 39bbab338 3a0be2038 3a0be1e88
7 39bbab338 3a0be20a0 3a0855850
8 39bbab338 3a0be2108 39f2062e0
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 39bbac380 39bbac198 I/P/A 0 NONE
----------------------------------------
SO: 3974e7a38, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=3974e7a38 handle=39b8bb5c8 mode=N
call pin=0 session pin=3974e3198
htl=3974e7aa8[3973d6940,3973d6940] htb=3973d6940
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=104
LIBRARY OBJECT HANDLE: handle=39b8bb5c8
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=2
lwt=39b8bb5f8[39b8bb5f8,39b8bb5f8] ltm=39b8bb608[39b8bb608,39b8bb608]
pwt=39b8bb628[39b8bb628,39b8bb628] ptm=39b8bb6b8[39b8bb6b8,39b8bb6b8]
ref=39b8bb5d8[397e20228, 397e20228] lnd=39b8bb6d0[39b8bb6d0,39b8bb6d0]
LIBRARY OBJECT: object=3a014ccb8
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=2 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
ACCESSES: count=2 size=16
TRANSLATIONS: count=2 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 3a014cf98 3a014c978 I/P/A 0 NONE
6 3a014cdd8 3a2d27738 I/-/A 0 NONE
----------------------------------------
SO: 3974fe840, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=3974fe840 handle=399616998 mode=N
call pin=3974fc1d8 session pin=0
htl=3974fe8b0[3973d64c0,3973d64c0] htb=3973d64c0
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=103
LIBRARY OBJECT HANDLE: handle=399616998
name=SELECT RL_IN_CLUSTER ,
RL_NAME
from BASIC_REALM
where (RL_NAME)
in
( select LD_IN_REALM
from BASIC_LOGIN_DOMAIN D
where D.LD_DOMAIN=:b1)
hash=53c51d8a timestamp=04-01-2004 22:56:37
namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=2
lwt=3996169c8[3996169c8,3996169c8] ltm=3996169d8[3996169d8,3996169d8]
pwt=3996169f8[3996169f8,3996169f8] ptm=399616a88[399616a88,399616a88]
ref=3996169a8[3996169a8, 3996169a8] lnd=399616aa0[399616808,399617268]
LIBRARY OBJECT: object=39a8ee1b0
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 397e20440 39a9ed690 39a8eee60
1 397e20440 397e20228 39b8bb5c8
2 397e20440 397e20290 39bea12a8
3 397e20440 397e202f8 39f9af1b8
4 397e20440 397e20360 39f9701e8
5 397e20440 397e203c8 39f13f8c8
6 397e20440 3a0b4cc78 3a0b4cac8
7 397e20440 3a0b4cce0 3a0957e98
8 397e20440 3a0b4cd48 3a0268748
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 39a2a3a08 39a8ee2a8 I/P/A 0 NONE
----------------------------------------
SO: 3979fcd68, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=3979fcd68 handle=39b88c5c8 mode=N
call pin=0 session pin=3979d7228
htl=3979fcdd8[3973d6860,3973d6860] htb=3973d6860
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=96
LIBRARY OBJECT HANDLE: handle=39b88c5c8
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=5
lwt=39b88c5f8[39b88c5f8,39b88c5f8] ltm=39b88c608[39b88c608,39b88c608]
pwt=39b88c628[39b88c628,39b88c628] ptm=39b88c6b8[39b88c6b8,39b88c6b8]
ref=39b88c5d8[39bba7d50, 39bba7d50] lnd=39b88c6d0[39b88c6d0,39b88c6d0]
LIBRARY OBJECT: object=3a01f1650
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
ACCESSES: count=1 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 3a01f4c18 3a03c5da0 I/P/A 0 NONE
6 3a01f1770 3a122a778 I/-/A 0 NONE
----------------------------------------
SO: 39787bba0, type: 51, owner: 3954ee0a8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=39787bba0 handle=399616c30 mode=N
call pin=39799a770 session pin=0
htl=39787bc10[3973d60a0,3973d60a0] htb=3973d60a0
user=3954ee0a8 session=3954ee0a8 count=1 flags=[00] savepoint=95
LIBRARY OBJECT HANDLE: handle=399616c30
name=SELECT PROD_BITRATE
from BASIC_PRODUCTS
where PROD_ID=:b2
and PROD_SUBNET_SIZE=:b1

July 06, 2004 - 11:37 am UTC

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

Anto, July 06, 2004 - 3:32 pm UTC

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

July 06, 2004 - 3:56 pm UTC

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

Mark J. Bobak, July 06, 2004 - 4:45 pm UTC

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

A reader, July 06, 2004 - 5:38 pm UTC

Thanks Tom and Mark for the quick response.

Mark answered what I was looking for.

Need help understanding this deadlock

Jupiter, June 02, 2005 - 5:00 pm UTC

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

June 02, 2005 - 6:26 pm UTC

definition of t1 is?

Jupiter, June 02, 2005 - 6:56 pm UTC

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.

June 02, 2005 - 8:16 pm UTC

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"

Jupiter, June 02, 2005 - 8:50 pm UTC

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.

June 03, 2005 - 7:05 am UTC

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

Mark J. Bobak, June 03, 2005 - 4:44 am UTC

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

June 03, 2005 - 7:29 am UTC

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

Oh yeah, minor detail of bind variables.....

Mark j. Bobak, June 03, 2005 - 9:15 am UTC

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

June 03, 2005 - 10:00 am UTC

(and it is my pet peeve!)

Jupiter, June 03, 2005 - 10:26 am UTC

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.

June 03, 2005 - 11:05 am UTC

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

Martin, October 14, 2005 - 3:42 am UTC

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

October 14, 2005 - 7:59 am UTC

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

Martin, October 14, 2005 - 10:25 am UTC

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

pinguman, October 17, 2005 - 1:57 pm UTC

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?

October 17, 2005 - 2:43 pm UTC

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?

David Hull, February 27, 2006 - 2:39 pm UTC

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.

February 27, 2006 - 2:46 pm UTC

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

Probably not an ITL wait.....

Mark J. Bobak, February 27, 2006 - 3:36 pm UTC

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

February 27, 2006 - 4:47 pm UTC

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

Mark J. Bobak, February 28, 2006 - 2:04 am UTC

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

Robert, March 06, 2006 - 1:43 pm UTC

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.

March 08, 2006 - 4:16 pm UTC

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

Robert, March 08, 2006 - 6:05 pm UTC

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.

March 09, 2006 - 12:57 pm UTC

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

Robert, March 09, 2006 - 2:35 pm UTC

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

Cherin, July 27, 2006 - 11:14 am UTC

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.

July 27, 2006 - 12:17 pm UTC

do you have unindexed foreign keys?

A reader, July 27, 2006 - 1:48 pm UTC

ORA-00060: deadlock detected while waiting for resource

cherin, July 28, 2006 - 9:19 am UTC

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

July 28, 2006 - 8:03 pm UTC

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

double check the indexes please

INI_TRANS on INDEXES as well?

Robert, August 23, 2006 - 2:38 pm UTC

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

August 27, 2006 - 5:44 pm UTC

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

A reader, November 22, 2006 - 1:01 am UTC

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

November 24, 2006 - 12:32 pm UTC

[Transaction Deadlock]<b>
Current SQL statement for this session:
update t set x = 2 where x = 2</b>
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)<b>
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.</b>
===================================================

Method to determine high DML tables

A reader, March 15, 2007 - 3:45 am UTC

HI, Tom,

Is there any way to determine the list of tables having high DML operations? My objective is to find out such tables, and set INITRANS to be a higher value, say 10 to avoid ITL blocking.

Thanks

March 15, 2007 - 11:50 am UTC

well, why would "high dml" imply "need more initrans"

maybe you want to query v$segstat where statistic_name = 'ITL waits' to find segments with high ITL waits

drop the "high dml" theory - too many exceptions and holes in it.

permanent solution

Goh, November 20, 2007 - 9:28 pm UTC

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

November 21, 2007 - 11:44 am UTC

If there are no data structures available to spread the workload out

eg: if I had a really hot block due to many rows being on the block, I could just increase pctfree - thereby reducing the number of rows Oracle will put on blocks from now on, hence decreasing the number of people hitting that block....

increasing the number of ITLs initially would work.

you would look for storage related techniques, not necessary application redesigns, first I would think.

Restrict the number of concurrent session

shgoh41, November 22, 2007 - 10:05 am UTC

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

Allan F. Barr, November 23, 2007 - 3:07 pm UTC

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?

A reader, January 02, 2008 - 11:29 pm UTC

Hi Tom,

Could you help me interpret the below deadlock graph?

Deadlock happened for an application running in 3 threads.
we dont see any possibility for row level locking.

Also, deadlock graph shows wait for S mode lock. an UPDATE statement is shown as the waiting SQL. table which is given in the update statment has initrans as 1. and there are 4 parallel sessions.

ITL Waits is 18 in v$segment_statistics for this particular table.

Could this be a problem of INITRANS?

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

January 03, 2008 - 11:30 am UTC

... we dont see any possibility for row level locking. ...

why do you say that? you are waiting on rows here:

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)

(initrans is a minimum of two these days by the way)

Here is the trace of an ITL deadlock:

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.

I received that by executing:

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

tablespace test_drop in my database was manual segment space managed, with an 8k block - those three rows went on a single block (one more byte and they won't)

Now, when that script paused - in session 2 I executed:

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

1 row updated.

In session three I then:

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

1 row updated.

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

that blocked.... session 3 was waiting on session 1 - I went back there and hit enter -both blocked, then one got the deadlock and you are looking at that trace above....

ITL Waits

Matt, February 24, 2008 - 4:18 am UTC

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?

February 24, 2008 - 11:56 am UTC

... MAXTRANS being ignored (ASM) ...

ASM is something else, ASSM is what you meant and maxtrans is maxed out to 255 these days anyway regardless.

You would either

a) set initrans higher - so new blocks permanently allocate more space
b) fiddle with pctfree - so that new inserts are prevented from filling the block up totally - allowing for the transaction header to grow

Deadlock due to a committed transaction!!!

Ramesh, February 28, 2008 - 2:52 am UTC

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:

February 28, 2008 - 3:44 pm UTC

... In our application we are getting deadlock error due to a committed
transaction. ...

umm, that is a impossible condition.

... upadte3.sql,upadte4.sql,update5.sql are done. update6.sql is throwing the dead
lock error due to session corresponding to update3.sql. ...

that means that update3 and update6 are running concurrently, not sequentially.

think about this please - you seem to indicate udpate3 runs and commits, and then update 4 runs and commits and then..... then update6 runs

If that were the case, the session running update3 would not be running udpate3 anymore.

There is something else afoot here, please look at your application logic a bit more closely.

MAXTRANS Limit in Oracle 10g R2

Dulal, August 29, 2008 - 10:06 am UTC

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.

August 30, 2008 - 9:33 am UTC

maxtrans is obsolete, it is always 255 now.

initrans is something you have to think about - that you create this table for online transactions is so very vague. We have no idea the concurrency needs here, the pctfree settings, etc.

probably "the default" is usually sufficient, if you experience high itl waits in test, you can adjust (v$segment_statistics will tell you)

George

A reader, September 11, 2008 - 5:35 am UTC

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

Thanks
George

September 16, 2008 - 12:15 pm UTC

if you have bitmap indexes and concurrent users - you have contention pretty much, you don't even need to look - you just KNOW you do.

Bitmaps and multi-users = oxymoron of the highest degree.

anyway, if you do this:

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.

and in another session do this:

ops$tkyte%ORA10GR2> @trace

Session altered.

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

wait a bit, and then commit the first session, you would see in the tkprof:


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)

<b>
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
</b>

Deadlocks

A reader, October 20, 2008 - 7:53 am UTC

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?

October 21, 2008 - 11:55 am UTC

... O/S info: user: SYSTEM, ...

stop that, do not use system, sys or any account like that, they are ours - you do not use them for your applications.

... Would this be related to inittrans parameter set up wrongly? ...

no, you have row lock contention here, you have typical "we are deadlocking ourself" contention here.

Look at your application logic, do not dive into database parameters or settings here. This will be in the application itself.

Deadlock problem with INITTRANS=20

A reader, January 05, 2009 - 8:06 pm UTC

Hi Tom,
version 8174.
I have a monthly job , that run with 10 process simultaneously.
after few hours of work i find alots of deadlocks in the alert log.
The deadlock alway happened on the same table.

The table have INITRANS=20.
There are no foreign keys on this table.
There are no BITMAP indexes on this table.
The table have unqiue index with INITRANS=20

Bellow are the table and index structure and also part of the trace file.
Do you think its an ITL problem ?

Thanks Alot
Yoav

CREATE TABLE TC_SUBS_DETAIL
(
AREA VARCHAR2(2) NOT NULL,
PHONE VARCHAR2(7) NOT NULL,
HEKER_NO VARCHAR2(8) NOT NULL,
PAYING_CUST_NO NUMBER(9),
BEZEQ_BILL_CYCLE NUMBER(2),
DETAILED_IDD_BILL CHAR(1),
DETAILED_188_BILL CHAR(1),
VAT_PAYMENT CHAR(1),
NO_VET_PAYMENT_REASON VARCHAR2(50),
CHANGE_TO_BEZEQ CHAR(1),
START_DATE DATE NOT NULL,
END_DATE DATE,
UPDATING_USER NUMBER(10),
LAST_INVOICE_DATE DATE,
PREV_INVOICE_DATE DATE,
PREV_END_DATE DATE,
SUBS_STATUS CHAR(1),
SON_FATHER_IND NUMBER(1)
)
TABLESPACE DATA06_TS
PCTUSED 40
PCTFREE 10
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 100M
NEXT 100M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 10
FREELIST GROUPS 3
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

The index structure :
CREATE UNIQUE INDEX BEZEQ.TC_SUBS_DETAIL_I_01 ON BEZEQ.TC_SUBS_DETAIL
(AREA, PHONE, HEKER_NO, START_DATE)
LOGGING
TABLESPACE INDEX05_TS
PCTFREE 10
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 50M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 10
FREELIST GROUPS 3
BUFFER_POOL DEFAULT
)
NOPARALLEL;

DEADLOCK DETECTED
Current SQL statement for this session:

update tc_subs_detail set prev_invoice_date = last_invoice_date,
last_invoice_date = to_date(:1,'yyyymmdd')
where area = :2 and phone = :3 and heker_no = :4
and start_date >= to_date(:5,'yyyymmddhh24miss')

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-000e0025-00587b21 80 175 X 69 85 S
TX-000e002a-00588921 69 85 X 80 175 S
session 175: DID 0001-0050-00000002 session 85: DID 0001-0045-00000002
session 85: DID 0001-0045-00000002 session 175: DID 0001-0050-00000002
Rows waited on:
Session 85: no row
Session 175: no row
===================================================
PROCESS STATE
-------------
Process global information:
process: c0000001681fcb60, call: c000000170e99728, xact: c0000001701f0908, curses: c0000001682d7d40, usrses: c0000001682d7d40
----------------------------------------
SO: c0000001681fcb60, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
(process) Oracle pid=80, calls cur/top: c000000170e99728/c000000170e99728, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 1
last post received-location: kslfre
last process to post me: c0000001681f2c40 100 0
last post sent: 13835058061459737328 0 13
last post sent-location: ksasnd
last process posted by me: c0000001681e9580 1 2
(latch info) wait_event=0 bits=10
holding c0000001928389c0 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 0xC0000001681E6C00 to 0xC0000001681E6C10
681E6C00 00000000 00000000 00000000 00000000 [................]
Process Group: DEFAULT, pseudo proc: c00000016826abe8
O/S info: user: operbill, term: UNKNOWN, ospid: 3048
OSD pid info: 3048
----------------------------------------
SO: c0000001682d7d40, type: 3, owner: c0000001681fcb60, pt: 0, flag: INIT/-/-/0x00
(session) trans: c0000001701f0908, creator: c0000001681fcb60, flag: (8000041) USR/- BSY/-/-/-/-/-
DID: 0001-0050-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 13/BEZEQ
O/S info: user: operbill, term: , ospid: 3035, machine: romeo
program: ? @romeo (TNS V1-V3)
application name: MONTHLY_INV, hash value=2950993020
last wait for 'enqueue' blocking sess=0x682a2640 seq=24595 wait_time=308
name|mode=54580004, id1=e002a, id2=588921
----------------------------------------
SO: c0000001708456c0, type: 35, owner: c0000001682d7d40, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c0000001708456c0 handle=c0000001896e60e0 mode=N
call pin=c0000001706672c0 session pin=0
user=c0000001682d7d40 session=c0000001682d7d40 count=1 flags=[00] savepoint=25702
LIBRARY OBJECT HANDLE: handle=c0000001896e60e0
name=BEZEQ.TC_PAYING_CUST_AFTER_TRG
hash=82d8246a timestamp=11-26-2008 02:03:54
namespace=TRGR flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0059-0059 lock=N pin=0 latch=7
lwt=c0000001896e6110[c0000001896e6110,c0000001896e6110] ltm=c0000001896e6120[c0000001896e6120,c0000001896e6120]
pwt=c0000001896e6140[c0000001896e6140,c0000001896e6140] ptm=c0000001896e61d0[c0000001896e61d0,c0000001896e61d0]
ref=c0000001896e60f0[c0000001896e60f0,c0000001896e60f0] lnd=c0000001896e61e8[c000000189691058,c00000018977f0c8]
LIBRARY OBJECT: object=c0000001896e5df8
type=TRGR flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DEPENDENCIES: count=17 size=32
READ ONLY DEPENDENCIES: count=11 size=16
ACCESSES: count=11 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 c0000001896e6650 c0000001896e5b50 I/-/A 0 NONE
3 c0000001896e5f08 0 -/P/- 0 NONE
4 c0000001896e5fb0 c0000001896d1ea0 I/-/A 0 NONE
6 c0000001896e5ac8 c0000001737d8e90 I/-/A 0 NONE
----------------------------------------
SO: c000000185518760, type: 36, owner: c0000001682d7d40, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=c000000185518760 handle=0 lock=c00000017085dcd0
user=c0000001682d7d40 session=c0000001682d7d40 count=0 mask=0000 savepoint=25680 flags=[00]
----------------------------------------
SO: c00000017085dcd0, type: 35, owner: c0000001682d7d40, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c00000017085dcd0 handle=c00000017e56e300 mode=N
call pin=0 session pin=c000000185518760
user=c0000001682d7d40 session=c0000001682d7d40 count=1 flags=[00] savepoint=25677
LIBRARY OBJECT HANDLE: handle=c00000017e56e300
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch=0
lwt=c00000017e56e330[c00000017e56e330,c00000017e56e330] ltm=c00000017e56e340[c00000017e56e340,c00000017e56e340]
pwt=c00000017e56e360[c00000017e56e360,c00000017e56e360] ptm=c00000017e56e3f0[c00000017e56e3f0,c00000017e56e3f0]
ref=c00000017e56e310[c000000178d365f0,c000000178d365f0] lnd=c00000017e56e408[c00000017e56e408,c00000017e56e408]
LIBRARY OBJECT: object=c0000001734c4590
type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
DEPENDENCIES: count=1 size=16
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
ACCESSES: count=1 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 c00000018aac3210 c00000017d0c4828 I/P/A 0 NONE
6 c0000001734c46a0 c00000017efeb678 I/-/A 0 NONE
----------------------------------------
SO: c0000001709e7a38, type: 36, owner: c0000001682d7d40, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=c0000001709e7a38 handle=0 lock=c000000185cc41c8
user=c0000001682d7d40 session=c0000001682d7d40 count=0 mask=0000 savepoint=25521 flags=[00]
----------------------------------------

January 06, 2009 - 8:39 am UTC

update tc_subs_detail set prev_invoice_date = last_invoice_date,
last_invoice_date = to_date(:1,'yyyymmdd')
where area = :2 and phone = :3 and heker_no = :4
and start_date >= to_date(:5,'yyyymmddhh24miss')

that updates potentially many rows.

Are you sure you do not have a chance to update overlapping sets of data there?

does not look like an ITL deadlock and with initrans at 20, that should be sufficient.

Thanks a lot

A reader, April 29, 2009 - 3:04 am UTC

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

seems not to be ITL related

Marco, September 01, 2010 - 9:15 am UTC

Hello Tom,

In your last reaction (a long time ago...) you say that the last deadlock graph seems not to be ITL related. Reading the whole thread I came to the conclusion that
- if the transaction is of type 'S'
- and there is no row information
then there is a change of a deadlock caused by ITL waits. Both are the case in the last posted deadlock graph. Can you explain why this deadlock graph seems not to be ITL related? (or was this only based on the initrans 20).

Thanks,
Marco

Deadlock Question

Pardeep Saini, November 18, 2010 - 11:47 pm UTC

I am also getting the Global Enqueue deadlock. Please advise.

ENQUEUE DUMP REQUEST: from 3.24671 on [0x11f0003][0xb1774],[TX] for reason 2 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x11f0003][0xb1774],[TX]
----------resource 0x0x25c6dfea8----------------------
resname : [0x11f0003][0xb1774],[TX]
Local node : 1
dir_node : 1
master_node : 1
hv idx : 75
hv last r.inc : 86
current inc : 88
hv status : 0
hv master : 3
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 7 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x0
resp : 0x25c6dfea8
On Scan_q? : N
Total accesses: 85198
Imm. accesses: 76039
Granted_locks : 1
Cvting_locks : 7
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0x259ab0388 gl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25bd08e50 possible pid 13148 xid 55000-0002-00000DA8 bast 0 rseq 8622 mseq 0 history 0xd497d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x259abf5c8 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 owner 3 bast 1 rseq 6959 mseq 0x1 history 0x97d497ad
convert opt KJUSERGETVALUE
lp 0x25887fe08 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25ac7b4d8 possible pid 24657 xid 42000-0002-00000F57 bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x25760a260 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25fc8b140 possible pid 23109 xid 39000-0002-00000FEA bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x2586c41c8 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25acae248 possible pid 10807 xid 49000-0002-00000E9A bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x258a3e5f8 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25bd179c8 possible pid 20481 xid 45000-0002-00000E92 bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x259b56e40 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25aca8ca8 possible pid 29785 xid 52000-0002-00000D7B bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x259a00d80 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 owner 2 bast 1 rseq 6700 mseq 0x1 history 0x97d497ad
convert opt KJUSERGETVALUE
----------enqueue 0x0x259ab0388------------------------
lock version : 61845
Owner node : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x25c6dfea8
procp : 0x258283f38
pid : 7592
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : 0x25bd08e50
possible pid : 13148
xid : 55000-0002-00000DA8
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 : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE
History : 0xd497d495
Msg_Seq : 0x0
res_seq : 8622
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for TIMEOUT
possible owner[85.13148] on resource TX-011F0003-000B1774
Submitting asynchronized dump request [28]
*** 2010-07-16 17:28:23.056
Setting 3-way CR grants to 1 global-lru off? 0
*** 2010-07-16 18:43:39.035
kjddopr: skip converting lock 0x25760a260 dd_cnt 131
user session for deadlock lock 0x258503218
pid=40 serial=40460 audsid=11912807 user: 57/MPORTAL
O/S info: user: , term: , ospid: 1234, machine: coden-odp-app4
program:
Current SQL Statement:
UPDATE MENU_DOWNLOAD set last_modified_date = (sysdate-3)where device_contact =2027026240
user session for deadlock lock 0x258502e28
pid=90 serial=25605 audsid=11912767 user: 57/MPORTAL
O/S info: user: , term: , ospid: 1234, machine: coden-odp-app4
program:
Current SQL Statement:
UPDATE MENU_DOWNLOAD set last_modified_date = (sysdate-3)where device_contact =2027026240
user session for deadlock lock 0x258502e28
pid=90 serial=25605 audsid=11912767 user: 57/MPORTAL
O/S info: user: , term: , ospid: 1234, machine: coden-odp-app4
program:
Current SQL Statement:
UPDATE MENU_DOWNLOAD set last_modified_date = (sysdate-3)where device_contact =2027026240
ENQUEUE DUMP REQUEST: from 2.17670 on [0x11f0003][0xb1774],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x11f0003][0xb1774],[TX]
----------resource 0x0x25c6dfea8----------------------
resname : [0x11f0003][0xb1774],[TX]
Local node : 1
dir_node : 1
master_node : 1
hv idx : 75
hv last r.inc : 86
current inc : 88
hv status : 0
hv master : 3
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 7 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x0
resp : 0x25c6dfea8
On Scan_q? : N
Total accesses: 107134
Imm. accesses: 97130
Granted_locks : 1
Cvting_locks : 7
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0x259ab0388 gl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25bd08e50 possible pid 13148 xid 55000-0002-00000DA8 bast 0 rseq 8622 mseq 0 history 0xd497d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x259abf5c8 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 owner 3 bast 1 rseq 6959 mseq 0x1 history 0x97d497ad
convert opt KJUSERGETVALUE
lp 0x25887fe08 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25ac7b4d8 possible pid 24657 xid 42000-0002-00000F57 bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x25760a260 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25fc8b140 possible pid 23109 xid 39000-0002-00000FEA bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x2586c41c8 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25acae248 possible pid 10807 xid 49000-0002-00000E9A bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x258a3e5f8 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25bd179c8 possible pid 20481 xid 45000-0002-00000E92 bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x259b56e40 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 gl owner 0x25aca8ca8 possible pid 29785 xid 52000-0002-00000D7B bast 0 rseq 8622 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x259a00d80 gl KJUSERNL rl KJUSEREX rp 0x25c6dfea8 [0x11f0003][0xb1774],[TX]
master 1 owner 2 bast 1 rseq 6700 mseq 0x1 history 0x97d497ad
convert opt KJUSERGETVALUE

********************************************************************

Second Issue:

lp 0x259d168c0 gl KJUSERNL rl KJUSERPR rp 0x23b819ec0 [0x12998541][0xf74ca239],[LB]
master 3 gl owner 0x25acacce0 possible pid 29966 xid 2D000-0002-00000D81 bast 0 rseq 9474 mseq 0 history 0x1495149a
convert opt
lp 0x25ad28a78 gl KJUSERNL rl KJUSERPR rp 0x23b819ec0 [0x12998541][0xf74ca239],[LB]
master 3 gl owner 0x25bcdb680 possible pid 25584 xid 76000-0002-000003E0 bast 0 rseq 9474 mseq 0 history 0x1495149a
convert opt
ENQUEUE DUMP REQUEST: from 3.8574 on [0x8a8385f5][0x7e18817a],[LB] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x8a8385f5][0x7e18817a],[LB]
----------resource 0x0x23db7ea58----------------------
resname : [0x8a8385f5][0x7e18817a],[LB]
Local node : 1
dir_node : 2
master_node : 2
hv idx : 59
hv last r.inc : 30
current inc : 58
hv status : 0
hv master : 2
open options : dd
Held mode : KJUSERPR
Cvt mode : KJUSERNL
Next Cvt mode : KJUSERNL
msg_seq : 0x20003
res_seq : 10326
grant_bits : KJUSERPR
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 0 0 0 2 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x8
resp : 0x23db7ea58
On Scan_q? : N
cache level : 1
Total accesses: 97286
Imm. accesses: 86878
Granted_locks : 2
Cvting_locks : 0
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0x25ad28928 gl KJUSERPR rp 0x23db7ea58 [0x8a8385f5][0x7e18817a],[LB]
master 2 gl owner 0x25bcdb680 possible pid 25584 xid 76000-0002-000003E0 bast 0 rseq 10326 mseq 0 history 0x14951495
open opt KJUSERDEADLOCK
lp 0x259d16770 gl KJUSERPR rp 0x23db7ea58 [0x8a8385f5][0x7e18817a],[LB]
master 2 gl owner 0x25acacce0 possible pid 29966 xid 2D000-0002-00000D81 bast 0 rseq 10326 mseq 0 history 0x495149a5
open opt KJUSERDEADLOCK
CONVERT_Q:
----------enqueue 0x0x25ad28928------------------------
lock version : 13829467
Owner node : 1
grant_level : KJUSERPR
req_level : KJUSERPR
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x23db7ea58
procp : 0x2582aa758
pid : 25584
proc version : 1363
oprocp : (nil)
opid : 0
group lock owner : 0x25bcdb680
possible pid : 25584
xid : 76000-0002-000003E0
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 : KJUSERDEADLOCK
Convert options :
History : 0x14951495
Msg_Seq : 0x0
res_seq : 10326
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[118.25584] on resource LB-8A8385F5-7E18817A
Submitting asynchronized dump request [28]
----------enqueue 0x0x259d16770------------------------
lock version : 12711571
Owner node : 1
grant_level : KJUSERPR
req_level : KJUSERPR
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x23db7ea58
procp : 0x2582c1c00
pid : 29966
proc version : 586
oprocp : (nil)
opid : 0
group lock owner : 0x25acacce0
possible pid : 29966
xid : 2D000-0002-00000D81
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 : KJUSERDEADLOCK
Convert options :
History : 0x495149a5
Msg_Seq : 0x0
res_seq : 10326
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[45.29966] on resource LB-8A8385F5-7E18817A
Submitting asynchronized dump request [28]
user session for deadlock lock 0x259d16770
pid=45 serial=15979 audsid=10529993 user: 57/MPORTAL
O/S info: user: , term: , ospid: 1234, machine: coden-odp-app5
program:
Current SQL Statement:

SELECT COMM_END_POINT.COMM_END_POINT_ID AS DEVICE_ID,COMM_END_POINT.DEVICE_ID AS DEVICE_ID1, COMM_END_POINT.USER_ID,DEVICE.DEVICE_NBR,CODE.CODE AS STATUS, DEVICE.UNIT_NBR, USER_PROFILE.USER_BILLING_TYPE, USER_PROFILE.DISC_USER_CATEGORY_ID, DEVICE.MODEL_ID FROM COMM_END_POINT, DEVICE, USER_PROFILE, CODE WHERE COMM_END_POINT.DEVICE_ID = DEVICE.DEVICE_ID AND DEVICE.DEVICE_CONTACT= :1 AND USER_PROFILE.USER_ID = COMM_END_POINT.USER_ID AND USER_PROFILE.STATUS_CD = CODE.CODE_ID AND CODE.CODE <> 'Disconnected'
user session for deadlock lock 0x259d168c0
pid=45 serial=15979 audsid=10529993 user: 57/MPORTAL
O/S info: user: , term: , ospid: 1234, machine: coden-odp-app5
program:
Current SQL Statement:

SELECT COMM_END_POINT.COMM_END_POINT_ID AS DEVICE_ID,COMM_END_POINT.DEVICE_ID AS DEVICE_ID1, COMM_END_POINT.USER_ID,DEVICE.DEVICE_NBR,CODE.CODE AS STATUS, DEVICE.UNIT_NBR, USER_PROFILE.USER_BILLING_TYPE, USER_PROFILE.DISC_USER_CATEGORY_ID, DEVICE.MODEL_ID FROM COMM_END_POINT, DEVICE, USER_PROFILE, CODE WHERE COMM_END_POINT.DEVICE_ID = DEVICE.DEVICE_ID AND DEVICE.DEVICE_CONTACT= :1 AND USER_PROFILE.USER_ID = COMM_END_POINT.USER_ID AND USER_PROFILE.STATUS_CD = CODE.CODE_ID AND CODE.CODE <> 'Disconnected'
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x12998541][0xf74ca239],[LB]
----------resource 0x0x23b819ec0----------------------
resname : [0x12998541][0xf74ca239],[LB]
Local node : 1
dir_node : 3
master_node : 3
hv idx : 70
hv last r.inc : 32
current inc : 58
hv status : 0
hv master : 3
open options : dd
Held mode : KJUSERNL
Cvt mode : KJUSERPR
Next Cvt mode : KJUSERNL
msg_seq : 0x1
res_seq : 9474
grant_bits : KJUSERNL
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 2 0 0 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 3
vbreq_state : 0
state : x8
resp : 0x23b819ec0
On Scan_q? : N
Total accesses: 85656
Imm. accesses: 76145
Granted_locks : 0
Cvting_locks : 2
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
CONVERT_Q:
lp 0x259d168c0 gl KJUSERNL rl KJUSERPR rp 0x23b819ec0 [0x12998541][0xf74ca239],[LB]
master 3 gl owner 0x25acacce0 possible pid 29966 xid 2D000-0002-00000D81 bast 0 rseq 9474 mseq 0 history 0x1495149a
convert opt
lp 0x25ad28a78 gl KJUSERNL rl KJUSERPR rp 0x23b819ec0 [0x12998541][0xf74ca239],[LB]
master 3 gl owner 0x25bcdb680 possible pid 25584 xid 76000-0002-000003E0 bast 0 rseq 9474 mseq 0 history 0x1495149a
convert opt
Global blockers dump end:-----------------------------------
ENQUEUE DUMP REQUEST: from 3.8574 on [0x8a8385f5][0x7e18817a],[LB] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x8a8385f5][0x7e18817a],[LB]

INITTRANS

Ramki, May 04, 2012 - 7:28 am UTC

Hi Tom,

My questions is similar to "April 28, 2004 - 8pm Central time zone" in this thread ,can INITTRANS be cause of a deadlock for concurrent operations

Deadlock graph shows "no row"
i guess this is caused by INITTRANS.
In schema we have ~250 table , each tables are partition by day and PK - locally partitin and index/PK is compressed.

Table & index are created with default INITTRANS value 1 & 2 respectively.

My application is doing ETL, is high data loading millions of rows are loaded in table and this will happen in high concurrency.

How I can find it out problem is due to INITTRANS.
if so how I can find this because if index or table.

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00200016-00007523 387 334 X 467 418 S
TX-00450012-000062af 467 418 X 387 334 S

session 334: DID 0001-0183-00000477 session 418: DID 0001-01D3-0000004C
session 418: DID 0001-01D3-0000004C session 334: DID 0001-0183-00000477

Rows waited on:
Session 334: no row
Session 418: no row

----- Information for the OTHER waiting sessions -----
Session 418:
sid: 418 ser: 29717 audsid: 2632085 user: 85/OMC
flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 467 O/S info: user: oracle, term: UNKNOWN, ospid: 23983
image: oracle@idlds2
client details:
O/S info: user: system, term: unknown, ospid: 1234
machine: idlas2.idl1.netact.tim.it program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
INSERT INTO H2GDB_PMB.M1275069417 (start_time,network_element_CO_GID,c1276069420,c1276069421,c1276069419,weight,duration,c1276069422,c1276069423) VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 )

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=c03maw8bdga3n) -----
INSERT INTO H2GDB_PMB.M1275070117 (start_time,network_element_CO_GID,c1276769417,c1282411883,weight,duration) VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 )

Thanks & regards
Ramki

May 06, 2012 - 2:34 pm UTC

My application is doing ETL, is high data loading millions of rows are loaded
in table and this will happen in high concurrency.

that is just about the single worst approach possible. Data loading and ETL should be single threaded, with database parallelism, using direct path (so you skip undo, optionally redo, and take advantage of compression)....

If you are inserting, it'll be an index.

But again, your ETL process isn't going to work for too many years - heck, it isn't already ;)

INITTRANS

Ramki, May 07, 2012 - 5:16 am UTC

>>>> If you are inserting, it'll be an index.
You mean its index is the problem?

From Deadlock graph how can I find out problem is due to INITTRANS.
If so how I can find this because if index or table.

I suspect Index (PK) compression could be a reason, but I don't how to figure it out.
Since there is high degree of compression is possible in Index, all the index might try to insert in to same block in parallel.

May 07, 2012 - 5:52 am UTC

If you are doing conventional path inserts - it will be index, not table based.

Again, I'd really suggest looking at your code and NOT doing it the way you are. If you want to scale, if you want this to work, you will use bulk direct path operations, compress your tables, use database parallelism not "do it yourself" parallelism.

please don't turn your warehouse into an OLTP system.

if we change initrans, does table allow frequent updates?

uma, December 25, 2012 - 6:19 am UTC

Hi TOM,
Now the problem is , we have one of the table(data_t) in the database, In month end days users updating frequently on this table,
This frequent(parallel) updates creating locks (or) dead locks in the database. this table created with default initrans 1 value. now can i change this inittrans value 1 to 10 , can i prevent this deadlock issue? Please guide me.

January 04, 2013 - 10:40 am UTC

need more information as to how these updates are done. given what you've said so far - I'm guessing "application logic is messed up" more than "inittrans"

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

INITRANS Cause of deadlock

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Wouldn't have thought to look there

deadlock

initrans related deadlock or not

Whether it is due to low initrans or bitmap index

Distinguishing waits due to INITRANS

Distinguish bettween waits due to ITL and bitmap index

Need help understanding this deadlock

Overlapping primary keys....

Oh yeah, minor detail of bind variables.....

Deadlocks on GTTs

Forest and trees

initrans maxtrans, index and table

Would you agree with the following?

Probably not an ITL wait.....

Not unindexed FKs......

INITRANS vs MAXTRANS

INITRANS and ITL growth

Thanks!!!

ORA-00060: deadlock detected while waiting for resource

ORA-00060: deadlock detected while waiting for resource

INI_TRANS on INDEXES as well?

Method to determine high DML tables

permanent solution

Restrict the number of concurrent session

Another deadlock solution

INITRANS Deadlock?

ITL Waits

Deadlock due to a committed transaction!!!

MAXTRANS Limit in Oracle 10g R2

George

Deadlocks

Deadlock problem with INITTRANS=20

Thanks a lot

seems not to be ITL related

Deadlock Question

INITTRANS

INITTRANS

if we change initrans, does table allow frequent updates?

More to Explore

VLDB

VLDB