Oracle-监控用户锁

Oracle-监控用户锁

---

数据库的锁有时候是比较耗费资源的, 特别是发生锁等待的时候, 我们必须找到发生等待的锁, 有可能的话, 杀掉该进程. 下面的语句将找到数据库中所有的DML语句产生的锁,
还可以发现, 任何DML语句其实产生了两个锁, 一个是表锁, 一个是行锁. 可以通过alter system kill session ‘sid, serial#’来杀掉会话.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT /*+ rule */
 s.username,
 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
 o.owner,
 o.object_name,
 o.object_type,
 s.sid,
 s.serial#,
 s.terminal,
 s.machine,
 s.program,
 s.osuser
  FROM v$session s, v$lock l, dba_objects o
 WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT NULL;


我们要关注TX和TM两种类型的锁, UL锁为用户自定义锁, 一般很少会定义, 基本不用关注, 其他均为系统锁, 会很快自动释放, 不用关注.
ID1    NUMBER    Lock identifier #1 (depends on type)

ID2    NUMBER    Lock identifier #2 (depends on type)
当lock_type为TM时, Id1为DMLlocked object的object id . 当lock type 为TX时, id1为usn+slot, id2为seq. 当lock_type为其它时, 不用关注.

LMODE    NUMBER    Lock mode in which the session holds the lock:
?    0 - none
?    1 - null (NULL)
?    2 - row-S (SS)
?    3 - row-X (SX)
?    4 - share (S)
?    5 - S/Row-X (SSX)
?    6 - exclusive (X)
大于0时表示当前会话正以某种模式占有该锁, 等于0时表示当前会话正在等待该锁资源, 即表示该会话被阻塞.
往往在发生TX锁时, 伴随TM锁, 比如一个sid=9会话拥有一个TM锁, 一般会拥有一个或几个TX锁, 但他们的id1和id2是不同的.
REQUEST    NUMBER    Lock mode in which the process requests the lock:
?    0 - none
?    1 - null (NULL)
?    2 - row-S (SS)
?    3 - row-X (SX)
?    4 - share (S)
?    5 - S/Row-X (SSX)
?    6 - exclusive (X)
大于0时表示会话被阻塞
CTIME    NUMBER    Time since current mode was granted
BLOCK    NUMBER    The lock is blocking another lock
该锁是否阻塞了其他的会话.
0: not blocking /*没有阻塞其他会话*/
1: blocking /*阻塞了其他会话*/
2: global /*未知情况*/


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> select * from v$lock;

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000000008FD8EDD8 000000008FD8EE30          5 RS           25          1          2          0     686887          2
000000008FD8DFC0 000000008FD8E018          5 XR            4          0          1          0     686894          2
000000008FD8DE00 000000008FD8DE58          5 RD            1          0          1          0     686894          2
000000008FD8E420 000000008FD8E478          5 CF            0          0          2          0     686891          2
000000008FD8FF70 000000008FD8FFC8         14 AE          100          0          4          0      79464          2
000000008FD8FB10 000000008FD8FB68         15 AE          100          0          4          0      81776          2
000000008CF32B18 000000008CF32B90         15 TX       589834       1078          6          0      77397          2
00002B25BA245BF8 00002B25BA245C58         15 TM        87546          0          3          0      79085          2
000000008FD8DEE0 000000008FD8DF38         64 XR            0          0          1          0     686894          2
000000008FD8E0A0 000000008FD8E0F8         69 TS            3          1          3          0     686884          2
000000008FD8E180 000000008FD8E1D8         70 AE          100          0          4          0     686878          2
000000008FD8E260 000000008FD8E2B8         70 CO            0          0          6          0     686883          2
000000008FD8F5B8 000000008FD8F610        130 DM            1          0          4          0     686885          2
000000008FD8E6C0 000000008FD8E718        130 MR            1          8          4          0     686887          2
000000008FD8E7A0 000000008FD8E7F8        130 MR            2          8          4          0     686887          2
000000008FD8E880 000000008FD8E8D8        130 MR            3          8          4          0     686887          2
000000008FD8E960 000000008FD8E9B8        130 MR            4          8          4          0     686887          2
000000008FD8EA40 000000008FD8EA98        130 MR            5          8          4          0     686887          2
000000008FD8EB20 000000008FD8EB78        130 MR          201          8          4          0     686887          2
000000008FD8EC18 000000008FD8EC70        130 MR            3          0          4          0     686886          2
000000008FD8F238 000000008FD8F290        130 MR            4          0          4          0     686886          2
000000008FD8F3F8 000000008FD8F450        130 MR            5          0          4          0     686886          2
000000008FD8F158 000000008FD8F1B0        130 MR            1          0          4          0     686886          2
000000008FD8EEB8 000000008FD8EF10        130 MR            2          0          4          0     686886          2
000000008FD8F4D8 000000008FD8F530        130 MR          201          0          4          0     686886          2
000000008FD8EF98 000000008FD8EFF0        130 RT            1          2          6          0     686886          2
000000008FD90590 000000008FD905E8        134 AE          100          0          4          0      75571          2
000000008FD8F698 000000008FD8F6F0        140 AE          100          0          4          0     686870          2
000000008FD903D0 000000008FD90428        143 AE          100          0          4          0      81027          2
000000008FD8ECF8 000000008FD8ED50        188 KD            0          0          6          0     686883          2
000000008FD8E500 000000008FD8E558        188 KT        12876          0          4          0      34635          2
000000008FD8E5E0 000000008FD8E638        191 RT            1          0          6          0     686887          2
000000008FD8F078 000000008FD8F0D0        191 RT            1          1          6          0     686886          2
000000008FD8FDB0 000000008FD8FE08        199 AE          100          0          4          0     686870          2

