Oracle中的锁相关视图介绍（TX、TM）

2022-06-01 葫芦儿的成长记录

加入收藏

oracle中锁的分类：

enqueues---队列类型的锁，通常和业务相关的通常dml操作导致，数据写入

latches--系统资源方面的锁，防止资源争用，比如内存结构，sql解析等。

比如需要访问某个资源，但是如果要访问这个资源需要拿到数据库某个资源的授权，而这个资源的授权可以理解为latch。

锁的原则：

只有被修改时，行才会被锁定
当一条语句修改了一条记录，只有这条记录上被锁定，oracle数据库不存在锁升级（行级锁升级为表级锁不存在）
当某行被修改的时候，他将阻塞别人对他的修改
当一个事务修改一行时，将在这个行上加上行锁TX，用于阻止其他事务对相同行的修改
读永远不会阻止写，但有一个例外 select for update
写永远不会阻塞读
当一行被修改后，oracle通过回滚段提供一致性读。

常见的锁

最常见的已知资源类型是TM、TX和UL资源：

TM资源（称为DML排队）是在引用表的语句执行期间获取的，以便在执行期间不会删除或更改表。
TX 锁是在事务启动其第一个更改时获取的，并一直保留到事务执行 COMMIT 或 ROLLBACK 为止。它主要用作排队机制，以便其他会话可以等待事务完成。TX 锁的锁名称（ID1 和 ID2）反映了活动事务的事务 ID。
UL资源表示由DBMSLOCK包定义的用户自定义的锁

注意：TX 锁是一个应用程序编码、设计和使用问题，只能通过使用更频繁和显式的 COMMIT 语句和任何其他次要代码更改来更改应用程序代码来修复。Oracle 支持部门无法修复 TX 锁定等待问题，只能帮助识别导致等待的对象和命令。请与开发人员合作修复代码并缓解 TX 锁定等待。

TM就是DML锁，是表级上的锁。TX是事务锁，是行级锁。在执行DML操作时，先对表加TM锁，如果加锁成功，然后再加TX锁。一般情况下，一个会话中，只会出现一个TX锁，可能有多个TM锁，这些TM所共享一个TX锁。不同的语句加TM锁的类型不同，类型就是下面说的LOCK MODE。在表级上加了TM锁也是为了防止其他会话再在表上加上排它锁(例如对表执行DDL语句)。一个表上可以加上多个TM锁、TX锁的

常用视图学习锁

v$transaction视图

第一个视图是v$transaction，就是Oracle数据库所有活动的事务数，所有活动的事务每一个活动的事务在这里有一行。
v$transaction
XIDUSN表示当前事务使用的回滚段的编号
XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号（也可以叫做槽号）
XIDSQN说明序列号，即该槽（slot）被重用的次数
STATUS说明该事务是否为活动的

实验：

LUQX@oradb>delete from t;
2 rows deleted.
select xidusn,xidslot,xidsqn,status from v$transaction;

XIDUSN:事务使用的回滚段编号
XIDSLOT:使用哪个槽位
XIDSQN: 即该槽（slot）被重用的次数
这三个唯一标识一个事务的编号。
status为active标识事务状态

v$lock视图

v$lock 记录了session已经获得的锁定以及正在请求的锁定的信息
SID说明session的ID号
TYPE说明锁的类型，主要关注TX和TM TM表示表锁或DML锁，TX表示行锁或事务锁
Oracle执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。
TM 锁包括了SS 、 SX、 S 、X 等多种模式，在数据库中用 0 －6 来表示
LMODE说明已经获得的锁定的模式，以数字编码表示
REQUEST说明正在请求的锁定的模式，以数字编码表示
BLOCK说明是否阻止了其他用户获得锁定，大于0说明是，等于0说明否
表级锁TM和事务锁TX。
对TM来讲ID1是哪个对象，
对于TX来讲ID1和ID2用来标识事务的和回滚段信息，标识事务信息。
ID1对应视图V$TRANSACTION中的XIDUSN字段（Undo segment number：事务对应的撤销段序列号）和XIDSLOT字段（Slot number：事务对应的槽位号）。
其中ID1的高16位为XIDUSN，低16位为XIDSLOT。
ID2对应视图V$TRANSACTION中的XIDSQN字段（Sequence number：事务对应的序列号）以十进制数值表示环绕（wrap）次数，即该槽（slot）被重用的次数；

