查看oracle锁相关信息

最新推荐文章于 2021-04-15 10:12:16 发布

cuizeen198835

最新推荐文章于 2021-04-15 10:12:16 发布

阅读量79

点赞数

文章标签：数据库

oracle锁的模式

锁模式锁简称锁代码

Row Exclusive                     RX                              3
Row Shared                         RS                             2
Share                                   S                                4
Exclusive                               X                                6
Share Row Exclusive       SRX                           5
NULL                                    N/A                            0或者1

1、通过V$LOCK、V$TRANSACTION、DBA_OBJECTS来查看锁的信息、锁定的对象、锁定的事务相关信息

select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
from v$lock
where sid=???（注：此sql通过上面的锁模式、锁代码对应信息，使用decode来将v$lock中的锁代码信息显示为对应的锁模式，便于查看分析）

其中：SID session的ID号
            TYPE 锁定的级别，主要关注TX和TM
            LMODE 已经获得的锁模式
            REQUEST 正在请求的锁模式
            BLOCK    是否阻止了其他用户获得锁定，不等于0说明有堵塞

如：

sessionA：

scott@ORA10G> select distinct sid from v$mystat;

SID
----------
69

scott@ORA10G> update emp set ename='a' where empno=7900;

已更新 1 行。

在sessionB查看锁的情况：

scott@ORA10G> select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block
from v$lock
where sid=69

       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK
---------- --      ----------         ---------- ------------------- ------------------- ----------
        69 TM     317492          0 Row Exclusive       None                         0
        69 TX     655381       7756 Exclusive           None                         0

对于TM锁来说，ID1表示被锁定的对象的object_ID，ID2始终为0

故：可以通过select object_name from dba_objects where object_id=ID1;来查看锁对应的对象

sys@ORA11G> select object_name from dba_objects where object_id=317492;

OBJECT_NAME
------------------------------------------------------------------------

EMP

对于TX锁来说，ID1表示事务使用的回滚段编号以及在事务表中对应的记录编号，ID2表示该记录编号被重用的次数（wrap）

那么，拆解ID1即可得到当前锁对应的事务信息，如：

select trunc(655381/power(2,16)) as undo_blk#,bitand(655381 ,to_number('ffff','xxxx')) +
0 as slot# from dual;

UNDO_BLK# SLOT#
---------- ----------
10 21

说明：UNDO_BLK# 当前事务使用的回滚段的编号

SLOT# 事务槽号

并且与v$transaction中的信息是一样的，如：

select xidusn,xidslot,xidsqn,status from v$transaction;

XIDUSN XIDSLOT XIDSQN STATUS
---------- ---------- ---------- ----------------
10 21 7756 ACTIVE

2、通过V$ENQUEUE_LOCK、V$LOCK、V$SESSION来获得锁定队列中的session信息

说明：

V$ENQUEUE_LOCK视图中只显示那些申请锁定，但是无法获得锁定的session信息。其中的记录按照申请锁定的时间先后顺序排列，先申请锁定的session排在前面，排在前面的session将会先获得锁定。

例如：下面三个session更新相同的内容，那么后执行的2个将会等待

Session A

scott@ORA11G> select distinct sid from v$mystat;

SID
----------
69

scott@ORA11G> update emp set ename='a' where empno=7900;

已更新 1 行。

Session B

scott@ORA11G> select distinct sid from v$mystat;

SID
----------
134

scott@ORA11G> update emp set ename='a' where empno=7900;

Session C:
scott@ORA11G> select distinct sid from v$mystat;

SID
----------
198

scott@ORA11G> update emp set ename='a' where empno=7900;

此时，查询锁请求队列

select sid,type,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
request_mode
from v$enqueue_lock
where sid in(134,198)
/
      SID TY REQUEST_MODE
--------- -- -------------------
       134 TX Exclusive
       198 TX Exclusive
由此看到，session B、session C两个会话在请求TX锁，在SESSION A的锁释放之前，这2个会话将一直等待

可以通过sql查看等待时间等信息，如：

select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,
decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
c.ctime time_waited
from v$lock b, v$enqueue_lock c, v$session a
where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
order by time_held, time_waited;

BLOCKER_SID	SERIAL#	BLOCKER_USERNAME	TYPE	LOCK_MODE	TIME_HELD	WAITER_SID	REQUEST_MODE	TIME_WAITED
69	869	SCOTT	TX	Exclusive	2554	198	Exclusive	502
69	869	SCOTT	TX	Exclusive	2554	134	Exclusive	586

说明：BLOCKER_SID 导致锁等待的SID

BLOCKER_USERNAME 导致锁等待的用户

TIME_HELD 导致锁等待的session一直占用锁的时间

WAITER_SID 正在请求锁的session ID[当前有198、134]

TIME_WAITED 已经等待的时间

当前 134 等待的时间长、故会先获得锁定

此时，将SESSION A的占用的锁释放，再查看

Session A：

scott@ORA11G> select distinct sid from v$mystat;

SID
----------
69

scott@ORA11G> update emp set ename='a' where empno=7900;

已更新 1 行。

scott@ORA11G> roll
回退已完成。

BLOCKER_SID	SERIAL#	BLOCKER_USERNAME	TYPE	LOCK_MODE	TIME_HELD	WAITER_SID	REQUEST_MODE	TIME_WAITED
134	3239	SCOTT	TX	Exclusive	102	198	Exclusive	102

此时我们看到是134导致198等待{134等待的时间长、故会先获得锁定}

3、在数据库中通过V$PARAMETER来查看可以获得的TX锁定和TM锁定的总个数，可以获得的TX锁定的总个数由初始化参数transactions决定，而可以获得的TM锁定的个数则由初始化参数dml_locks决定

如：查看当前库中允许的TX和TM锁定的最大个数？

sys@ORA11G> select name,value from v$parameter where name in('transactions','dml_locks');

        NAME                           VALUE
------------------------------ ------------------------------
dml_locks                      1088
transactions                   272

可以通过v$resource_limit视图来查看详细的信息

如：

select resource_name as "NAME",current_utilization as "CURRENT",max_utilization as "MAX_
H",initial_allocation as "MAX"
from v$resource_limit
where resource_name in('transactions','dml_locks');
NAME                    CURRENT MAX_H MAX
------------ ---------- ----- --------------------
dml_locks                  2     7       1088
transactions               2     22      272

其中：CURRENT 指当前库中正在存在的TX和TM个数；MAX_H 指当前库中TX和TM曾经最大个数值；MAX 库中允许TX和TM达到的最大个数值

只要max_h没有等于max就表明够用；当然，通过v$resource_limit视图还可以查看其它相关资源的情况，以便于确认库中的某些资源是否需要修改其限制，如：select resource_name as "NAME",current_utilization as "CURRENT",max_utilization as "MAX_H",initial_allocation as "MAX" from v$resource_limit;