create or replace procedure do_update( p_n in number )
as
pragma autonomous_transaction;
l_rec t%rowtype;
resource_busy exception;
pragma exception_init( resource_busy, -54 );
begin
select *
into l_rec
from t
where x = p_n
for update NOWAIT;
do_update( p_n+1 );
commit;
exception
when resource_busy
then
dbms_output.put_line( 'locked out trying to select row ' || p_n );
commit;
when no_data_found
then
dbms_output.put_line( 'we finished - no problems' );
commit;
end;
/
create or replace procedure do_update( p_n in number )
as
pragma autonomous_transaction;
l_rec t%rowtype;
resource_busy exception;
pragma exception_init( resource_busy, -54 );
begin
select *
into l_rec
from t
where x = p_n
for update NOWAIT;
do_update( p_n+1 );
commit;
exception
when resource_busy
then
dbms_output.put_line( 'locked out trying to select row ' || p_n );
commit;
when no_data_found
then
dbms_output.put_line( 'we finished - no problems' );
commit;
end;
/
执行这个过程，可以得到如下结果：
tony@ORA11GR2> exec do_update(1);
locked out trying to select row 38
PL/SQL 过程已成功完成。
tony@ORA11GR2> exec do_update(1);
locked out trying to select row 38
PL/SQL 过程已成功完成。
阻塞
有5条常见的DML语句会产生阻塞：：INSERT、UPDATE、DELETE、MERGE 和SELECT FOR UPDATE。
对于一个阻塞的SELECT FOR UPDATE，只需增加NOWAIT 子句，它就不会阻塞，会报告一个ORA-00054:Resource Busy的错误。
1）阻塞的Insert
最常见的情况是，有一个带主键的表，或者表上有惟一的约束，但有两个会话试图用同样的值插入一行。如果是这样，其中一个会话就会阻塞。
此外，如果使用外键，对子表的插入可能会阻塞，因为它所依赖的父表正在创建或删除。
2）阻塞的Update 和Delete
如果有未提交的事务在这一行上执行了Update或者Delete操作，那么就会产生阻塞。
3）阻塞的Merge
Merge只是Insert和UPDATE（10g之后还有Delete）的组合。因此结果同上。
死锁
如果两个会话，每个会话都持有另一个会话想要的资源，此时就会出现死锁（deadlock）。
根据经验，导致死锁的头号原因是外键未加索引（第二号原因是表上的位图索引遭到并发更新）。
在以下两种情况下，Oracle在修改父表后会对子表加一个全表锁：
· 如果更新了父表的主键，由于外键上没有索引，所以子表会被锁住。
· 如果删除了父表中的一行，由于外键上没有索引，整个子表也会被锁住。
在 Oracle9i 及以上版本中，这些全表锁都是短期的，这意味着它们仅在DML 操作期间存在，而不是在整个事务期间都存在。即便如此，这些全表锁还是会导致很严重的锁定问题。
除了全表锁外，在下面情况下，未加索引的外键还可能带来性能问题。
· 如果有ON DELETE CASCADE，而且没有对子表加索引
例如，EMP是DEPT的子表，DELETE DEPTNO = 10 应该CASCADE（级联）至EMP[4]。如果EMP中的DEPTNO没有索引，那么删除DEPT表中的每一行时都会对EMP做一个全表扫描。这个全表扫描可能是不必要的，而且如果从父表删除多行，父表中每删除一行就要扫描一次子表。
· 从父表查询子表
例如利用DEPTNO查询EMP表是相当常见的。如果频繁地运行以下查询，没有索引会使查询速度变慢：
select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;
一般来说，仅当满足以下条件时不需要给外键加索引：
· 没有从父表删除行。
· 没有更新父表的惟一键/主键值（当心工具有时会无意地更新主键！）。
· 没有从父表联结子表
可以利用下面的脚本来查找没有加索引的外键。
column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped
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(decod