LMODE有如下几种，针对TM锁

          0 ＝None；
          1＝Null ;
          2＝Row-S (SS,行级共享锁，其他SQL语句只能查询这些数据行)，sql操作有select for update、lock for update、lock row share；
          3＝Row-X (SX,行级排它锁,在提交前不允许做DML操作)，sql操作有insert、update、delete、lock row share；
          4＝Share(共享锁)，sql操作有create index、lock share；
          5＝S/Row-X (SSX,共享行级排它锁)，sql操作有lock share row exclusive；
          6＝Exclusive(排它锁)，alter table、drop table、drop index、truncate table、look exclusive等DDL

不同级别锁之间的兼容矩阵

可以看出从下往上排他性越来越强，如果对表加6级锁那么此时此表不能加锁操作

实验

模拟两个会话执行delete操作
会话1 sid144 执行delete from t;不提交
会话2 sid20 再次执行delete from t；语句的时候会处于卡住状态。
新建会话查看此时数据块的lock状态。分析以下各个值的情况。


查看当前系统锁状态
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 type in ('TX','TM') order by 1,2;

图上可以看出sid20及sid144两个会话分别再表上面添加了TM及TX锁。两个会话TM类型的锁的ID1对应的ID为锁对象的object_id,可以通过dba_objects视图查看对应的对象名称。两个会话TX类型的锁对应的ID1和ID2的值一样，是由于144会话阻塞了20会话导致的，正常来说两个事务的id1和id2是不同的。图上可以看出144持有lock_mode为exclusive-6级锁。通过block字段值为1（表示有会话被阻塞），会话20并未持有任何tx锁，而是再请求一个6级锁。所以总上看出是由于144会话阻塞了20会话。

那么ID1和ID2如何转换为回滚段相关信息呢如下步骤演示

SYS@oradb>select xidusn,xidslot,xidsqn,status from v$transaction;    
通过transaction视图查看当前活动的事务的事务信息

    XIDUSN    XIDSLOT	  XIDSQN STATUS
---------- ---------- ---------- ----------------
	 8	   11	    1807 ACTIVE          

通过如下sql转换TX锁对应的ID1值进行拆分。输入id1的值，通过如下命令id1可以转换为对应的xidunsn及xidslot。
 select trunc(524299/power(2,16)) as undo_blk#,bitand(524299,to_number('ffff','xxxx')) + 0 as slot# from dual;

另外可以看到sid20的会话正在请求锁，这时对应的id1和id2并不是事务信息，而是持有锁的事务的回归段信息。当sid144会话提交或回滚后sid20的id1和id2会变为新的值（如下图）。新值才是他的事务回滚段信息。同时sid144 后面的block值为1表示当前事务阻塞了其他事务的进行并不是代表阻塞了多少会话。

常用sql

查看当前的TX锁及wait时常

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;
sql 144持有tx锁并且持有993s 阻塞会话20 等待时间980.

2、TX锁阻塞树

 column event format a30  
  column sess format a20
  set linesize 250
  set pagesize 0
  break on id1 skip 1
select decode(request,0,'Holder:',' Waiter:') || s.inst_id || ':' || s.sid||','|| s.serial# sess,
      id1, id2, lmode, request, l.type, ctime, s.username,s.sql_id, s.event
-- ,s.service_name
  from gv$lock l, gv$session s
  where (id1, id2, l.type) in
    (select id1, id2, type from gv$lock where request>0
    )
   and l.sid=s.sid
   and l.inst_id=s.inst_id    
  order by id1, ctime desc, request 
/

可以看到holder持有锁的会话信息sid 18 serial 231 id1 id2 lmod为6 request为0 tx类型
waiter等待锁的会话信息sid 141 serial 401 id1 id2 lmod为0 request为6 tx类型

3、查看被阻塞会话执行的sql语句

查看被阻塞会话的sql执行语句
 col event for a36 
 col username for a10 
 col sql_fulltext for a80 
 SELECT g.inst_id, 
          g.sid, 
          g.serial#, 
          g.event, 
          g.username, 
          g.sql_hash_value, 
          s.sql_fulltext 
   FROM   gv$session g, 
          v$sql s 
   WHERE  g.wait_class = 'Application' 
          AND g.sql_hash_value = s.hash_value;

4、查看某个快照周期出现row lock较多的对象

