目录
一、如何看锁的信息
二、释放资源
三、检查死锁脚本
如何看锁的信息
EM查看：
Performance --> Instance Locks --> all instance lock
视图查看：
V$LOCK
SQL> update emp set sal=0 where ename='SCOTT';
1 row updated.
--通过EM查看会话是sid=57 Serial Number=1997
通过视图v$session 查出scott的会话:
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
25 1378 SYSMAN
26 399 SYSMAN
40 4862 DBSNMP
44 405 SYSMAN
47 833 DBSNMP
51 393 DBSNMP
56 1424 SYSMAN
57 1997 SCOTT
58 51 SYSMAN
59 143 SYSMAN
60 7568 SCOTT
11 rows selected.
通过EM 可以看到服务进程31563
$ ps -ef|grep oracleorcl|grep 31563
oracle 31563 25915 0 07:19 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
通过会话服务进程找会话：
SQL> select PADDR from v$session where sid=57 and SERIAL#=1997;
PADDR
----------------
000000007F4A2590
PADDR RAW(4 | 8) Address of the process that owns the session 进程地址
根据进程地址去找进程号 通过v$process
SQL> select spid from v$process where addr='000000007F4A2590';
SPID
------------------------
15609
查出会话scott的会话号57 序列号1997 对应的服务进程地址15609
select p.spid,s.sid,s.serial#,s.username from v$session s,v$process p
where s.paddr=p.addr
and s.username is not null;
另开一个连接session2
conn as sysdba
delete from scott.emp where ename ='SCOTT';
挂起等待
再一次去看EM
发现被阻塞了 自己的会话sid=21 serial#=5958
谁阻塞： sid=50 Serial Number 6842 scott 原因，行级锁冲突。
再另开一个sesson3 使用pl-sql连接
再用system去连接 执行update scott.emp set sal=1 where empno =7788;
等待
被scott阻塞了
释放锁的方法：结束事务 commit 或者rollback
用户提示很慢 --> 锁冲突 管理员手工结束会话:
SQL> alter system kill session 'sid,serial#'immediate;
通过SQL找出锁再杀掉：
SQL> select SID, SERIAL#, USERNAME
from V$SESSION where SID in
(select BLOCKING_SESSION from V$SESSION)
SID SERIAL# USERNAME
---------- ---------- ------------------------------
53 1153 SCOTT
SQL> alter system kill session '53,1153' immediate ;
select SID, SERIAL#, USERNAME
from V$SESSION where SID in
(select BLOCKING_SESSION from V$SESSION)
SID SERIAL# USERNAME
---------- ---------- ------------------------------
50 59 SYSTEM
通过会话去找服务进程
SQL> select sid,serial#,paddr,username,status from v$session where sid=50 and SERIAL#=59 ;
SID SERIAL# PADDR USERNAME STATUS
---------- ---------- ---------------- ------------------------------ --------
50 59 000000007F49B3D0 SYSTEM INACTIVE
在根据内存地址去找v$process 定位 服务进程
SQL> select SPID,PNAME,USERNAME
from v$process
where ADDR='000000007F49B3D0'; 2 3
SPID PNAME USERNAME
------------------------ ----- ---------------
16902 grid
$ ps -elf |grep 16902 |grep -v grep
0 S oracle 16902 1 0 75 0 - 186343 ? 04:56 ? 00:00:00 oracleorcl (LOCAL=NO)
kill -9 16902
$ ps -elf |grep 1602 |grep -v grep
$
查看是否存在死锁：
set linesize 200
column oracle_username for a16
column os_user_name for a12
column object_name for a30
SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,
l.session_id,s.serial#, l.locked_mode,o.object_name
FROM v$locked_object l,dba_objects o,v$session s
where l.object_id = o.object_id and s.sid = l.session_id;
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;
关于oracle中的死锁，自动检测，自动维护。
