oracle TX enqueue 分析

清疚 2022-08-13 13:59 161阅读 0赞

1.'enq: TX - row lock contention'

事物插入/更新/删除数据记录加的锁  
可以能造成此事件原因有  
唯一约束  
bitmap索引

外键约束

2.'enq: TX - allocate ITL entry'   
发生在事物请求对数据块中的记录加锁时产生  
原因：  
initrans太小，没有保留足够的itl空间  
maxtrans不足，maxtrans默认255，但实际大小受到块大小的设置，实际达不到255个  
索引块上的递归事物itl slot争用（索引分裂）  
3.'enq: TX - index contention'   
索引分裂（index split）造成的

当在基于索引的表中插入或者更新记录时，此时正好另一会话事务正在操作这个索引块正处于分裂状态（即索引块分裂）

分析锁的语句：  
查询锁表：（查询出锁的类型，对象以及会话用户等）  
select ls.osuser os\_user\_name,  
       ls.username user\_name,  
       ls.type,  
       decode(ls.type,  
              'RW',  
              'Row wait enqueue lock',  
              'TM',  
              'DML enqueue lock',  
              'TX',  
              'Transaction enqueue lock',  
              'UL',  
              'User supplied lock') lock\_type,  
       o.object\_name object,  
       decode(ls.lmode,  
              1,  
              null,  
              2,  
              'Row Share',  
              3,  
              'Row Exclusive',  
              4,  
              'Share',  
              5,  
              'Share Row Exclusive',  
              6,  
              'Exclusive',  
              null) lock\_mode,  
       o.owner,  
       ls.sid,  
       ls.serial\# serial\_num  
  from sys.dba\_objects o,  
       (select s.osuser,  
               s.username,  
               l.type,  
               l.lmode,  
               s.sid,  
               s.serial\#,  
               l.id1,  
               l.id2,  
               ld.OBJECT\_ID  
          from v$session s, v$lock l, v$locked\_object ld  
         where s.sid = l.sid  
           and s.sid = ld.SESSION\_ID) ls  
 where o.object\_id = ls.object\_id  
   and o.owner <> 'SYS'  
 order by o.owner, o.object\_name  
  
 查询发生锁等待的语句：  
 select w.waiting\_session,  
        s2.username       waiting\_user,  
        q2.sql\_text       waiting\_sql  
   from dba\_waiters w, v$session s2, v$sqlarea q2  
  where w.waiting\_session = s2.sid  
    and s2.sql\_address = q2.address;  
  
找出造成锁等待的语句以及会话sid：  
select w.holding\_session,     
         s1.username holding\_user,     
         q1.sql\_text holding\_sql     
   from dba\_waiters w,     
         v$session   s1,     
         v$open\_cursor  q1,     
         v$locked\_object l1,     
        dba\_objects o1     
  where w.holding\_session = s1.sid     
     and s1.sid = q1.sid(+)     
     and l1.session\_id = s1.sid     
     and l1.object\_id = o1.object\_id     
     and (upper(q1.sql\_text) like '%DELETE%' or upper(q1.sql\_text) like '%UPDATE%')     
     and upper(q1.sql\_text) like '%'||o1.object\_name||'%';   
  
查询锁的等待事件：  
select s.sid, s.event     
from v$session\_event s, v$enqueue\_lock l     
 where s.sid = l.sid     
 and s.event like 'enq: TX%';