Deadlock question
- From: "Causey, Bob" <BCausey@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Feb 2012 11:01:31 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
