深入浅出oracle锁---锁阻塞的分析

深入浅出oracle锁---锁阻塞的分析 

上篇深入浅出oracle锁---原理篇是以理论为主，此文章列举三个关于锁阻塞的例子，并对此作详细的说明，话不多说，直接开题。

一：外键没有索引，引起阻塞

外键没有建立索引而引起的阻塞应该是最常见到，下面举例对此详细分析

SQL> DELETE EMP WHERE EMPNO=7900;

已删除1行。

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

       10 TX      458791      1813         6         0       416         0

       10 TM       30139         0         3         0       416         0

       10 TM       30137         0         2         0       416         0

SQL> select trunc(458791/power(2,16)) ,mod(458791,power(2,16)) from dual;

TRUNC(458791/POWER(2,16)) MOD(458791,POWER(2,16))

------------------------- -----------------------

7                       39

再来查v$transaction，看看sid=10会话当前使用的undo segment和slot

SQL> select addr,xidusn,xidslot,xidsqn from v$transaction

 2 where addr in (select taddr from v$session where sid=10);

ADDR        XIDUSN   XIDSLOT    XIDSQN

-------- ---------- ---------- ----------

67BAB0CC         7        39      1813

对于tx锁，v$lock中的id1即为持有该锁的事务的回滚段号，事务槽号的组合

再来看

SQL> select owner,object_name from dba_objects where object_id in (30139,30137);

OWNER                         OBJECT_NAME

------------------------------ --------------------------------------------------------------------------------

SCOTT                         DEPT

SCOTT                         EMP

对于tm锁，id1即为持有该锁的对象的id，结合上一个查询，可以看到，该会话对30139即emp表加了lmode=3的锁，即rx锁，同时，对30137即dept加了lmode=2的锁，即rs锁。

接下来在另外一会话中执行如下语句

delete dept where1=0；

该会话被阻塞

查看v$lock

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

       10 TX      458791      1813         6         0       416         0

       10 TM       30139         0         3         0       416         1

       10 TM       30137         0         2         0       416         0

       12 TM       30139         0         0         4       372         0

       12 TM       30137         0         3         0       372         0

可以看到，另一个会话(sid=12)对30137即dept加了lmode=3的锁，即rx锁。同时请求对30139即emp表加了lmode=4的锁，即s锁(request=4)，但该请求被阻塞(s和rx不相容)，可以通过id1,id2,,block字段看到，此会话sid=10会话阻塞。

接下来，在emp的外键字段deptno建立索引

SQL> create index idx_emp_dept on emp(deptno);

Index created

按原步骤重复上面的实验

SQL> DELETE EMP WHERE EMPNO=7900;

已删除1行。

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

       10 TX      196632      2251         6         0       386         0

       10 TM       30139         0         3         0       386         0

       10 TM       30137         0         2         0       386         0

可以看到，是否建立索引，对emp表的锁情况没有影响。

在另外一个会话中执行如下语句

SQL> DELETE DEPT WHERE 1=0;

已删除0行。

该语句没有被阻塞，可以执行。看看当前的锁信息

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

       10 TX      196632      2251         6         0       435         0

       10 TM       30139         0         3         0       435         0

       10 TM       30137         0         2         0       435         0

       12 TM       30139         0         2         0         9         0

       12 TM       30137         0         3         0         9         0

可以看到，在外键上建立索引后，删除父表，对子表加的是rs锁，比没有索引加s的强度要低，这样避免了阻塞的发生，提高了系统的并行性。

二：位图索引带来的阻塞

位图索引适合建在低基数列上面，在数据仓库比较常用，如果是在并发性要求较高的oltp系统就要慎重了，不当的应用，可能会带来严重的阻塞。

SQL> create bitmap index bidx_emp_job on emp(job);

索引已创建。

SQL> DELETE EMP WHERE EMPNO=7369;

已删除1行。

在另外一会话中执行sql语句

SQL> DELETE EMP WHERE EMPNO=7876;

该语句阻塞

SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK

---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

       10 TX      524312      2362         6         0       116         1

       10 TM       30139         0         3         0       116         0

       10 TM       30137         0         2         0       116         0

       12 TX      589864      2399         6         0        98         0

       12 TM       30139         0         3         0        98         0

       12 TM       30137         0         2         0        98         0

       12 TX      524312      2362         0         4        98         0

可以看到，sid=12的会话等待sid=10持有的tx锁，虽然删除的是不同记录，但因为

empno等于7876，7369的job字段值都是’CLERK’,而我们恰恰在job字段建立了位图索引，第一个dml语句引起了对job=CLERK位图段的锁定，进而阻塞了第二个会话对该位图段其他记录的dml操作（注：如此时对job<>CLERK的记录做dml是不会阻塞的）。

三：Maxtrans带来的阻塞

在创建一个对象的时候，可以指定initrans，maxtrans参数，这二个参数指定了分配给该对象中每个block初始和最大允许并发事务数的，对每个事务，在其受影响的block内都对应一个itl，受其影响的行都会在行的lock byte（lb）位置此itl号（dump一个dml过的但还没有commit或者rollback的block可以清楚的看到），如果在一个并发性非常高的系统中，甚至超过了maxtrans的值(或者block的free space用完)，那么oracle就无法增加itl，因此过低的maxtrans同样会引起sql语句的阻塞。

SQL> drop index bidx_emp_job;

索引已丢弃。

SQL> alter table emp maxtrans 3;

表已更改。

SQL>select distinct dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from emp;

FILE_ID  BLOCK_ID

---------- ---------

1               50466

所有的记录都在1号文件的50466块，开三个session，分别删除三条不同的记录，三个session都可以正常执行，现在再开第四个session，删除一个不同的记录，该session被阻塞