RE: Deadlock question

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Fri, 24 Feb 2012 12:07:23 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F8AB2CD9F_at_AAPQMAILBX02V.proque.st>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Causey, Bob Sent: Friday, February 24, 2012 11:02 AM To: oracle-l_at_freelists.org
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_at_lx00278.starbucks.net

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)

=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

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

INITRAN INFORMATION
=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_at_logility.com

=20

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




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

Received on Fri Feb 24 2012 - 11:07:23 CST

This message: [ Message body ]
Next message: Causey, Bob: "RE: Deadlock question"
Previous message: Adams, Matthew (GE, Appl & Light): "determining actual user when using EUS"
In reply to: Causey, Bob: "Deadlock question"
Next in thread: Causey, Bob: "RE: Deadlock question"
Reply: Causey, Bob: "RE: Deadlock question"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message