oracle数据库锁与阻塞分析（oracle数据库锁表解决办法）

接下来分享一下锁与阻塞的相关分析内容

通常来讲，系统如果平时运行正常，突然会停止不动，多半是被阻塞（Blocked）住了。 我们可以通过v$lock 这张视图，看查看阻塞的信息。

SQL> desc v$lock;

名称 是否为空? 类型

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

ADDR RAW(4)

KADDR RAW(4)

SID NUMBER

TYPE VARCHAR2(2)

ID1 NUMBER

ID2 NUMBER

LMODE NUMBER

REQUEST NUMBER

CTIME NUMBER

BLOCK NUMBER

我们关注的比较多的是request 和 block 字段。

如果某个request列是一个非0值，那么它就是在等待一个锁。 如果block列是1，这个SID 就持有了一个锁，并且阻塞别人获得这个锁。 这个锁的类型由TYPE 字段定义。锁的模式有LMODE 字段定义，ID1和ID2 字段定义了这个锁的相关信息。ID1相同，就代表指向同一个资源。 这样就有可能有加锁者和等待者。 LMODE 的6中模式参考上面的TM锁类型表。

可以结合v$lock 和 v$session 视图来查询相关的信息：

/* Formatted on 2010/8/18 10:03:08 (QP5 v5.115.810.9015) */

SELECT sn.username,

m.SID,

sn.SERIAL#,

m.TYPE,

DECODE (m.lmode,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl.',

4,

'Share',

5,

'S/Row Excl.',

6,

'Exclusive',

lmode,

LTRIM (TO_CHAR (lmode, '990')))

lmode,

DECODE (m.request,

0,

'None',

1,

'Null',

2,

'Row Share',

3,

'Row Excl.',

4,

'Share',

5,

'S/Row Excl.',

6,

'Exclusive',

request,

LTRIM (TO_CHAR (m.request, '990')))

request,

m.id1,

m.id2

FROM v$session sn, v$lock m

WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞

OR (sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定

AND m.request = 0 AND lmode != 4

AND (id1, id2) IN

(SELECT s.id1, s.id2

FROM v$lock s

WHERE request != 0

AND s.id1 = m.id1

AND s.id2 = m.id2))

ORDER BY id1, id2, m.request;

或者

/* Formatted on 2010/8/18 0:03:02 (QP5 v5.115.810.9015) */

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

3.2 引起阻塞的几种常见情况

（1）DML语句引起阻塞

（2）外键没有创建索引

3.2.1 DML 语句

当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。4个常见的dml语句会产生阻塞

INSERT

UPDATE

DELETE

SELECT…FOR UPDATE

INSERT

Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。当2个的会话同时试图向表中插入相同的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。一个会话提交时，另一个会话将收到主键重复的错误。回滚时，被阻塞的会话将继续执行。

Update 和 Delete

UPDATE 和DELETE当执行Update和delete操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。

Select …for update

当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，

此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放(操作commit或rollback.)，产生的外在现象就是其他的操作将发生阻塞.

同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出Ora-00054:resource busy and acquire with nowait specified.

可以通过发出 select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resource busy and acquire with nowait specified.

3.2.2 外键没有创建索引

如果系统中有主，外键引用关系，并且满足一下三个条件中的任意一个，那么就应该考虑给外键字段创建索引，否则系统的性能可能会下降甚至阻塞。

（1） 主表上有频繁的删除操作

（2） 主键上有频繁的修改操作。

（3） 业务上经常会出现主表和从表做关联查询的情况。

第一和第二个条件操作的时候，主表会在从表上创建一个锁定，以保证主表主键的修改不会导致从表的数据在引用上出现问题，这是一个数据引用完整性的要求。 如果主表上经常出现这样的删除或者是对主键列进行修改的操作，或者每次操作的记录数很多，都将会造成从表长时间被锁定，而影响其他用户的正常操作。 比如主表每次删除1000行数据，它就需要扫描从表1000次，以确定每一行记录的改变都不会造成从表数据在引用上的不完整。

特别是在OLAP 系统中，从表经常会是非常巨大的表，在这种情况下，如果从表没有索引，那么查询几乎是不可想象的。

Oracle OLAP 与 OLTP 介绍

http://blog.csdn.net/tianlesoftware/archive/2010/08/08/5794844.aspx

四． Latch 和 等待

Latch 属于System Locks. 在前面的内容里里，已有相关的说明。 Latch 是Oracle 为保护内存结构而发明的一种资源。

在Oracle 复杂的内存结构中，比如SGA中，各种数据被反复从磁盘读取到内存，又被重新写回到磁盘上，如果有并发的用户做相同的事情，Oracle必须使用一种机制，来保证数据在读取的时候，只能由一个会话来完成，这种保护机制就是Latch。

并发（concurrency）：是说有超过两个以上的用户对同样的数据做修改（可能包括插入，删除和修改）。

并行（parallel）：是说将一件事情分成很多小部分，让每一部分同时执行，最后将执行结果汇总成最终结果。

可以把Latch 理解为一种轻量级的锁，它不会造成阻塞，只会导致等待。 阻塞是一种系统设计上的问题，等待是一种系统资源争用的问题。

如果发现系统中经常由于Lock 导致用户等待，这时需要考虑系统在逻辑设计上是否有问题，比如多用户对主键的删除或者修改，是否有用户使用select … for update这样的语法，外键是否创建索引的因素。 这些因素是需要结合系统的业务逻辑性来进行数据库对象设计的。

如果发现系统慢是因为很多的Latch争用，就要考虑系统及数据库自身设计上是否存在问题，比如是否使用绑定变量，是否存在热快，数据存储参数设计是否合理等因素。

导致Latch争用而等待的原因非常多，内存中很多资源都可能存在争用。 最常见的两类latch争用如下：

（1） 共享池中的Latch争用。

（2） 数据缓冲池中的latch争用。