oracle中死锁及解锁

SELECT OBJECT_ID,SESSION_ID,SERIAL# ,a.oracle_username,a.os_user_name,a.process
 FROM V$LOCKED_OBJECT a ,
V$SESSION  WHERE a.SESSION_ID=SID;

 

解锁SQL:alter system kill session 'sid,serial#'；

 

 

--查询是table锁还是行锁

select   /*+ rule */ s.username,s.SID,s.SERIAL#,
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;

 

解锁：

alter system kill session 's.SID,s.SERIAL'

 

 

 

在存储过程中，往往因为逻辑的复杂会修改很多表记录。

1，修改a表： update a set a.1=1 where a.id=2

2，修改b表： update b set b.1=1 where b.id=2

3，修改a表： update a set a.1=1 where a.id=2

4，修改b表： update b set b.1=1 where b.id=2

 

oracle一般会自动解锁，不过上面的写法可能会照成错误，一般修改重复表时建议，如果可能就先修改a，在修改b，不要交叉处理

 

 

 

 

 

 

收集;

oracle会话被锁是经常的。但有时alter system kill session 'sid,serial#';并不能彻底的杀死会话。只能通过杀死Linux上对应的进程才行。

以前都是通过v$session里的logon_time，和ps -ef|grep oracle所列出的时间大约的定位进程。然后结束。本来想把这个写成日志。但有一个服务器存在了好几个前几天启动的进程（估计是我kill -9 weblogic进程产生的） ps -ef不能只能列出进程启动的日期，不能列出具体时间。

上网查到了2篇详细的解决方法。就转了过来：

Oracle杀死死锁进程

先查看哪些表被锁住了:

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; OWNER        OBJECT_NAME        SESSION_ID LOCKED_MODE ------------------------------ ----------------- WSSB SBDA_PSHPFTDT      22 3 WSSB_RTREPOS WB_RT_SERVICE_QUEUE_TAB      24 2 WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB      29 2 WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB      39 2 WSSB SBDA_PSDBDT        47 3 WSSB_RTREPOS WB_RT_AUDIT_DETAIL        47 3 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; USERNAME      SID      SERIAL# LOGON_TIME ------------------------------ ---------- ------- WSSB_RTACCESS        39        1178 2006-5-22 1 WSSB_RTACCESS        29        5497 2006-5-22 1

杀进程中的会话:

alter system kill session 'sid,serial#'; e.g alter system kill session '29,5497';

如果有ora-00031错误，则在后面加immediate;alter system kill session '29,5497' immediate；

如何杀死oracle死锁进程

１.查哪个过程被锁:

查V$DB_OBJECT_CACHE视图:

SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND CLOCKS!='0';

2. 查是哪一个SID,通过SID可知道是哪个SESSION:

查V$ACCESS视图:

SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';

3. 查出SID和SERIAL#：

查V$SESSION视图:

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID';

查V$PROCESS视图:

SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';

4. 杀进程：

(1)先杀ORACLE进程:

ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';

(2)再杀操作系统进程:

KILL -9 刚才查出的SPID或ORAKILL 刚才查出的SID 刚才查出的SPID。

Oracle的死锁

查询数据库死锁：

select t2.username||'      '||t2.sid||'     '||t2.serial#||'      '||t2.logon_time||'     '||t3.sql_text from v$locked_object t1,v$session t2,v$sqltext t3 where t1.session_id=t2.sid and t2.sql_address=t3.address order by t2.logon_time;

查询出来的结果就是有死锁的session了，下面就是杀掉，拿到上面查询出来的SID和SERIAL#，填入到下面的语句中：

alter system kill session 'sid,serial#';

一般情况可以解决数据库存在的死锁了，或通过session id 查到对应的操作系统进程，在Unix中杀掉操作系统的进程。

SELECT a.username,c.spid AS os_process_id,c.pid AS oracle_process_id FROM v$session a,v$process c WHERE c.addr=a.paddr and a.sid= and a.serial#= ;

然后采用kill （unix） 或 orakill（windows ）。

在Unix中：

ps -ef|grep os_process_id kill -9 os_process_id ps -ef|grep os_process_id

经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

1）查找死锁的进程：

