Lock（二）解决Lock问题

本文介绍通过Toad、EM及SQL语句来处理数据库产生的锁。在这之前需要对v$lock和v$session这两个数据字典有一定的了解。

（一）使用Toad处理锁

(1)使用Toad的session browser查看锁情况

这里对每一个字段进行解释：

栏位名称	说明
SID	session ID，每一个session都会产生一个sid，用于标识会话
User	产生锁的数据库用户
Lock Type	锁的类型，常见的有： --DML锁 --Transaction锁（事物锁）等
Mode Held	session保持锁的模式： --none --null（NULL） --row-S（SS，行级共享锁。其它session只能查询这些数据行。SQL操作有select for update、lock for update、lock row share） --row-X（SX，行级排它锁。在提交前不允许做DML操作。SQL操作有insert、update、delete、lock row share） --share（S，共享锁。SQL操作有create index，lock share） --S/Row-X(SSX，共享行级排它锁。SQL操作有lock share row exclusive) --exclusive(X，排它锁。SQL操作有alter table、drop table、drop index、truncate table、lock exclusive等DDL操作)
Owner	被锁定的对象的属主
Object Type	被锁定的对象类型
Object Name	被锁定的对象名称
Blocking	该session是否正在阻塞其他session对资源进行访问。YES代表阻塞
Session Blocked	该会话是否正处于被阻塞的状态，打勾代表该session正在被其他session阻塞
OS User	建立该session的用户的OS名称
Machine Name	建立该session的用户的Machine名称

 

（2）使用Toad解锁

 

（二）使用SQL命令处理锁

（1）查看锁信息

select 
  se.machine,
  se.sid,
  se.serial#,
  se.seconds_in_wait,
  se.paddr,
  lo.block
from 
  v$lock lo,
  v$session se
where
 lo.sid = se.sid
and
 lo.block > 0;    --bloc>0代表这个会话阻塞了其他会话

（2）查看哪个数据库对象被锁

select 
  lo.sid,
  do.owner,
  do.object_name
from 
  v$lock lo,
  dba_objects do
where
  lo.id1 = do.object_id
and
  lo.sid = 23;    --这里23是例子，我们需要根据上一步得到的sid来查看具体对象

（3）Kill Session

alter system kill session 'sid,serial#';    --sid,serial从第1步中得到

（4）如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：

select 
  pr.spid,  --我们要的 
  se.osuser, 
  se.program
from 
  v$session se,
  v$process pr
where 
  se.paddr=pr.addr 
and 
  se.sid=24    --sid从第1步得到

（5）在OS级别Kill Process

（5.1） 在unix上，用root身份执行命令:

su - root 
#kill -9 spid    --即第步查询出的spid

（5.2）在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：

orakill sid thread

其中：
sid：表示要杀死的进程属于的实例名 ，与上面的session id不同
thread：是要杀掉的线程号，即第4步查询出的spid

例：c:>orakill orcl 12345

 

（三）模拟锁的产生及处理

（1）对scott.emp表进行行更新，但是不提交

SQL> select * from scott.emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
  …    …           …

14 rows selected

SQL> update scott.emp set job = 'SALESMAN' where empno = 7369;
 
1 row updated

 

（2）使用Toad查看锁情况，已经可以看到锁的存在

 

（3）查看EM，从EM的top activity并不能看到锁的情况

(4)查看V$lock。V$lock记录了当前数据库中存在的全部锁，锁是Oracle的一种正常的机制，但从这个视图并不能看出什么。对于用户而言，最关心的是TM和TX锁，结合上面Toad的结果，我们可以看到session id = 46的会话已经持续了454s。

SQL> select * from v$lock;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
...              ...
000000008D45B1C0 000000008D45B218         53 AE          100          0          4          0        436          0
00007F2A9960F420 00007F2A9960F480         46 TM        73201          0          3          0        454          0
000000008C105C90 000000008C105D08         46 TX       458781       1379          6          0        454          0

对于TM锁（表级锁），ID1代表的是被锁定的object_id，ID2为0，通过dba_object可以查看到正在被锁的对象的名称。

SQL> select owner,object_name,object_id,object_type from dba_objects where object_id = 73201;
 
OWNER  OBJECT_NAME   OBJECT_ID OBJECT_TYPE
------ ------------ ---------- ------------
SCOTT  EMP               73201 TABLE

（5）再来查看v$session视图，SECOND_IN_WAIT字段代表会话处于等待的时间。

SQL> select se.SADDR,se.SID,se.SERIAL#,se.PADDR,se.USERNAME,se.MACHINE,se.SECONDS_IN_WAIT  from v$session se;
 
SADDR                   SID    SERIAL# PADDR            USERNAME                       MACHINE                     SECONDS_IN_WAIT 
---------------- ---------- ---------- ---------------- ------------------------------ --------------------------- --------------- 
000000008DF74F68         46         13 000000008DC9F4D0 LIJIAMAN                       WORKGROUP\DESKTOP-TKAPD8E   1585 
000000008DF720F8         47        235 000000008DCA0510 DBSNMP                         localhost.localdomain       11 
...                     ...        ...

（6）在同一个session中执行delete操作，在新开的session中执行update操作。通过Toad，我们可以看到，在sid=46的session上，存在2个DML锁，值得一提的是，在我们对表emp进行操作时，由于其外键在dept表上，也将dept表锁住了。三个DML锁都是SX锁，即行级排它锁，46上还有一个x锁，即排它锁。

SQL> delete from scott.emp where emp.empno = 7369;
SQL> update scott.emp set emp.sal = sal + 200 where emp.empno = 7369 ;

结果如图：

 

由于执行了delete操作，将dept表也锁了起来

 

(7)此时，再去观察EM，可看到大量的Application阻塞。并且可以看到这个阻塞是有sid=54的session引起的

 

通过SQL ID查看具体执行的SQL语句

 

(8)此时查看v$lock。从红色部分可以看到， session46与session54对object id = 73201的对象产生了表级锁竞争，并且目前session46正在占用该表，导致该session阻塞了session54。

SQL> select * from v$lock;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00007F2A9960C378 00007F2A9960C3D8         54 TM        73201          0          3          0       1510          0
00007F2A9960C378 00007F2A9960C3D8         46 TM        73201          0          3          0       3707          0
00007F2A9960C378 00007F2A9960C3D8         46 TM        73199          0          3          0       1798          0
000000008C105C90 000000008C105D08         46 TX       458781       1379          6          0       3707          1

（9）Kill Session

SQL> alter system kill session '46,13';
 
System altered

至此锁解除。