java+oracle数据库锁_Oracle数据库锁诊断

我们都知道Oracle是一个大并发的数据库，有了锁数据库才可能实现大并发，也是应为锁Oracle大并发受到影响。

首先介绍下如何判断数据库是否有锁，来看一个视图：

v$lock

type:TM 表锁 或者DML锁

TX 行锁 事务锁

lmode:会话保持的锁模式

0 = none

1 = null

2 = Row-S(SS 行级共享锁 ，只能查询这些对象)

3 = Row-X(行级排他锁，在提交前不允许修改)

4 = Share(共享锁)

5 = S/ROW-X(共享行级排他锁)

6 = Exclusive(排他锁)

ID1,ID2 根据Type取值不同而不同。

对于type=TM表级锁或者DML锁， ID1表示被锁定表的object_id，ID2 为0 ；

对于type=TX事务锁，ID1表示高事务所占用的回滚段及事务槽，ID2表示为 环绕warp次数，即事务槽被重用的次数

REQUEST：表示会话请求锁类型

block:表示堵塞了别的会话对该锁对象的请求次数，重点关注大于 1 ，等待锁类型由lmode决定，

SQL> select sid,type, id1,id2,LMODE,REQUEST,BLOCK from v$lock where type in ('TM','TX') order by type,sid;

SID TY        ID1        ID2      LMODE    REQUEST      BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

133  TX    1996         1701        0          6         0

135  TX    1996         1701        6          0         1

133 会话持有TX锁，锁类型类0，要请求6号锁，堵塞别人0 次，

135 会话持有TX锁，所类型为6(排他锁)，堵塞过别人一次；从ID1,ID2可知  这两个会话请求的对象都一样，可见 135堵塞了133。

可以看到一点，lmode = 0 表示会话没有持有锁，但是 很有可能被别的会话给堵塞了，具体要REQUEST 字段和ID1,ID2字段

死锁查询：

select a.sid,b.sid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b where a.id1=b.id1 and a.id2=b.id2 and a.block > 1 and b.block =0;

查询死锁对象：

select ls.sid,ls.serial#,o.object_name from  (select s.osuers,s.username,l.type,s.paddr,l.lmode,s.sid,s.serial# ,l.id1,l.id2 from v$session s,v$lock l where s.sid=l.sid) ls ，v$object o

where o.object_id=ls.id1;

持有锁对象查询(不一定是死锁)：

select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id = t2.sid order by logon_time

v$locked_object

This view lists all locks acquired by every transaction on the system. It shows which sessions are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.

只包含DML的锁信息，包括回滚段和会话的信息。

测试：

对一张表在不同的会话进行update更行

SQL> select sid,type,id1,id2,lmode,request,block from  v$lock where type in ('TX

','TM');

SID TYPE        ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---- ---------- ---------- ---------- ---------- ----------

5 TX       131079        777          0          6          0

68 TM        74569          0          3          0          0

5 TM        74569          0          3          0          0

68 TX       131079        777          6          0          1

SQL>

我们可以看到 sid 为5的会话持有3号锁(R/X 行级排它锁)，请求6号锁(排它锁)，请求对象是74569   ，发生事务是 ‘ 131079 ，777 ’ 。

sid=68，持有6号锁和3号锁，加锁的对象是 74569，堵塞了别人锁请求一次。

所以问题和明显了，sid=68的会话在对象74569 上加了一个6号锁，而sid=5的会话需要请求对象74569 的一个6号锁，我们知道6号锁是一个排它锁，会话互斥，所以堵塞了1次别人锁请求。

SQL> select object_name,owner ,object_id from dba_objects where object_id=74569;

OBJECT_NAME          OWNER                 OBJECT_ID

-------------------- -------------------- ----------

TEST1                SYS                       74569

可以看到发生锁的对象是 sys.Test1表。

SQL> select sid,serial#,sql_id from v$session where sid in (select sid from v$lo

ck where type in ('TX','TM'));

SID    SERIAL# SQL_ID

---------- ---------- --------------------------

5         68 0d4ag0mv6hqcp

68         82

SQL> select sql_text,sql_id from v$sql where sql_id='0d4ag0mv6hqcp';

SQL_TEXT

--------------------------------------------------------------------------------

SQL_ID

--------------------------

update test1 set object_name='test1' where object_id=20

0d4ag0mv6hqcp

以上可以看到，堵塞的sql_text 。

这样就好解决了，找到这个sql的开发或者业务，告诉他这个sql堵塞了别的会话，排查是不是没有提交或者数据量比较大未执行完，要么回滚掉，要么强制杀死会话。

alter system kill session 'SID,SERIAL#' 或者  rollback;