Oracle 一次 锁表 处理小记

 

            同事说测试库上的一张表被锁了。 不能执行DML 操作。 锁表的准确说法应该是阻塞。之前的一遍blog里有说明：

            锁 死锁 阻塞Latch 等待 详解

            http://blog.csdn.net/tianlesoftware/article/details/5822674

 

            找多锁表的session，并kill 掉之后，对该表的DML 操作正常。 这里在模拟一次这个问题。

 

开2个session：

session A：

SQL>select sid from v$mystat whererownum=1;

 

      SID

----------

      137

 

session B：

SQL> select sid from v$mystat whererownum=1;

 

      SID

----------

      140

 

session A 更新表T1，不commit：

SQL>  update t1 set object_id=100 where object_id=20;

2 rows updated.

 

 

session B 执行同样的操作，测试session B 会挂住：

SQL> update t1 set object_id=100 whereobject_id=20;

--在session A commit 之前，一直处于等待状态..

 

 

查看表上锁的情况：

  SELECT   sn.username,

           m.SID,

           sn.SERIAL#,

           m.TYPE,

           DECODE (m.lmode,

                   0,

                   'None',

                   1,

                   'Null',

                   2,

                   'RowShare',

                   3,

                   'RowExcl.',

                   4,

                   'Share',

                   5,

                   'S/RowExcl.',

                   6,

                   'Exclusive',

                   lmode,

                   LTRIM (TO_CHAR (lmode, '990')))

              lmode,

           DECODE (m.request,

                   0,

                   'None',

                   1,

                   'Null',

                   2,

                   'RowShare',

                   3,

                   'RowExcl.',

                   4,

                   'Share',

                   5,

                   'S/RowExcl.',

                   6,

                   'Exclusive',

                   request,

                   LTRIM (TO_CHAR (m.request, '990')))

              request,

           m.id1,

           m.id2

    FROM   v$session sn, v$lock m

   WHERE   (sn.SID = m.SID AND m.request != 0)          --存在锁请求，即被阻塞

           OR (sn.SID = m.SID         --不存在锁请求，但是锁定的对象被其他会话请求锁定

                             AND m.request = 0 AND lmode != 4

               AND (id1, id2) IN

                        (SELECT   s.id1, s.id2

                           FROM   v$lock s

                          WHERE      request != 0

                                  AND s.id1 = m.id1

                                  AND s.id2 = m.id2))

ORDER BY   id1, id2, m.request;

 

 

            这里就显示了锁的信息。 一个DML 操作需要持有2个锁。 一个3级的TM 锁和一个6级的TX锁。 TM 是共享锁，TX 是行级exclusive 锁。

 

查看v$lock, 可以验证以上锁的信息：

  select * from v$lock where sid in (137,140);

 

 

request 是申请锁资源

block：如果是1，就代表该该SID 就持有了一个锁，并且阻塞别人获得这个锁。

 

2个功能类似的查询SQL:

 

/* Formatted on2011/8/11 14:18:13 (QP5 v5.163.1008.3004) */

SELECT p.spid,

       a.sid,

       a.serial#,

       a.state,

       c.object_name,

       b.locked_mode,

       b.session_id,

       b.oracle_username,

       b.os_user_name

  FROM v$process p,

       v$session a,

       v$locked_object b,

       all_objects c

 WHERE     p.addr = a.paddr

       AND a.process = b.process

       AND c.object_id = b.object_id;

 

 

  SELECT                                                            /*+ rule */

      s  .username,

         DECODE (l.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL)

            LOCK_LEVEL,

         o.owner,

         o.object_name,

         o.object_type,

         s.sid,

         s.serial#,

         s.terminal,

         s.machine,

         s.program,

         s.osuser

  FROM   v$session s, v$lock l, dba_objects o

 WHERE   l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username IS NOT NULL

 

 

在session A 提交：

SQL> commit;

Commit complete.

 

session B 完成：

SQL> update t1 set object_id=100 whereobject_id=20;

0 rows updated.

 

            阻塞已经结束。 如果找不到对应的session 来进行commit 操作，那就只能kill session了。

            因为我这是测试库，所以也是用kill session来进行的。

 

SQL>alter  system     kill   session  'sid,serial#'; 

 

 

            此篇blog 没有什么新东西，里面的内容，以前也整理过了，随便看看，算个笔记吧。

 

 

 

 

 

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

QQ:492913789

Email:ahdba@qq.com

Blog:  http://www.cndba.cn/dave

Weibo:   http://weibo.com/tianlesoftware

Twitter:  http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)  

DBA 超级群：63306533(满);  DBA4 群： 83829929(满) DBA5群： 142216823(满) 

DBA6 群：158654907(满)  聊天 群：40132017(满)   聊天2群：69087192(满)

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请