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 沒有什麼新東西，裡面的內容，以前也整理過了，隨便看看，算個筆記吧。

 

 

 

 

 

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

Blog： http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

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

DBA 超級群：63306533(滿);  DBA4 群： 83829929(滿) DBA5群： 142216823(滿) 

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

--加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請