ALTER SESSION SET nls_timestamp_format='DD-MON-RR HH24:MI';
SELECT P.snap_id,
  P.begin_interval_time,
  O.owner,
  O.object_name,
  O.subobject_name,
  O.object_type,
  S.row_lock_waits_delta
FROM dba_hist_seg_stat S,
  dba_hist_seg_stat_obj O,
  dba_hist_snapshot P
WHERE S.dbid               =O.dbid
AND S.ts#                  =O.ts#
AND S.obj#                 =O.obj#
AND S.dataobj#             =O.dataobj#
AND S.snap_id              =P.snap_id
AND S.dbid                 =P.dbid
AND S.instance_number      =P.instance_number
AND S.row_lock_waits_delta > 0
AND P.snap_id BETWEEN      728  AND 729   #替换为你想查的snapid
ORDER BY 1,3,4;

5、查看锁定的行信息（rowid）

查看到rowid后再根据rowid进行查看行信息。

column  object_name format a30
 SELECT do.object_name ,
       s.row_wait_obj#  ,
       s.row_wait_file# ,
       s.row_wait_block#,
       s.row_wait_row#  ,
       dbms_rowid.rowid_create ( 1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
FROM   v$session s,
       dba_objects do,
       v$datafile v
WHERE  s.blocking_session = &blocking_session                            
AND    s.row_wait_obj# = do.object_id
AND    s.row_wait_file# = v.file#;    输入持有锁的sid信息

其他视图记录

V$SESSION_WAIT
When a session is waiting on a resource, it can be found waiting on the enqueue wait event

Example:
SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
    SID identifier of session holding the lock
    P1, P2, P3 determine the resource when event = 'enqueue'
    SECONDS_IN_WAIT gives how long the wait did occurs

V$SESSION
Session information and row locking information
    SID, SERIAL# identifier of the session
    EVENT event waited on
    P1, P2, P3 determine the resource when event = 'enqueue'
    # SECONDS_IN_WAIT gives how long the wait did occurs
    LOCKWAIT address of the lock waiting, otherwise null
    ROW_WAIT_OBJ# object identified of the object we are waiting on (object_id of dba_objects)
     ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
    file_id , block_id and  row location within block of the locked row

V$LOCK
List of all the locks in the system
    SID identifier of session holding the lock
    TYPE, ID1 and ID2 determine the resource
    LMODE and REQUEST indicate which queue the session is waiting on, as follows:
    LMODE > 0, REQUEST = 0 owner
    LMODE = 0, REQUEST > 0 acquirer
    LMODE > 0, REQUEST > 0 converter
    CTIME time since current mode was converted
    BLOCK are we blocking another lock
    BLOCK = 0 non blocking
    BLOCK = 1 blocking others


DBA_LOCK or DBA_LOCKS
Formatted view on V$LOCK (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
    SESSION_ID == SID in V$LOCK
    LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
    MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
    LAST_CONVERT == CTIME of V$LOCK
    BLOCKING_OTHERS formatted value of BLOCK from V$LOCK


V$TRANSACTION_ENQUEUE
Subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)

V$ENQUEUE_LOCK
Subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)

DBA_DML_LOCKS
Subset of the V$LOCK for the DML (TM) locks only
Created via $ORACLE_HOME/rdbms/admin/catblock.sql
Same description as the DBA_LOCK view

V$LOCKED_OBJECT
Same info as DBA_DML_LOCKS, but linked with the rollback and session information
    XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
    OBJECT_ID object being locked
    SESSION_ID session id
    ORACLE_USERNAME oracle user name
    OS_USER_NAME OS user name
    PROCESS OS process id
    LOCKED_MODE lock mode


V$RESOURCE
List of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK
    TYPE, ID1 and ID2 determine the resource


DBA_DDL_LOCKS 
Has a row for each DDL lock that is being held, and one row for each outstanding request for a DDL lock.
It is subset of DBA_LOCKS
Same description as the DBA_LOCK view

DBA_WAITERS
View that retrieve information for each session waiting on a lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
    WAITING_SESSION waiting session
    HOLDING_SESSION holding session
    LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
    MODE_HELD lock type held
    MODE_REQUESTED lock type requested


DBA_BLOCKERS
View that gives the blocking sessions (created via  $ORACLE_HOME/rdbms/admin/catblock.sql)
    HOLDING_SESSION holding session