Re: 'no row' deadlock - ORA-00060

So far I've discovered 8 reasons why you can get TX locks in mode 4 on an Oracle
system; and most of them can lead to
'row-free' deadlocks.

The most likely causes on a delete statement, especially when you mention 'same table different rows' are:

1. Is the table involved in a parent/child relation where there is no index on the child table representing a foreign key
2. Are there any bitmap indexes on the table, as a row delete locks a bitmap section, which covers many table - so a second session could be waiting for the bitmap section to be released, without being able to identify which table row was causing the bitmap section to be locked.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Samuel Becker wrote in message ...


>Hello,

>

>executing a multi-threaded application, i get puzzling deadlocks.

>Indeed, each thread works on the same tables but not on same rows !

>

>Notes :

>- I have tried to put INITRANS value of all tables and index to 100 instead

>  of 1 but it did not change anything.

>

>- I use Oracle sequences, and sequence name appears in Oracle log. Can

Oracle


>  sequence induce deadlock ? I don't think so but..

>

>

>

>Here's a sample of Oracle log:

>

>

>

>*** 2001-11-28 16:54:45.280

>*** SESSION ID:(16.2237) 2001-11-28 16:54:45.270

>DEADLOCK DETECTED

>Current SQL statement for this session:

> DELETE FROM TRK_ELEMENTARYTRANSPORT WHERE TRK_ELEMENTARYTRANSPORT_ID=:1

>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


>TM-000067fb-00000000        18      16    SX   SSX       23      20    SX

SSX


>TM-000067fb-00000000        23      20    SX   SSX       18      16    SX

SSX


>session 16: DID 0001-0012-00000002 session 20: DID 0001-0017-00000002

>session 20: DID 0001-0017-00000002 session 16: DID 0001-0012-00000002

>Rows waited on:

>Session 20: no row

>Session 16: no row

>

>Thanks.