Oracle锁机制

锁

• 以下几个相关的表

  SELECT * FROM v$lock;
  SELECT * FROM v$sqlarea;
  SELECT * FROM v$session;
  SELECT * FROM v$process ;
  SELECT * FROM v$locked_object;
  SELECT * FROM all_objects;
  SELECT * FROM v$session_wait;
  

• 查看被锁的表

  select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
  

• 查看那个用户那个进程照成死锁

  select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
  

• 查看连接的进程

  SELECT sid, serial#, username, osuser FROM v$session;
  

• 查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode

  SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
  s.terminal, s.logon_time, l.type
  FROM v$session s, v$lock l
  WHERE s.sid = l.sid
  AND s.username IS NOT NULL
  ORDER BY sid;
  /*这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
  任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。*/
  

• 杀掉进程 sid,serial#

  alter system kill session'210,11562';
  

解决死锁的方法

Oracle的死锁问题实际上很少见，如果发生，基本上都是不正确的程序设计造成的，经过调整后，基本上都会避免死锁的发生。 当出现session锁时，我们要及时进行处理，处理方法如下：

• 步骤一、查看被锁会话

  • 针对所有类型的锁
    LOCK视图包含所有被锁对象，被锁对象可以是表、存储过程、视图等，在不确定锁类型DDL与DML类型时，可以通过查看VLOCK视图查看，语句如下：

  SELECT L.SID         "会话ID",
         S.SERIAL#     "会话序列号",
         P.SPID        "会话进程号",
         L.TYPE        "锁类型",
         S.USERNAME    "所属用户",
         S.MACHINE     "客户端",
         O.OBJECT_NAME "被锁对象",
         O.OBJECT_TYPE "被锁对象类型",
         L.CTIME       "被锁时间(S)"
    FROM V$LOCK L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
   WHERE L.SID = S.SID
     AND L.ID1 = O.OBJECT_ID
     AND S.PADDR = P.ADDR
     AND S.SCHEMA# <> 0
     AND S.USERNAME = 'GANGMA2';
  

  • 针对DML类型锁
    V$LOCKED_OBJECT视图只包DML锁信息，查询语句如下：

  SELECT L.SESSION_ID "会话ID",
         S.SERIAL# "会话序列号",
         P.SPID "会话进程号",
         S.USERNAME "所属用户",
         S.MACHINE "客户端",
         O.OBJECT_NAME "被锁对象",
         O.OBJECT_TYPE "被锁对象类型",
         CEIL((SYSDATE - S.LOGON_TIME) * 24 * 60 * 60) "被锁时间(S)"
    FROM V$LOCKED_OBJECT L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
   WHERE L.SESSION_ID = S.SID
     AND L.OBJECT_ID = O.OBJECT_ID
     AND S.PADDR = P.ADDR
     AND S.SCHEMA# <> 0
  AND S.USERNAME = 'GANGMA2';
  

  • DDL类型锁
    DBA_DDL_LOCKS只包DML锁信息，查询语句如下：

  SELECT L.SESSION_ID "会话ID",
         S.SERIAL# "会话序列号",
         P.SPID "会话进程号",
         S.USERNAME "所属用户",
         S.MACHINE "客户端",
         S.PROGRAM "客户端程序",
         O.OBJECT_NAME "被锁对象",
         O.OBJECT_TYPE "被锁对象类型",
         CEIL((SYSDATE - S.LOGON_TIME) * 24 * 60 * 60) "被锁时间(S)"
    FROM DBA_DDL_LOCKS L, V$SESSION S, DBA_OBJECTS O, V$PROCESS P
   WHERE L.SESSION_ID = S.SID
     AND S.ROW_WAIT_OBJ# = O.OBJECT_ID
     AND S.PADDR = P.ADDR
     AND S.SCHEMA# <> 0
     AND S.USERNAME = 'GANGMA2';
  

• 步骤二、kill锁的进程

  • 数据库层面
    根据上面查询到的会话ID与会话序列号进行杀进程：

  alter system kill session 'SID,SERIAL#';
  

• 操作系统层面
  根据上面结果查询的会话进程号，在操作系统上查杀进程：
  windows平台:orakill

  orakill gangma2 3876
  

  linux平台:kill -9 SPID

  kill -9 9876
  

附录