oracle的锁表和解锁

最新推荐文章于 2025-11-30 19:25:58 发布

原创最新推荐文章于 2025-11-30 19:25:58 发布 · 112 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

DateSource 专栏收录该内容

105 篇文章

订阅专栏

本文深入探讨了Oracle数据库中会话管理及锁操作的原理与实践，包括如何查询锁定对象的会话信息、锁定表的会话详情、锁定类型与模式等，并详细解释了使用killsession命令终结会话的过程及其区别。通过实例展示了在不同场景下使用killsession命令时的状态变化，以及在没有immediate参数的情况下会话状态的持久性和恢复过程。

 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 as sid,s.serial# as 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
 
--kill session语句alter system kill session'sid,serial#' immediate;
 alter system kill session'10,46004' immediate;
 --以下几个为相关表
 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;
--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid, s.serial# as serial#, l.locked_mode,l.oracle_username,
 l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
 FROM v$locked_object l, all_objects o, v$session s
 WHERE l.object_id = o.object_id
 AND l.session_id = s.sid
 ORDER BY sid, s.serial# ;
 --2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
 --比上面那段多出sql_text和action
 SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
 l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
 FROM v$sqlarea a,v$session s, v$locked_object l
 WHERE l.session_id = s.sid
 AND s.prev_sql_addr = a.address
 ORDER BY sid, s.serial#;
 --3.查出锁定表的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语句其实产生了两个锁，一个是表锁，一个是行锁。
 --杀锁命令
 alter system kill session 'sid,serial#'
 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
 --以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
 --如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
 col user_name format a10
 col owner format a10
 col object_name format a10
 col object_type format a10
  --如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
 --以下的语句可以查询到谁锁了表，而谁在等待。
 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

===================================

SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
        20       1925 INACTIVE DEDICATED          1

不加immedate，kill session后查询gv$session，该会话记录STATUS是KILLED。

SYS >alter system kill session '20,1925,@1';
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
        20       1925 KILLED   PSEUDO             1

在用户app的会话中操作，提示如下：
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-00028: your session has been killed

再查询gv$session，该会话记录已经没有了。
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

no rows selected

用app用户重新登录，再加上immediate子句操作：
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
       243      15622 INACTIVE DEDICATED          1

SYS >alter system kill session '243,15622,@1' immediate;

再查询gv$session
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

no rows selected

这是可以看到gv$session已经没有记录了。

而app用户查询，直接提示连接断开了。
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 10189
Session ID: 243 Serial number: 15622

Whether or not the session has an ongoing transaction, Oracle Database does not
recover the entire session state until the session user issues a request to the session and
receives a message that the session has been terminated.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing
transactions, release all session locks, recover the entire session state, and return
control to you immediately.

根据oracle的文档，在没有immediate字句情况下，oracle终结会话后，直到会话用户发起请求并提示会话终结后，才会恢复该会话状态。而immediate子句的作用是终结会话后，马上恢复该会话状态，所以前面例子操作后查询gv$session，已经没有该会话状态。