oracle 锁

时间：2010-10-19 来源：ank

1.解决死锁

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  FROM v$session
WHERE sid IN (SELECT sid
                   FROM v$lock
                   WHERE block = 1);

查看 select sid ,serial# from v$session where sid in

(select sid from v$lock where block = 1) ;

然后进行kill session

执行alter system kill session '391,48398'(sid为391);

注意：应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill.

2. 查看导致死锁的SQL

/* Formatted on 2010/8/18 0:06:11 (QP5 v5.115.810.9015) */
  SELECT s.sid, q.sql_text
    FROM v$sqltext q, v$session s
   WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的

ORDER BY piece;

3. 查看谁锁了谁

/* Formatted on 2010/8/18 0:07:49 (QP5 v5.115.810.9015) */
SELECT s1.username
         || '@'
         || s1.machine
         || ' ( SID='
         || s1.sid
         || ' ) is blocking '
         || s2.username
         || '@'
         || s2.machine
         || ' ( SID='
         || s2.sid
         || ' ) '
            AS blocking_status
  FROM v$lock l1,
         v$session s1,
         v$lock l2,
         v$session s2
WHERE s1.sid = l1.sid
         AND s2.sid = l2.sid
         AND l1.BLOCK = 1
         AND l2.request > 0
         AND l1.id1 = l2.id1
         AND l2.id2 = l2.id2;

或者
/* Formatted on 2010/8/18 0:03:46 (QP5 v5.115.810.9015) */
  SELECT /*+ rule */
        LPAD (' ', DECODE (l.xidusn, 0, 3, 0))
           || l.oracle_username
              User_name,
           o.owner,
           o.object_name,
           o.object_type,
           s.sid,
           s.serial#
    FROM v$locked_object l, dba_objects o, v$session s
   WHERE l.object_id = o.object_id AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC

可以结合v$lock 和 v$session 视图来查询相关的信息

/* Formatted on 2010/8/18 10:03:08 (QP5 v5.115.810.9015) */

  SELECT sn.username,
           m.SID,
           sn.SERIAL#,
           m.TYPE,
           DECODE (m.lmode,
                   0,
                   'None',
                   1,
                   'Null',
                   2,
                   'Row Share',
                   3,
                   'Row Excl.',
                   4,
                   'Share',
                   5,
                   'S/Row Excl.',
                   6,
                   'Exclusive',
                   lmode,
                   LTRIM (TO_CHAR (lmode, '990')))
              lmode,
           DECODE (m.request,
                   0,
                   'None',
                   1,
                   'Null',
                   2,
                   'Row Share',
                   3,
                   'Row Excl.',
                   4,
                   'Share',
                   5,
                   'S/Row Excl.',
                   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;

或者

/* Formatted on 2010/8/18 0:03:02 (QP5 v5.115.810.9015) */
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