34 rows selected.

SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 oracle锁概念基础
数据库是一个多用户使用的共享资源. 当多个用户并发地存取数据时, 在数据库中就会产生多个事务同时存取同一数据的情况. 若对并发操作不加控制就可能会读取和存储不正确的数据, 破坏数据的一致性.
加锁是实现数据库并发控制的一个非常重要的技术. 当事务在对某个数据对象进行操作前, 先向系统发出请求, 对其加锁. 加锁后事务就对该数据对象有了一定的控制, 在该事务释放锁之前, 其他的事务不能对此数据对象进行更新操作.
3.1.1 oracle数据库的锁类型
根据保护的对象不同, oracle数据库锁可以分为以下几个大类:
?    DML锁(data locks 数据锁), 用户保护数据的完整性;
?    DDL锁(dictionary locks, 字典锁)用于保护数据库对象的结构, 如表, 索引等的结构定义;
?    内部锁和闩(internal locks and latches), 保护数据库的内部结构.
DML锁的目的在于保证并发情况下的数据完整性. 在oracle数据库中, DML锁主要包括TM锁和TX锁, 其中TM锁称为表级锁, TX锁称为事务锁或行锁.
当oracle执行DML语句时, 系统自动在所要操作的表上申请TM类型的锁. 当TM锁获得后, 系统再自动申请TX类型的锁, 并将实际锁定的数据行的锁标志位进行置位. 这样在事务锁加锁前检查TX锁相容性时就不用再逐行检查锁标志, 而只需要检查TM锁模式的相容性即可, 从而大大提供了系统的效率. TM锁包括了SS, SX, S, X等多种模式, 在数据库中用0-6来表示, 不同的SQL操作产生不同类型的TM锁.
在数据行上只有X锁(排他锁). 在oracle中, 当一个事务首先发起一个DML语句时就获得一个TX锁, 该锁保持到事务被提交或回滚. 当两个或多个会话在表的同一条记录上执行DML语句时, 第一个会话在该条记录上加锁, 其他的会话处于等待状态. 当第一个会话提交后, TX锁被释放, 其他会话才可以加锁.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2 oracle的TX级锁
许多对oracle不太了解的技术人员可能会以为每一个TX锁代表一条封锁的数据行, 其实不然, TX的本意是transaction(事务), 当一个事务第一次执行数据更改时, 它即获得一个TX锁, 直到事务结束, 该锁才会被释放.
在oracle的每行数据上, 都有一个标志位来表示该行数据是否被锁定. 数据行上的锁标志一旦被置位, 就表明该行数据被加X锁, oracle在数据行上没有S锁.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3 oracle的TM锁
表是由行组成的，当我们向某个表加锁时，一方面需要检查该锁的申请是否与原有的表级锁相容；另一方面，还要检查该锁是否与表中的每一行上的锁相容。比如一个事务要在一个表上加S锁，如果表中的一行已被另外的事务加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。
意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。如：对表中的任一行加锁时，必须先对它所在的表加意向锁，然后再对该行加锁。这样一来，事务对表加锁时，就不再需要检查表中每行记录的锁标志位了，系统效率得以大大提高。

3.1 意向锁的类型
由两种基本的锁类型（S锁、X锁），可以自然地派生出两种意向锁：
意向共享锁（Intent Share Lock，简称IS锁）：如果要对一个数据库对象加S锁，首先要对其上级结点加IS锁，表示它的后裔结点拟（意向）加S锁；
意向排它锁（Intent Exclusive Lock，简称IX锁）：如果要对一个数据库对象加X锁，首先要对其上级结点加IX锁，表示它的后裔结点拟（意向）加X锁。
另外，基本的锁类型（S、X）与意向锁类型（IS、IX）之间还可以组合出新的锁类型，理论上可以组合出4种，即：S+IS，S+IX，X+IS，X+IX，但稍加分析不难看出，实际上只有S+IX有新的意义，其它三种组合都没有使锁的强度得到提高（即：S+IS=S，X+IS=X，X+IX=X，这里的“=”指锁的强度相同）。所谓锁的强度是指对其它锁的排斥程度。
这样我们又可以引入一种新的锁的类型
共享意向排它锁（Shared Intent Exclusive Lock，简称SIX锁）：如果对一个数据库对象加SIX锁，表示对它加S锁，再加IX锁，即SIX=S+IX.例如：事务对某个表加SIX锁，则表示该事务要读整个表（所以要对该表加S锁），同时会更新个别行（所以要对该表加IX锁）。
这样数据库对象上所加的锁类型就可能有5种：即S、X、IS、IX、SIX.
具有意向锁的多粒度封锁方法中任意事务T要对一个数据库对象加锁，必须先对它的上层结点加意向锁。申请封锁时应按自上而下的次序进行；释放封锁时则应按自下而上的次序进行；具有意向锁的多粒度封锁方法提高了系统的并发度，减少了加锁和解锁的开销。

3.2 oracle的TM锁
Oracle的DML锁（数据锁）正是采用了上面提到的多粒度封锁方法，其行级锁虽然只有一种（即X锁），但其TM锁（表级锁）类型共有5种，分别称为共享锁（S锁）、排它锁（X锁）、行级共享锁（RS锁）、行级排它锁（RX锁）、共享行级排它锁（SRX锁），与上面提到的S、X、IS、IX、SIX相对应。需要注意的是，由于Oracle在行级只提供X锁，所以与RS锁（通过SELECT … FOR UPDATE语句获得）对应的行级锁也是X锁（但是该行数据实际上还没有被修改），这与理论上的IS锁是有区别的。
下表为Oracle数据库TM锁的相容矩阵（Y=Yes，表示相容的请求；N=No，表示不相容的请求；-表示没有加锁请求）：
T2
T1    S    X    RS    RX    SRX    -
S    Y    N    Y    N    N    Y
X    N    N    N    N    N    Y
RS    Y    N    Y    Y    Y    Y
RX    N    N    Y    Y    N    Y
SRX    N    N    Y    N    N    Y
-    Y    Y    Y    Y    Y    Y



+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4 监控被锁定的对象

select rpad(oracle_username, 10) o_name,
       session_id sid,       
       decode(locked_mode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row share',              
              3,
              'Row Exclusive',
              4,
              'Share',
              5,
              'Share Row Exclusive',
              6,
              'Exclusive') lock_type,       
       object_name,
       xidusn,
       xidslot,
       xidsqn
  from v$locked_object, all_objects
 where v$locked_object.object_id = all_objects.object_id;


This view lists all locks acquired by every transaction on the system.
Column    Datatype    Description
XIDUSN    NUMBER    Undo segment number 回滚段号
XIDSLOT    NUMBER    Slot number 槽号
XIDSQN    NUMBER    Sequence number 序列号
OBJECT_ID    NUMBER    Object ID being locked 被锁对象标识
SESSION_ID    NUMBER    Session ID持有锁的会话
ORACLE_USERNAME    VARCHAR2(30)    Oracle user name持有该锁的用户的oracle用户名
OS_USER_NAME    VARCHAR2(15)    OS user name持有该锁的用户的操作系统用户
PROCESS    VARCHAR2(9)    OS process ID操作系统进程号
LOCKED_MODE    NUMBER    Lock mode 锁模式


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
5 跟用户锁相关的其他动态性能视图

1.查询数据库中的锁

select * from v$lock;
select * from v$lock where block=1;

2.查询被锁的对象

select * from v$locked_object;

3.查询阻塞

查被阻塞的会话
select * from v$lock where lmode=0 and  type in ('TM','TX');

查阻塞别的会话锁
select * from v$lock where lmode>0 and  type in ('TM','TX');

4.查询数据库正在等待锁的进程

select * from v$session where lockwait is not null;

5.查询会话之间锁等待的关系
select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;
6.查询锁等待事件
select * from v$session_wait where event='enqueue';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1353784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29500582/viewspace-1353784/