Re: Deadlocks

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 21 Jul 2001 21:24:56 GMT
Message-ID: <9ht0ev02qn8@drn.newsguy.com>

In article <9hsmma$g3p1k$1_at_ID-85580.news.dfncis.de>, "Chuck says...
>
>Anyone ever gotten a deadlock detected error where the trace file doesn't
>show the row locks causing the deadlock? What causes this type of deadlock.
>I had several this morning and here's the deadlock graph from the trace
>file.
>
>Deadlock graph:
> ---------Blocker(s)-------- ---------Waiter(s)------
>---
>Resource Name process session holds waits process session holds
>waits
>TM-00000afb-00000000 22 24 S 14 43
>SX
>TM-00000b07-00000000 14 43 SX 22 24
>S
>session 24: DID 0001-0016-00000002 session 43: DID 0001-000E-00000002
>session 43: DID 0001-000E-00000002 session 24: DID 0001-0016-00000002
>Rows waited on:
>Session 43: no row
>Session 24: no row
>
>--
>Never share a foxhole with anyone braver than yourself!
>
>Chuck Hamilton
>chuck_hamilton_at_hotmail.com
>
>

Sure, run a script like:

drop table t1 cascade constraints;
drop table c1;
drop table t2 cascade constraints;

create table t1 ( x int primary key, y references t1 );

create table t2 ( x int primary key );

insert into t1 values ( 1, null );
insert into t1 values ( 2, null );

commit;

delete from t1 where x = 1;

prompt in another session issue:
prompt insert into t2 values ( 1 ) ;;
prompt insert into t1 values ( 3, null ) ;; pause

insert into t2 values ( 1 );

and do what the prompt says to do and you'll get a deadlock graph like:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0003001e-00000c35        13       9     X             12       8           S
TM-0000836a-00000000        12       8   SSX             13       9          SX

session 9: DID 0001-000D-00000002 session 8: DID 0001-000C-00000002 session 8: DID 0001-000C-00000002 session 9: DID 0001-000D-00000002 Rows waited on:
Session 8: no row
Session 9: no row

You have some inserts or updates colliding with eachother. (index t1(y) and the above will not happen)

do you have unindexed foreign keys (#1 cause of deadlocks in my experience). Use

tkyte_at_TKYTE816> column columns format a30 word_wrapped
tkyte_at_TKYTE816> column tablename format a15 word_wrapped
tkyte_at_TKYTE816> column constraint_name format a15 word_wrapped

tkyte_at_TKYTE816> select table_name, constraint_name,
  2       cname1 || nvl2(cname2,','||cname2,null) ||
  3       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  4       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  5       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  6              columns
  7    from ( select b.table_name,
  8                  b.constraint_name,
  9                  max(decode( position, 1, column_name, null )) cname1,
 10                  max(decode( position, 2, column_name, null )) cname2,
 11                  max(decode( position, 3, column_name, null )) cname3,
 12                  max(decode( position, 4, column_name, null )) cname4,
 13                  max(decode( position, 5, column_name, null )) cname5,
 14                  max(decode( position, 6, column_name, null )) cname6,
 15                  max(decode( position, 7, column_name, null )) cname7,
 16                  max(decode( position, 8, column_name, null )) cname8,
 17                  count(*) col_cnt
 18             from (select substr(table_name,1,30) table_name,
 19                          substr(constraint_name,1,30) constraint_name,
 20                          substr(column_name,1,30) column_name,
 21                          position
 22                     from user_cons_columns ) a,
 23                  user_constraints b
 24            where a.constraint_name = b.constraint_name
 25              and b.constraint_type = 'R'
 26            group by b.table_name, b.constraint_name
 27         ) cons
 28   where col_cnt > ALL
 29           ( select count(*)
 30               from user_ind_columns i
 31              where i.table_name = cons.table_name
 32                and i.column_name in (cname1, cname2, cname3, cname4,
 33                                      cname5, cname6, cname7, cname8 )
 34                and i.column_position <= cons.col_cnt
 35              group by i.index_name
 36           )

37 /

to see if you have any unindexed foreign keys

Are you using some explicit table lock commands?

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sat Jul 21 2001 - 16:24:56 CDT