Oracle deadock - 原因分析

Oracle死锁原因分析

1，第一种情况：当应用程序事务设计不合理，出现逻辑冲突，2个或多个session的事务出现彼此锁住对方需要的资源，进而出现同时等待对方释放资源的情况，便会出现死锁。
    当出现这种情况，Oracle会自动回滚前面那个事务的最后一个SQL语句（不是整个事务语句），进行演变成阻塞。
2，第二种情况：一般不是死锁，只能称为阻塞，即当外键没有索引。
Session A:

create table p ( x int primary key );
create table c ( x references p );
insert into p values ( 1 );
insert into p values ( 2 );
commit;--准备一张带有外键的表c,外键的column为x,x在表c里面没有添加索引,其引用的父表为p
insert into c values ( 2 );

如果 insert子表c，会锁住c全表。
Session B:

delete from p where x = 1;

如果delete父表p，如果此时c在外键没有索引，则也会锁住c全表，即与session A锁冲突，出现阻塞。
此时只要建立了索引，则阻塞消失。
3，第三种情况：位图索引遭到并发更新。

找出未添加索引的外键约束

DBA账户

-- No index of FK from DBA
col owner for a20
col table_name for a30
col constraint_name for a30
col columns for a40
set linesize 132
set pagesize 100

select 	d.owner owner,c.table_name table_name,
		c.constraint_name constraint_name,
		c.columns columns
from
(select table_name, constraint_name,
     cname1 || nvl2(cname2,','||cname2,null) ||
     nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
     nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
     nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
            columns
from ( select b.table_name,
                b.constraint_name,
                max(decode( position, 1, column_name, null )) cname1,
                max(decode( position, 2, column_name, null )) cname2,
                max(decode( position, 3, column_name, null )) cname3,
                max(decode( position, 4, column_name, null )) cname4,
                max(decode( position, 5, column_name, null )) cname5,
                max(decode( position, 6, column_name, null )) cname6,
                max(decode( position, 7, column_name, null )) cname7,
                max(decode( position, 8, column_name, null )) cname8,
                count(*) col_cnt
          from (select  substr(table_name,1,30) table_name,
                        substr(constraint_name,1,30) constraint_name,
                        substr(column_name,1,30) column_name,
                        position
                  from  dba_cons_columns ) a,
                        dba_constraints b
          where a.constraint_name = b.constraint_name
            and b.constraint_type = 'R'
       group by b.table_name, b.constraint_name
       )  cons
where 	col_cnt > ALL
		 ( select count(*)
             from dba_ind_columns i
            where i.table_name = cons.table_name
              and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 )
              and i.column_position <= cons.col_cnt
            group by i.index_name
         )
      and table_name not in (select table_name from dba_tables where owner in ('SYS','SYSTEM','SYSMAN','MGMT_VIEW','OLAPSYS','DBSNMP'))) c
left join dba_tables d 
       on c.table_name=d.table_name order by 1;

普通用户

-- No index of FK from USER
col table_name for a30
col constraint_name for a30
col columns for a40
set linesize 132
set pagesize 100

select table_name, constraint_name,
     cname1 || nvl2(cname2,','||cname2,null) ||
     nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
     nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
     nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
            columns
  from ( select b.table_name,
                b.constraint_name,
                max(decode( position, 1, column_name, null )) cname1,
                max(decode( position, 2, column_name, null )) cname2,
                max(decode( position, 3, column_name, null )) cname3,
                max(decode( position, 4, column_name, null )) cname4,
                max(decode( position, 5, column_name, null )) cname5,
                max(decode( position, 6, column_name, null )) cname6,
                max(decode( position, 7, column_name, null )) cname7,
                max(decode( position, 8, column_name, null )) cname8,
                count(*) col_cnt
           from (select substr(table_name,1,30) table_name,
                        substr(constraint_name,1,30) constraint_name,
                        substr(column_name,1,30) column_name,
                        position
                   from user_cons_columns ) a,
                        user_constraints b
          where a.constraint_name = b.constraint_name
            and b.constraint_type = 'R'
          group by b.table_name, b.constraint_name
       ) cons
 where col_cnt > ALL
         ( select count(*)
             from user_ind_columns i
            where i.table_name = cons.table_name
              and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 )
              and i.column_position <= cons.col_cnt
         group by i.index_name
         );