RE: Deadlock question

Hi Bob,

From the looks of the included deadlock graph, this is *not* an INITRANS 
problem.  (That would explain why continuing to increase INITRANS isn't solving 
the problem.)  If this were an INITRANS problem, the deadlock graph would 
indicate 'X' where lock is being held, and 'S' where lock is being waited on.  
In your deadlock graph, lock mode being held is 'X', but lock modewaited on is 
also 'X'.

So, in your case, this is simple, row-level locking.  You have session 1 locks 
row A, session 2 locks row B, then session 1 tries to lock row B, starts 
waiting, then session 2 tries to lock row A, and there's your deadlock.

So, you need to look at the application, and understand how or why different 
sessions would overlap on the set of rows they are processing.

Hope that helps,

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Causey, Bob
Sent: Friday, February 24, 2012 11:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Deadlock question

Content-Type: text/plain;
        charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable We have our first 32K block size 
customer and suddenly we are receiving several deadlock graphs (0060) with what 
looks to be true row level deadlocks.  We have had a history of this problem 
relating to initran on both tables and indexes.  We usually adjusted initran 
and the problem goes away. =20

=20

This time however "rows waited on" is displaying and I can get table data 
returned from the rowid. =20

=20

Is it possible that this is still an initran problem?  The reason I ask is when 
we reduce the activity (number of concurrent processes) against the database 
the process runs.  We however are already up to 120
initrans.   This is an example of the problem but I have tons of
information available.

=20

DEADLOCK GRAPH

=20

*** 2012-02-20 09:14:19.568

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

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

Resource Name          process session holds waits  process session
holds waits

TX-005f0006-000010bc       332    1003     X            359    3243
X

TX-0039003d-000010a1       359    3243     X            332    1003
X

session 1003: DID 0001-014C-00000003      session 3243: DID
0001-0167-00000001=20

session 3243: DID 0001-0167-00000001       session 1003: DID
0001-014C-00000003=20

=20

Rows waited on:

  Session 1003: obj - rowid =3D 000124E2 - AAAUN8AAkAAACv5AcI

  (dictionary objn - 74978, file - 36, block - 11257, slot - 1800)

  Session 3243: obj - rowid =3D 000124E2 - AAAUN8AAlAAAEEnAZK

  (dictionary objn - 74978, file - 37, block - 16679, slot - 1610)

----- Information for the OTHER waiting sessions -----

Session 3243:

  sid: 3243 ser: 2733 audsid: 112588 user: 94/LPSUSER

    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x40008) -/-

  pid: 359 O/S info: user: oracle, term: UNKNOWN, ospid: 5213

    image: oracle@xxxxxxxxxxxxxxxxxxxxx

  client details:

    O/S info: user: XXXXXXXXX, term: MS00902, ospid: 7012:7016

    machine: XXXXXXXXX\XXXXXXXXXXXX program: wdrsct.exe

    application name: wdrsct.exe, hash value=3D2396471524

  current SQL:

  update SCP_FCST_TIME_SERIES  set
FCST_YR_PRD=3D:b1,DMD_ACTL_QTY=3D0,DMD_ADJ_QTY=3D0,DMD_CODE=3D'N',DMD_ADD=
L_QTY=3D0
,VAL_HIST_QTY=3D0,FCST_SYS_QTY=3D0,FCST_SYS_FRC_QTY=3D0,FCST_RSLT_QTY=3D0=
,MGMT_C
ODE=3D'H',FCST_SUM_QTY=3D0,BUDGET_QTY=3D0,BUDGET_VALUE=3D0,PRC_TIME_PHSD_=
VALUE=3D0
,PRC_TIME_PHSD_CODE=3D'N',COST_TIME_PHSD_VALUE=3D0,COST_TIME_PHSD_CODE=3D=
'N',O
RD_FUT_QTY=3D0,USR_01_QTY=3D0,USR_02_QTY=3D0,USR_03_QTY=3D0,USR_04_QTY=3D=
0,USR_05_
QTY=3D0,USR_06_QTY=3D0,USR_07_QTY=3D0,USR_08_QTY=3D0,ADS1_MGMT_CODE=3D'H'=
,DMD_ADDL
2_QTY=3D0,ADS2_MGMT_CODE=3D'H',DMD_ADDL3_QTY=3D0,ADS3_MGMT_CODE=3D'H',ADS=
1_FCST_
RSLT_QTY=3D0,ADS2_FCST_RSLT_QTY=3D0,ADS3_FCST_RSLT_QTY=3D0 where rowid =
=3D:b2

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

Information for THIS session:

----- Current SQL Statement for this session (sql_id=3Db0pz0cnrkx3b3)
-----

update SCP_FCST_TIME_SERIES  set
FCST_YR_PRD=3D:b1,DMD_ACTL_QTY=3D0,DMD_ADJ_QTY=3D0,DMD_CODE=3D'N',DMD_ADD=
L_QTY=3D0
,VAL_HIST_QTY=3D0,FCST_SYS_QTY=3D0,FCST_SYS_FRC_QTY=3D0,FCST_RSLT_QTY=3D0=
,MGMT_C
ODE=3D'H',FCST_SUM_QTY=3D0,BUDGET_QTY=3D0,BUDGET_VALUE=3D0,PRC_TIME_PHSD_=
VALUE=3D0
,PRC_TIME_PHSD_CODE=3D'N',COST_TIME_PHSD_VALUE=3D0,COST_TIME_PHSD_CODE=3D=
'N',O
RD_FUT_QTY=3D0,USR_01_QTY=3D0,USR_02_QTY=3D0,USR_03_QTY=3D0,USR_04_QTY=3D=
0,USR_05_
QTY=3D0,USR_06_QTY=3D0,USR_07_QTY=3D0,USR_08_QTY=3D0,ADS1_MGMT_CODE=3D'H'=
,DMD_ADDL
2_QTY=3D0,ADS2_MGMT_CODE=3D'H',DMD_ADDL3_QTY=3D0,ADS3_MGMT_CODE=3D'H',ADS=
1_FCST_
RSLT_QTY=3D0,ADS2_FCST_RSLT_QTY=3D0,ADS3_FCST_RSLT_QTY=3D0 where rowid =
=3D:b2

=20

=20

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

=20

=20

ORACLE OBJECT ID

=20

Please get me the results of=20

            select object_name, owner from dba_objects

where object_id =3D 74978;

=20

=20

=20

=20

ROWID INFORMATION

select t.scp_seq_nbr, t.fcst_yr_prd, r.fcst_id=20

from scp_fcst_time_series t

inner join scp_fcst_root r

on t.scp_seq_nbr =3D r.scp_seq_nbr

where t.rowid =3D 'AAAUN8AAkAAACv5AcI';

=20

select t.scp_seq_nbr, t.fcst_yr_prd, r.fcst_id=20

from scp_fcst_time_series t

inner join scp_fcst_root r

on t.scp_seq_nbr =3D r.scp_seq_nbr

where t.rowid =3D 'AAAUN8AAlAAAEEnAZK';

=20

=20

=20

=20

=20

=20

INITRAN INFORMATION

=20

=20

=20

=20

=20

I would appreciate any assistance from the forum.

=20

Thanks you,

Bob

=20

Bob Causey

Database Administrator

Logility Inc.

phone 404-264-5919

fax 404-364-7617

bcausey@xxxxxxxxxxxx

=20




--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l


Other related posts: