三, 监控用户锁
数据库的锁有时候是比较耗费资源的, 特别是发生锁等待的时候, 我们必须找到发生等待的锁, 有可能的话, 杀掉该进程. 下面的语句将找到数据库中所有的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
This view lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.
3.1 oracle锁概念基础
数据库是一个多用户使用的共享资源. 当多个用户并发地存取数据时, 在数据库中就会产生多个事务同时存取同一数据的情况. 若对并发操作不加控制就可能会读取和存储不正确的数据, 破坏数据的一致性.
加锁是实现数据库并发控制的一个非常重要的技术. 当事务在对某个数据对象进行操作前, 先向系统发出请求, 对其加锁. 加锁后事务就对该数据对象有了一定的控制, 在该事务释放锁之前, 其他的事务不能对此数据对象进行更新操作.
3.1.1 oracle数据库的锁类型
根据保护的对象不同, oracle数据库锁可以分为以下几个大类:
l DML锁(data locks 数据锁), 用户保护数据的完整性;
l DDL锁(dictionary locks, 字典锁)用于保护数据库对象的结构, 如表, 索引等的结构定义;
l 内部锁和闩(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锁被释放, 其他会话才可以加锁.
3.2 oracle的TX级锁
许多对oracle不太了解的技术人员可能会以为每一个TX锁代表一条封锁的数据行, 其实不然, TX的本意是transaction(事务), 当一个事务第一次执行数据更改时, 它即获得一个TX锁, 直到事务结束, 该锁才会被释放.
在oracle的每行数据上, 都有一个标志位来表示该行数据是否被锁定. 数据行上的锁标志一旦被置位, 就表明该行数据被加X锁, oracle在数据行上没有S锁.
3.3 oracle的TM锁
表是由行组成的,当我们向某个表加锁时,一方面需要检查该锁的申请是否与原有的表级锁相容;另一方面,还要检查该锁是否与表中的每一行上的锁相容。比如一个事务要在一个表上加S锁,如果表中的一行已被另外的事务加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响。为了解决这个问题,可以在表级引入新的锁类型来表示其所属行的加锁情况,这就引出了“意向锁”的概念。
意向锁的含义是如果对一个结点加意向锁,则说明该结点的下层结点正在被加锁;对任一结点加锁时,必须先对它的上层结点加意向锁。如:对表中的任一行加锁时,必须先对它所在的表加意向锁,然后再对该行加锁。这样一来,事务对表加锁时,就不再需要检查表中每行记录的锁标志位了,系统效率得以大大提高。
3.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.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 |
3.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.
3.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';