oracle-锁秘籍--珍藏

Ora-00054:resource busy and acquire with nowait specified.
Select …for update
 当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。
需要等另一个会话结束之后才可继续执行。可以通过发出 select… for update nowait的语句来避免发生阻塞，
如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resource busy and acquire with nowait specified.
Oracle资源正忙,要求指定NOWAIT：
NOWAIT：可以不必等待，返回一个错误，更清楚的告诉你资源被锁

查询被锁表：

通过v$session，v$locked_object查看谁锁定了资源
select  t1.sid, t1.serial#, t1.username,t1.SERVICE_NAME, t1.logon_time,t1.MACHINE,t2.OS_USER_NAME,t1.TERMINAL,t1.PROGRAM,t2.ORACLE_USERNAME,t2.OS_USER_NAME,t2.LOCKED_MODE,t1.STATE
from v$session t1 , v$locked_object t2
where t1.sid = t2.session_id order by t1.logon_time

锁表查询的代码有以下的形式：
select count(*) from v$locked_object; 首先得到被锁对象的session_id
select * from v$locked_object;

查看哪个表被锁
select b.owner,b.object_name,b.OBJECT_TYPE,a.session_id,a.locked_mode,a.ORACLE_USERNAME,a.OS_USER_NAME
from v$locked_object a,dba_objects b  
where b.object_id = a.object_id;
查看是哪个session引起的

查询被锁的正在执行的sql语句
select spid, osuser, s.program,s.sql_address,q.sql_text
from v$process p, v$session s ,v$sql q
where p.addr=s.paddr
and s.sql_address=q.address
and s.sid=&SID
 union all
 select spid, osuser, s.program,s.PREV_SQL_ADDR,q.sql_text
from v$process p, v$session s ,v$sql q
where p.addr=s.paddr
and  s.PREV_SQL_ADDR=q.address
and s.sid=&SID

杀掉对应进程 执行命令：alter system kill session'1025,41';
其中1025为sid,41为serial#。
SELECT sid, serial#, username, osuser FROM v$session where sid = session_id;   --通过上面得到的session_id去取得v$session的sid和serial#，然后对该进程进行终止。
ALTER SYSTEM KILL SESSION 'sid,serial';

查询正在跑的存储过程
方法一：
select name from v$db_object_cache where type='PROCEDURE';
首先你要清楚存储过程运行的内容，然后 select * from v$locked_object 查看锁表的情况，就能够知道那些存储过程在运行，希望对你有帮助。
方法二、
查询正在执行的存储过程另外一种方法
select name from v$db_object_cache where locks > 0 and pins > 0 and type='PROCEDURE';

1、找到正在执行的存储过程的 sid ,serial#
select b.sid,b.SERIAL#
from SYS.V_$ACCESS a, SYS.V_$session b
where a.type = 'PROCEDURE'
   and (a.OBJECT like upper('%存储过程名%') or
       a.OBJECT like lower('%存储过程名%'))
   and a.sid = b.sid
   and b.status = 'ACTIVE';

kill 正在执行的存储过程
alter system kill session 'sid,SERIAL#';

 

------------------

看锁阻塞的方法是
SELECT (select username FROM v$session WHERE sid=a.sid) blocker,
      a.sid,
      'is blocking',
      (select username FROM v$session WHERE sid=b.sid) blockee,
      b.sid
  FROM v$lock a, v$lock b
WHERE a.block = 1
  AND b.request > 0
  AND a.id1 = b.id1
  AND a.id2 = b.id2
解释：request =6表示当前回话正等待一个模式为6的锁！该值为非0，就表示等待一个锁。
block =1表示这个会话正阻塞其他回话。表示这个sid持有一把锁，并阻塞其他会话。
TM锁：段级共享锁，允许同级别或更低级别的锁同时设置，但是拒绝高级别（ddl操作）的锁定请求。
TX锁：行级排它锁。
对于tm，字段id1表示加锁的段对象，可以是表或分区，此时id2一般是0
对于tx，id1和id2表示回滚段的位置

查看是否有session阻塞：
select * from v$lock where BLOCK=1;
ADDR     KADDR    SID TYPE ID1    ID2 LMODE REQUEST CTIME BLOCK
1FE0C654 1FE0C770 151 TX   131115 520 6     0       669    1
查看TX锁的SID与Serial#：
select sid,serial#,username,state,blocking_session_status,blocking_session from v$session where event like'%TX%';
SID SERIAL# USERNAME STATE   BLOCKING_SESSION_STATUS BLOCKING_SESSION
137  6      FMISMAIN WAITING  VALID                  150
临时KILL这个session:
第一：kill掉137,6，则是kill掉被block的session。
alter system kill session '137,6';
第二：kill掉150，则是kill掉之前block别人的session。
因此通过前面的137,6所查到的SQL语句，则是后面运行被等待的SQL语句，而不是阻塞别人的SQL语句。
如果要查找原因，操作如下：
查找相应的SQL语句：
select a.sql_text from v$sqlarea a,v$session b where a.sql_id=b.sql_id
select a.sql_text from v$sqlarea a,v$session b where a.sql_id=b.sql_id and b.event like'%TX%'

查询存储过程
select j.*, v.sid, v.serial#, v.PROCESS
  from V$session        v,
       DBA_JOBS_RUNNING R,
       DBA_JOBS         J   where upper(J.WHAT) like '%P_FU_B_CUSE_D_G%';

 