sqlplus "/as sysdba"      (sys/change_on_install) SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#, l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

2）kill掉这个死锁的进程：

alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

3）如果还不能解决：

select pro.spid from v$session ses, v$process pro where ses.sid=XX and ses.paddr=pro.addr;

其中sid用死锁的sid替换:

exit ps -ef|grep spid

其中spid是这个进程的进程号，kill掉这个Oracle进程。

上文来源：http://www.webjx.com/htmldata/2007-05-23/1179879038.html

Oracle中Kill session的研究

作者: Eygle

link:

http://www.eygle.com/faq/Kill_Session.htm

我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:

alter system kill session ''''sid,serial#'''' ;

被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.

我们发现当一个session被kill掉以后，该session的paddr被修改，如果有多个session被kill，那么多个session 的paddr都被更改为相同的进程地址:

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null; SADDR              SID       SERIAL# PADDR       USERNAME                          STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C            11           314 542B70E8 EYGLE                             INACTIVE 542E5044            18           662 542B6D38 SYS                               ACTIVE SQL> alter system kill session ''''11,314''''; System altered. SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null; SADDR              SID       SERIAL# PADDR       USERNAME                          STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C            11           314542D6BD4 EYGLE                             KILLED 542E5044            18           662 542B6D38 SYS                               ACTIVE SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null; SADDR              SID       SERIAL# PADDR       USERNAME                          STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C            11           314 542D6BD4EYGLE                             KILLED 542E2AA4            14           397 542B7498 EQSP                              INACTIVE 542E5044            18           662 542B6D38 SYS                               ACTIVE SQL> alter system kill session ''''14,397''''; System altered. SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null; SADDR              SID       SERIAL# PADDR       USERNAME                          STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C            11           314542D6BD4 EYGLE                             KILLED 542E2AA4            14           397 542D6BD4EQSP                              KILLED 542E5044            18           662 542B6D38 SYS                               ACTIVE

在这种情况下,很多时候，资源是无法释放的,我们需要查询spid，在操作系统级来kill这些进程.

但是由于此时v$session.paddr已经改变，我们无法通过v$session和v$process关联来获得spid

那还可以怎么办呢?

我们来看一下下面的查询:

     SQL> SELECT s.username,s.status,      2     x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,      3     decode(bitand (x.ksuprflg,2),0,null,1)      4     FROM x$ksupr x,v$session s      5     WHERE s.paddr(+)=x.addr      6     and bitand(ksspaflg,1)!=0; USERNAME                          STATUS      ADDR          KSLLAPSC      KSLLAPSN KSLLASPO          KSLLID1R KS D ------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- -                                            542B44A8             0             0                          0                                   ACTIVE      542B4858             1            14 24069                    0       1                                   ACTIVE      542B4C08            26            16 15901                    0       1                                   ACTIVE      542B4FB8             7            46 24083                    0       1                                   ACTIVE      542B5368            12            15 24081                    0       1                                   ACTIVE      542B5718            15            46 24083                    0       1                                   ACTIVE      542B5AC8            79             4 15923                    0       1                                   ACTIVE      542B5E78            50            16 24085                    0       1                                   ACTIVE      542B6228           754            15 24081                    0       1                                   ACTIVE      542B65D8             1            14 24069                    0       1                                   ACTIVE      542B6988             2            30 14571                    0       1 USERNAME                          STATUS      ADDR          KSLLAPSC      KSLLAPSN KSLLASPO          KSLLID1R KS D ------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- - SYS                               ACTIVE      542B6D38             2             8 24071                    0                                            542B70E8             1            15 24081                  195 EV                                            542B7498             1            15 24081                  195 EV SYS                               INACTIVE 542B7848             0             0                          0 SYS                               INACTIVE 542B7BF8             1            15 24081                  195 EV 16 rows selected.

我们注意,红字标出的部分就是被Kill掉的进程的进程地址.

简化一点,其实就是如下概念:

 

SQL> select p.addr from v$process p where pid <> 1 2 minus 3 select s.paddr from v$session s;

ADDR -------- 542B70E8 542B7498

 

现在我们获得了进程地址，就可以在v$process中找到spid，然后可以使用Kill或者orakill在系统级来杀掉这些进程.