Oracle Database Discussions

Dear all,

We got two Deadlocks for same table (OUTSTANDING_SALES_TAB) one with "Rows waited on: no row" for both sessions and other one with "Rows waited on: no row" for one session. I can't figure out why we are getting the "Rows waited on: no row" type of Deadlocks. Could anyone help me to identify this?

============== 1 st Deadlock=========================================
*** 2011-07-12 00:11:27.861
*** ACTION NAME:() 2011-07-12 00:11:27.860
*** MODULE NAME:() 2011-07-12 00:11:27.860
*** SERVICE NAME:(SYS$USERS) 2011-07-12 00:11:27.860
*** SESSION ID:(120.8609) 2011-07-12 00:11:27.860
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-0014000c-00004948 100 120 X 56 142 S
TX-00110028-00005449 56 142 X 100 120 S
session 120: DID 0001-0064-00044205 session 142: DID 0001-0038-000D56D8
session 142: DID 0001-0038-000D56D8 session 120: DID 0001-0064-00044205
Rows waited on:
Session 142: no row
Session 120: no row
Information on the OTHER waiting sessions:
Session 142:
pid=56 serial=14474 audsid=46663024 user: 20/APP
O/S info: user: oracle, term: UNKNOWN, ospid: 28102, machine: ora
program: oracle@ora (J002)
client info: FLO02 0en
Current SQL Statement:
SELECT ROWID OBJID, TO_CHAR(ROWVERSION,'YYYYMMDDHH24MISS') OBJVERSION FROM OUTSTANDING_SALES_TAB WHERE DELIV_NO = :B1 AND INVOICE_ID IS NULL ORDER BY OUTSTANDING_SALES_ID FOR UPDATE
End of information on OTHER waiting sessions.
Current SQL statement for this session:
SELECT ROWID OBJID, TO_CHAR(ROWVERSION,'YYYYMMDDHH24MISS') OBJVERSION FROM OUTSTANDING_SALES_TAB WHERE DELIV_NO = :B1 AND INVOICE_ID IS NULL ORDER BY OUTSTANDING_SALES_ID FOR UPDATE

================ End First Deadlock =====================================================================

=============== Second Deadlock =======================================================================
*** 2011-07-12 00:11:36.426
*** ACTION NAME:() 2011-07-12 00:11:36.425
*** MODULE NAME:() 2011-07-12 00:11:36.425
*** SERVICE NAME:(SYS$USERS) 2011-07-12 00:11:36.425
*** CLIENT ID:() 2011-07-12 00:11:36.425
*** SESSION ID:(142.14474) 2011-07-12 00:11:36.425
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-00110028-00005449 56 142 X 121 213 S
TX-000b0025-000118f4 121 213 X 56 142 S
session 142: DID 0001-0038-000D56D8 session 213: DID 0001-0079-0001BA2A
session 213: DID 0001-0079-0001BA2A session 142: DID 0001-0038-000D56D8
Rows waited on:
Session 213: no row
Session 142: obj - rowid = 0000FAA1 - AAAPqhAAFAAB+9BAAA (dictionary objn - 64161, file - 5, block - 520001, slot - 0)
Information on the OTHER waiting sessions:
Session 213:
pid=121 serial=59352 audsid=46662978 user: 20/APP
O/S info: user: oracle, term: UNKNOWN, ospid: 28170, machine: ora
program: oracle@ora (J015)
client info: FLO02 0en
Current SQL Statement:
SELECT ROWID OBJID, TO_CHAR(ROWVERSION,'YYYYMMDDHH24MISS') OBJVERSION FROM OUTSTANDING_SALES_TAB WHERE DELIV_NO = :B1 AND INVOICE_ID IS NULL ORDER BY OUTSTANDING_SALES_ID FOR UPDATE
End of information on OTHER waiting sessions.
Current SQL statement for this session:
SELECT ROWID OBJID, TO_CHAR(ROWVERSION,'YYYYMMDDHH24MISS') OBJVERSION FROM OUTSTANDING_SALES_TAB WHERE DELIV_NO = :B1 AND INVOICE_ID IS NULL ORDER BY OUTSTANDING_SALES_ID FOR UPDATE
=============== End Second Deadlock=====================================================================


Table definition and indexes for this table

-- Create table
create table OUTSTANDING_SALES_TAB
(
OUTSTANDING_SALES_ID NUMBER not null,
DELIV_NO NUMBER not null,
CONTRACT VARCHAR2(5) not null,
COMPANY VARCHAR2(20) not null,
INVOICE_ID NUMBER,
ITEM_ID NUMBER,
QTY_EXPECTED NUMBER not null,
QTY_SHIPPED NUMBER not null,
DATE_COGS_POSTED DATE,
DATE_SALES_POSTED DATE,
ROWVERSION DATE not null
)
tablespace APP_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

-- Create/Recreate primary, unique and foreign key constraints
alter table OUTSTANDING_SALES_TAB
add constraint OUTSTANDING_SALES_PK primary key (OUTSTANDING_SALES_ID)
using index
tablespace APP_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index OUTSTANDING_SALES_1X on OUTSTANDING_SALES_TAB (DELIV_NO)
tablespace APP_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index OUTSTANDING_SALES_2X on OUTSTANDING_SALES_TAB (COMPANY, DATE_COGS_POSTED, DATE_SALES_POSTED)
tablespace APP_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index OUTSTANDING_SALES_3X on OUTSTANDING_SALES_TAB (INVOICE_ID)
tablespace APP_INDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


Thank you.