查询oracle正在执行的sql语句及等待事件,运行多长时间（s为单位）,cpu时间（和运行时间比对看是否产生不正常的等待）
select OSUSER, PROGRAM, USERNAME, SCHEMANAME, STATUS, b.sid,b.status,B.SQL_TEXT,a.event,a.last_call_et--运行时间，b.cpu_time--cpu运行时间,b.elapsed_time--sql执行时间
  from V$SESSION A,  V$SQL B   where A.SQL_ADDRESS = B.ADDRESS 
   AND A.SQL_HASH_VALUE = B.HASH_VALUE 
   --and OSUSER = 'lkf'  AND USERNAME = 'APPS'  

锁级别：
ORACLE里锁有以下几种模式:
0：none
1：null 空           Select
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive
或参考：
0 none
1 NULL   空S elect
2 SS(Row-S) 行级共享锁，其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share
3 SX(Row-X) 行级排它锁，在提交前不允许做DML操作   Insert、Update、Delete、Lock row share
4 S(Share) 共享锁       Create index、Lock share
5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive
6 X(Exclusive) 排它锁     Alter table、Drop table、Drop index、Truncate table 、Lock exclusive

数字越大锁级别越高, 影响的操作越多。

1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
Oracle动态性能视图学习之v$lock & v$locked_object
如果你发现session在等待一个锁，那么按如下先后顺序：

使用V$LOCK找出session持有的锁。
使用V$SESSION找出持有锁或等待锁的session执行的sql语句。
使用V$SESSION_WAIT找出什么原因导致session持有锁堵塞。
使用V$SESSION获取关于持有锁的程序和用户的更多信息。

公共锁类型
在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。
当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁，如下表1。
TX：行级锁，事务锁
在改变数据时必须是排它模式(mode 6)。每一个活动事务都拥有一个锁。它将在事务结束(commit/rollback)时释放。如果一个块包括的列被改变而没有ITL(interested transaction list)槽位(entries)，那么session将锁置于共享模式(mode 4)。当session获得块的ITL槽位时释放。当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。指出回滚段和事务表项。
按下列项以避免竞争：
避免TX－6类型竞争，需要根据您的应用而定。
避免TX－4类型竞争，可以考虑增加对象INITRANS参数值。

TM：表级锁
数据库执行任何DDL语句时必须是排它模式；例如alter table,drop table。执行像insert,update,delete这类DML语句时处于共享模式。它防止其它session对同一个对象同时执行ddl语句。任何对象拥有正被改变的数据，TM锁都将必须存在。锁指向对象。在TM队列避免竞争，可以考虑屏蔽对象表级锁，屏蔽表级锁防止对象执行任何ddl语句。

ST：空间事务锁
每个数据库(非实例)拥有一个ST锁。除了本地管理表空间，在space管理操作(新建或删除extents)时必须是排它模式。对象creation, dropping, extension, 以及truncation都处于这种锁。多数公共原因的争夺，是在磁盘排序(并非使用真正的临时表空间)或回滚段扩展或收缩。
按如下项以避免竞争：
使用真正的临时表空间(true temporary tablespaces)，利用临时文件。临时段在磁盘排序之后并不创建或删除。
使用本地管理表空间。
指定回滚段避免动态扩展和收缩，或使用自动undo management。
避免应用执行创建或删除数据库对象。

UL：用户定义锁

用户可以自定义锁。内容较多并与此节关系不大，略过。

数字越大锁级别越高, 影响的操作越多。一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。select ... from ... for update; 是2的锁。

当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select...for update操作。insert / update / delete ... ; 是3的锁。       

没有commit之前插入同样的一条记录会没有反应,因为后一个3的锁会一直等待上一个3的锁,我们必须释放掉上一个才能继续工作。

创建索引的时候也会产生3,4级别的锁。locked_mode为2,3,4不影响DML(insert,delete,update,select)操作,但DDL(alter,drop等)操作会提示ora-00054错误。有主外键约束时 update / delete ... ; 可能会产生4,5的锁。DDL语句时是6的锁。

如果出现了锁的问题,某个DML操作可能等待很久没有反应。当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num 来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。记得在数据库级别用alter system kill session 'sid,serial#';杀掉不正常的锁。

示例：

这个SQL可用来生成杀死死锁进程的SQL
 
01.select 'alter system kill session '''||a.sid||','||b.serial#||''';'
 
02.from v$lock a,all_objects c,v$session b
 
03.where a.sid > 6 and a.id1 = c.object_id and a.sid = b.sid;

 

select  t1.sid, t1.serial#, t1.username,t1.SERVICE_NAME, t1.logon_time,t1.MACHINE,t2.OS_USER_NAME,t1.TERMINAL,t1.PROGRAM,t2.ORACLE_USERNAME,t2.OS_USER_NAME,t2.LOCKED_MODE,t1.STATE
from v$session t1 , v$locked_object t2
where t1.sid = t2.session_id order by t1.logon_time;
SELECT
s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS ,a.owner,a.object_name
FROM V$LOCKED_OBJECT l,V$SESSION S ,all_objects a
WHERE l.SESSION_ID=S.SID
and l.object_id=a.OBJECT_ID;

 select * from v$lock where sid=138;
  select * from v$lock where sid=139;
 -- alter system kill session '139,9' immediate
 SELECT (select username FROM v$session WHERE sid=a.sid) blocker,
      a.sid,
      'is blocking',
      (select username FROM v$session WHERE sid=b.sid) blockee,
      b.sid
  FROM v$lock a, v$lock b
WHERE a.block = 1
  AND b.request > 0
  AND a.id1 = b.id1
  AND a.id2 = b.id2;
 
 
  select *  from v$process where addr in (select paddr from v$session where (sid||serial#)=('13825'));
  select * from v$process p ,v$session s where p.ADDR=s.PADDR and s.SID='138'