ORA-00060: deadlock detected while waiting for resource

From: mike <hillmw_at_charter.net>
Date: 7 Jun 2005 09:09:50 -0700
Message-ID: <1118160590.589071.227950@g43g2000cwa.googlegroups.com>

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 Received on Tue Jun 07 2005 - 11:09:50 CDT