锁表处理小结

对数据库锁的概念一直很模糊，到现在也没彻底搞懂锁到底是个啥，每种情况该如何处理，在itpub看到了一篇好文章，链接如下：http://www.itpub.net/forum.php?mod=viewthread&tid=1445427&extra=&highlight=&page=1 ,索性自己也按照帖子的内容作了一次实验，我是按照下面的处理方式解决的，其他的锁情况也许也应该这样处理吧，还不太敢确定。

1、建立表：

SQL>create table test(a int,b varchar2(64)) tablespace ecology;

 

2、插入数据：

SQL>begin
for i in 1..1000000 loop
insert into test values(i,'ok');
commit;
end loop;
end;
/

 

3、创建索引：

SQL>create index idx_a1 on test(a);

 

4、更新一条数据，不提交

SQL>update test set a=10000 where a=103;

5、打开另一个窗口，在线重建索引

SQL>alter index idx_a1 rebuild online;

此时观察确实是卡住不动了...

 

6、解决办法如下：

6.1 先查询出sid和serial#

SQL> select 'alter system kill session ''' || sid || ',' || serial# || ''';' as lock_event
  2    from v$session a
  3   where sid in (select sid from v$lock where block = 1);

LOCK_EVENT
--------------------------------------------------------------------------------
alter system kill session '141,186';

 

6.2 先不贸然杀掉session，查询一下是谁锁住了谁：

SQL> select s1.username,
  2         s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' ||
  3         s2.username,
  4         s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  5    from v$lock l1, v$session s1, v$lock l2, v$session s2
  6   where s1.sid = l1.sid
  7     and s2.sid = l2.sid
  8     and l1.BLOCK = 1
  9     and l2.request > 0
 10     and l1.id1 = l2.id1
 11     and l2.id2 = l2.id2;

 

USERNAME   S1.MACHINE||'(SID='||S1.SID||')ISBLOCKIN
---------- ----------------------------------------
BLOCKING_STATUS
------------------------------
CIRC       oadbbackup ( SID=141 )  is blocking CIRC
oadbbackup ( SID=145 )

结果显示session sid=141锁了sid=145

 

6.3 查询一下等待事件

SQL>select sid,event,wait_class from v$session_wait where sid in(141,145);
 SID       EVENT                                       WAIT_CLASS
---------- ---------------------------------------- ---------------
       141 SQL*Net message from client              Idle
       145 enq: TM - contention                      Application

查询证明 141是空闲（Idle）状态，而145是申请（Application）状态，145在等待141，也就是141的update test set a=10000 where a=103;语句没有提交，那么145的alter index idx_a1 rebuild online一直在等待

6.4 可以在PL/SQL develop工具、会话选项里查到141和145两个事件的SQL内容，确认没有问题后，执行第一步查出的alter system kill session '141,186';

SQL> alter system kill session '141,186';

System altered.

 

同时也可以看到重建索引的session重建成功

SQL>  alter index idx_a1 rebuild online;

Index altered.