I am getting "ORA-00060: deadlock detected while waiting for resource"
oracle error message when some on my users are savings their data.

Sounds like some kind of contention problem.

I have a number of tables that are being updated on this update script
using coldfusion something like:

<cftransaction>

update tableA set x='?', y='?' where mykey='123'

delete from tableB where mykey='123'
insert into tableB (mykey, m, n) values ('123','??','??')
insert into tableB (mykey, m, n) values ('123','??','??')

delete from tableC where mykey='123'
insert into tableC (mykey, j, k) values ('123','??','??')
insert into tableC (mykey, j, k) values ('123','??','??')
..
..
..
</cftransaction>

I think you get the message. Instead of checking to see what was
added/changed/deleted, etc. I delete the rows and insert. If there are
any errors then the would transaction gets rolled back.

I looked at the lock graph and part of it looked like:

---------Blocker(s)--------
---------Waiter(s)---------
Resource Name process session holds waits process session
holds waits
TM-0000b5e8-00000000 16 7 SX 27 52
SX SSX
TX-0001000d-00054659 27 52 X 16 7
X
session 7: DID 0001-0010-00000002 session 52: DID 0001-001B-00000002
session 52: DID 0001-001B-00000002 session 7: DID 0001-0010-00000002
Rows waited on:
Session 52: no row
Session 7: obj - rowid = 0000B5E8 - AAALXoABFAAAAHWAAC

I have no idea what Session 52: no row is.

Any help is appreciated.

Mike

mike wrote:
> I am getting "ORA-00060: deadlock detected while waiting for resource"
> oracle error message when some on my users are savings their data.
>
> Sounds like some kind of contention problem.
>
> I have a number of tables that are being updated on this update script
> using coldfusion something like:
>
> <cftransaction>
>
> update tableA set x='?', y='?' where mykey='123'
>
> delete from tableB where mykey='123'
> insert into tableB (mykey, m, n) values ('123','??','??')
> insert into tableB (mykey, m, n) values ('123','??','??')
>
> delete from tableC where mykey='123'
> insert into tableC (mykey, j, k) values ('123','??','??')
> insert into tableC (mykey, j, k) values ('123','??','??')
> .
> .
> .
> </cftransaction>
>
> I think you get the message. Instead of checking to see what was
> added/changed/deleted, etc. I delete the rows and insert. If there are
> any errors then the would transaction gets rolled back.
>
> I looked at the lock graph and part of it looked like:
>
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session
> holds waits
> TM-0000b5e8-00000000 16 7 SX 27 52
> SX SSX
> TX-0001000d-00054659 27 52 X 16 7
> X
> session 7: DID 0001-0010-00000002 session 52: DID 0001-001B-00000002
> session 52: DID 0001-001B-00000002 session 7: DID 0001-0010-00000002
> Rows waited on:
> Session 52: no row
> Session 7: obj - rowid = 0000B5E8 - AAALXoABFAAAAHWAAC
>
> I have no idea what Session 52: no row is.
>
> Any help is appreciated.
>
> Mike

http://metalink.oracle.com
open an iTAR
provided the following exists

1. You have actually applied all applicable patches to whatever, unnamed
database version, you have.

2. You have created an RDA to attach to the iTAR.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Deadlock information like that shown where one of the sessions has no
row are not unusual and I do not think an iTAR is necessary or will do
much good. The problem is still the application where two different
sessions are going after the same data.

Session 7 holds a row (TM) and needs information from the rollback
segment (TX) held exclusively by session 52.

Question what does "Session 7: obj - rowid = 0000B5E8 -
AAALXoABFAAAAHWAAC" point to?

To solve the problem it is necessary to find the two pieces of code and
compare what they do, in what order they do it, and consideration has
to be given to the rows that the Oracle read consistency model will be
returning recognizing the fact a second session will be accessing some
of the same rows during the transaction.

HTH -- Mark D Powell --

Question what does "Session 7: obj - rowid = 0000B5E8 -
AAALXoABFAAAAHWAAC" point to?

How do I find that out Mark?

The information displayed should be a rowid so look at dbms_rowid.
Soory I missed the additon to the thread till now.

HTH -- Mark D Powell --

Are you saying look at:

select * from dbms_rowid

Procedure, function, package, or type not allowed here.

Mike

"mike" <hillmw@charter.net> wrote in
news:1119365265.693425.267050@g14g2000cwa.googlegr oups.com:

> Are you saying look at:
>
> select * from dbms_rowid
>
> Procedure, function, package, or type not allowed here.
>
> Mike
>

SQL> desc dbms_rowid
FUNCTION ROWID_BLOCK_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_CREATE RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN
RELATIVE_FNO NUMBER IN
BLOCK_NUMBER NUMBER IN
ROW_NUMBER NUMBER IN
PROCEDURE ROWID_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
FUNCTION ROWID_OBJECT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_RELATIVE_FNO RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_ROW_NUMBER RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_TO_ABSOLUTE_FNO RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
FUNCTION ROWID_TO_EXTENDED RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TO_RESTRICTED RETURNS ROWID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TYPE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_VERIFY RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN

I have no idea what to do with this.

When I used: desc dbms_rowid using TOAD I got a package body window
loaded and when pressed execute procedure I got a series of "set
parameters" one of which was "rowid_info" defined below.

How do I use this to solve my problem: deadlocks

DECLARE
ROWID_IN ROWID;
ROWID_TYPE NUMBER;
OBJECT_NUMBER NUMBER;
RELATIVE_FNO NUMBER;
BLOCK_NUMBER NUMBER;
ROW_NUMBER NUMBER;

BEGIN
ROWID_IN := NULL;
ROWID_TYPE := NULL;
OBJECT_NUMBER := NULL;
RELATIVE_FNO := NULL;
BLOCK_NUMBER := NULL;
ROW_NUMBER := NULL;

SYS.DBMS_ROWID.ROWID_INFO ( ROWID_IN, ROWID_TYPE, OBJECT_NUMBER,
RELATIVE_FNO, BLOCK_NUMBER, ROW_NUMBER );
COMMIT;
END;

"mike" <hillmw@charter.net> wrote in
news:1121267376.820348.183150@g43g2000cwa.googlegr oups.com:

> I have no idea what to do with this.
>
> When I used: desc dbms_rowid using TOAD I got a package body window
> loaded and when pressed execute procedure I got a series of "set
> parameters" one of which was "rowid_info" defined below.
>
> How do I use this to solve my problem: deadlocks
>
> DECLARE
> ROWID_IN ROWID;
> ROWID_TYPE NUMBER;
> OBJECT_NUMBER NUMBER;
> RELATIVE_FNO NUMBER;
> BLOCK_NUMBER NUMBER;
> ROW_NUMBER NUMBER;
>
> BEGIN
> ROWID_IN := NULL;
> ROWID_TYPE := NULL;
> OBJECT_NUMBER := NULL;
> RELATIVE_FNO := NULL;
> BLOCK_NUMBER := NULL;
> ROW_NUMBER := NULL;
>
> SYS.DBMS_ROWID.ROWID_INFO ( ROWID_IN, ROWID_TYPE, OBJECT_NUMBER,
> RELATIVE_FNO, BLOCK_NUMBER, ROW_NUMBER );
> COMMIT;
> END;
>

Deadlocks are caused by poorly coded applications.
By using the information in the trace/dump files,
you should be able to identify which object(s)
are involved. From there you should be able to
determine where in the application the conflict
occurs. From there the application needs to be
changed so two different pieces of code don't
fight over the same object at the same time.