Oracle Enqueue（队列）

IT那活儿 2022-05-24

2170

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

Enqueue（队列）是一种共享内存结构，用于串行访问数据库资源，关联一个会话或事务，它是Oracle访问数据库对象的lock，每个试图锁住资源的会话，将会获得这个资源的Enqueue。

01

Enqueue通常用"TYPE-ID1-ID2"格式表示，其中：

"TYPE" 用两个字符串表示，如：TX、TM、UL、BL、BR、CF等。

02

Enqueue包含用户类型的锁和系统锁。

2.1 用户类型的锁

用户锁是用户应用程序获得的锁，来保护数据的完整性和用户对象的结构。

用户类型的锁等待不是Oracle代码的问题，是应用程序代码的问题。

用户类型的锁有三种类型：TX、TM、UL。

TM锁称为表级锁；
TX锁称为事务锁或行级锁；
UL是用户使用DBMS_LOCK包自定义的锁。

2.2 系统锁

系统锁包含：

CF(Controlfile Enqueue)
DX (Distributed Transaction Enqueue)
FB (Block Format Enqueue)
HW( High Water Enqueue)
JS (Job Scheduler Enqueue)
RO (Fast Object Reuse Enqueue)
SQ( Sequence Cache Enqueue)
ST( Space Transaction Enqueue)
TO( Temporary Table Object Enqueue)
TT (Tablespace Operations Enqueue)
US( Undo Segment Enqueue)等等

系统锁通常会自动释放，不用过多关注。

生产环境中TX类型的锁比较常见。

LOCK: TX "Transaction Enqueue".

TX锁是表中单个行的行锁，当某行被增删改、合并及SELECT ... FOR UPDATE时，事务就获得了该行的行锁，直到提交或回滚才释放该锁。

当TX锁竞争出现，通常会出现各种'enq: TX - '等待事件，如：

enq: TX - contention
enq: TX - row lock contention
enq: TX - allocate ITL entry
enq: TX - index contention

以enq: TX - row lock contention举例

test1表如下:

--会话1:

update u1.test1 set name='MM' where id=5
复制

--会话2:

update u1.test1 set name='zz' where id=5
复制

--DBA会话3:

SELECT sid,type,id1,id2,lmode,request FROM v$lock WHERE type='TX';
复制

查询结果：

SID TYPE ID1 ID2 LMODE REQUEST
52   TX 524288   13106   6    0
59   TX 524288   13106   0    6
复制

表明SID 59正在等待SID 52 持有的TX锁，它想以独占模式获得该锁。

03

一些常见的处理锁的实用SQL

3.1 查找blocker、waiter

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
复制

查询结果：

SESS        ID1 ID2 LMODE REQUEST TYPE
Holder: 52  524288  13106 6    0      TX
Waiter: 59  524288  13106 0    6      TX
复制

SID:52是blocker，正持有该锁;
SID:59是waiter，等待获得该锁.

3.2 查询被锁的对象、sql_id及sql语句等

SELECT DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID) SESS_STATUS,S.USERNAME,OBJECT_NAME,S.inst_id,S.SID,s.serial#, DECODE(L.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(L.LMODE)) LOCK_MODE, TRUNC(L.CTIME  3600) || ':' || TRUNC(MOD(L.CTIME, 3600)  60) || ':' || MOD(L.CTIME, 60) CTIME, S.STATUS, S.MACHINE, S.SQL_ID, Q.SQL_TEXT
FROM (SELECT /*+ NO_MERGE */(3-LEVEL) LV, INST_ID, SID, TYPE, LMODE, CTIME
FROM (SELECT /*+ NO_MERGE */A.INST_ID, A.SID, A.TYPE, A.LMODE, A.REQUEST,
CASE
WHEN REQUEST = 0 THEN ID1
END ID1,
CASE
WHEN REQUEST > 0 THEN ID1
END ID3,
A.CTIME
FROM GV$LOCK A
WHERE A.TYPE <> 'MR') START WITH REQUEST > 0 CONNECT BY PRIOR ID3 = ID1 ) L,
GV$SESSION S,
GV$PROCESS P,
dba_objects O,
GV$SQL Q
WHERE L.SID = S.SID
AND L.INST_ID = S.INST_ID
AND S.INST_ID = P.INST_ID(+)
AND S.PADDR = P.ADDR(+)
AND S.ROW_WAIT_OBJ# = O.OBJECT_ID(+)
AND S.SQL_ID = Q.SQL_ID(+)
GROUP BY DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID), S.INST_ID,S.SID,s.serial#, S.USERNAME, O.OBJECT_NAME, L.TYPE, L.LMODE, L.CTIME, S.STATUS, S.MACHINE,S.SQL_ID,Q.SQL_TEXT;
复制

查询结果：

SESS_STATUS USERNAME OBJECT_NAME INST_ID SID SERIAL# LOCK_MODE CTIME STATUS MACHINE SQL_ID SQL_TEXT
Holder: 52 U1 1 52 54450 Exclusive 0:34:15 INACTIVE WORKGROUP\DREAM
Waiter: 59 U1 TEST1 1 59 50489 None 0:34:6 ACTIVE WORKGROUP\DREAM 2btf137sycbdh update u1.test1 set name='zz' where id=5
复制

3.3 锁的处理

查询数据库锁情况:

select decode(request,0,'Holder','Waiter') req ,s.inst_id , s.sid, s.serial#,p.spid,s.status, id1, id2, lmode, request, l.type, ctime, s.sql_id, s.event#,s.event,s.last_call_et
from gv$lock l
join gv$session s on l.sid=s.sid and l.inst_id=s.inst_id
join gv$process p on s.paddr=p.addr and p.inst_id=s.inst_id
where (id1, id2, l.type) in
(select id1, id2, type from gv$lock where request>0   )
order by id1, ctime desc, request;
复制