Oracle 一次 锁表 处理小记

司寇3851

　　
　　同事说测试库上的一张表被锁了。 不能执行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
　　WHERErequest != 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>altersystem 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表空间和数据文件的关系，否则拒绝申请