•   分类

    按用户和系统分可以分为自动锁和显示锁

当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。

• 自动锁分为三种：

• DML 锁

• DDL 锁

• systemlocks

显示锁（ Manual Data Locks）

某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。

排他锁（exclusive lock，即X锁）和共享锁（share lock，即S锁）

事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。

它又分为

1. TM锁（表级锁）

2. TX锁（事务锁或行级锁）

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-600的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

TM锁（表级锁）

当事务获得行锁后，此事务也将自动获得该行的表锁（共享锁），以防止其它事务进行DDL语句影响记录行的更新。

事务也可以在进行过程中获得共享锁或排他锁，只有当事务显示使用LOCL TABLE语句显示的定义一个排它锁时，事务才会获得表上的排它锁，也可使用LOCK TABLE显示的定义一个表级的共享锁。

TM锁包括了SS、SX、S、X、等多种模式，在数据库中用0-6来表示。不同的SQL操作产生不同类型的TM锁。

当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。

事务发起个修改时会得到TX锁（事务锁），而且会一直持有这个锁，直至事务执行提交（COMMIT)或回滚（ROLLBACK)

对用户的数据操纵， Oracle 可以自动为操纵的数据进行加锁，但如果有操纵授权，则为满足并发操纵的需要另外实施加锁。

DML 锁可由一个用户进程以显式的方式加锁，也可通过某些 SQL 语句隐含方式实现。这部分属于 Manual Data Locks。

原理：一个事务要修改块中的数据，必须获得该块中的一个itl，通过itl和undo segment header中的transaction table，可以知道事务是否处于活动阶段。事务在修改块时（其实就是在修改行）会检查行中row header中的标志位，如果该标志位为0（该行没有被活动的事务锁住），就把该标志位修改为事务在该块获得的itl的序号，这样当前事务就获得了对记录的锁定，然后就可以修改行数据了，这也就是oracle行锁实现的原理。

DML 锁有如下三种加锁方式：

• 共享锁方式（ SHARE）

• 独占锁方式（ EXCLUSIVE）

• 共享更新锁（ SHARE UPDATE） 
  其中： 
  SHARE， EXCLUSIVE 用于 TM 锁（表级锁） 
  SHARE UPDATE 用于 TX 锁（ 行级锁）

共享方式的表级锁（ Share）

共享方式的表级锁是对表中的所有数据进行加锁，该锁用于保护查询数据的一致性，防止其它用户对已加锁的表进行更新。

其它用户只能对该表再施加共享方式的锁，而不能再对该表施加独占方式的锁，共享更新锁可以再施加，但不允许持有共享更新封锁的进程做更新。

共享该表的所有用户只能查询表中的数据，但不能更新。

共享方式的表级锁只能由用户用 SQL 语句来设置.

语句格式如下：

LOCK TABLE <表名>[,<表名>]... IN SHARE MODE [NOWAIT]

执行该语句，对一个或多个表施加共享方式的表封锁。

当指定了选择项NOWAIT，若该锁暂时不能施加成功，则返回并由用户决定是进行等待，还是先去执行别的语句。

持有共享锁的事务，在出现如下之一的条件时，便释放其共享锁：

• A、执行 COMMIT 或 ROLLBACK 语句。

• B、退出数据库（ LOG OFF）。

• C、程序停止运行。

共享方式表级锁常用于一致性查询过程，即在查询数据期间表中的数据不发生改变。

独占方式表级锁（ Exclusive）

独占方式表级锁是用于加锁表中的所有数据，拥有该独占方式表封锁的用户，既可以查询该表，又可以更新该表，其它的用户不能再对该表施加任何枷锁（包括共享、独占或共享更新封锁）

其他用户虽然不能更新该表，但可以查询该表

独占方式的表封锁可通过如下的SQL语句来显示地获得：

LOCK TABLE <表名>[,<表名>].... IN EXCLUSIVE MODE [NOWAIT]

独占方式的表级锁也可以在用户执行 DML 语句 INSERT、UPDATE、DELETE时隐含获得。

拥有独占方式表封锁的事务，在出现如下条件之一时，便释放该封锁：

• （ 1）、执行 COMMIT 或 ROLLBACK 语句。

• （ 2）、退出数据库（ LOG OFF）

• （ 3）、程序停止运行。

独占方式封锁通常用于更新数据，当某个更新事务涉及多个表时，可减少发生死锁.

共享更新加锁方式（ Share Update）

LOCK TABLE < 表 名 >[,< 表 名 >].... IN SHARE UPDATE MODE[NOWAIT]

SELECT <列名 >[,<列名 >]...FROM <表名 > WHERE <条件 > FORUPDATE OF <列名>[,<列名>].....[NOWAIT]

一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．

如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。

行锁永远是独占方式锁。

当出现如下之一的条件，便释放共享更新锁：

• （ 1）、执行提交（ COMMIT）语句；

• （ 2）、退出数据库（ LOG OFF）

• （ 3）、程序停止运行。

执行 ROLLBACK 操作不能释放行锁。

DLL锁( dictionary locks)

DDL 锁用于保护数据库对象的结构，如表、索引等的结构定义。

DDL 锁又可以分为：

• 排它 DDL 锁、

• 共享 DDL 锁、

• 分析锁

排它 DDL 锁

创建、修改、删除一个数据库对象的 DDL 语句获得操作对象的 排它锁。

如使用 alter table 语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它 DDL 锁

共享 DDL 锁

需在数据库对象之间建立相互依赖关系的 DDL 语句通常需共享获得 DDL锁。

如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时该事务就获得了引用表的共享 DDL 锁。

分析锁

ORACLE 使用共享池存储分析与优化过的 SQL 语句及 PL/SQL 程序，使运行相同语句的应用速度更快。

一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。

分析锁是一种独特的 DDL 锁类型， ORACLE 使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。

当一个事务修改或删除了共享池持有分析锁的数据库对象时， ORACLE 使共享池中的对象作废，下次在引用这条SQL/PLSQL 语 句时， ORACLE 重新分析编译此语句。

DDL 级加锁也是由 ORACLE RDBMS 来控制，它用于保护数据字典和数据定义改变时的一致性和完整性。它是系统在对 SQL 定义语句作语法分析时自动地加锁，无需用户干予。

字典/语法分析加锁共分三类：

• 字典操作锁： 
  用于对字典操作时，锁住数据字典，此封锁是独占的，从而保护任何一个时刻仅能对一个字典操作。

• 字典定义锁： 
  用于防止在进行字典操作时又进行语法分析，这样可以避免在查询字典的同时改动某个表的结构。

• 表定义锁： 
  用于一个 SQL 语句正当访问某个表时，防止字典中与该表有关的项目被修改。

悲观封锁和乐观封锁

悲观封锁

锁在用户修改之前就发挥作用：

Select ..for update [nowait] Select * from tab1 for update

用户发出这条命令之后，oracle将会对返回集中的数据建立行级封锁，以防止其他用户的修改。

如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。

• 1：对返回结果集进行update或delete操作会发生阻塞。 

• 2：对该表进行ddl操作将会报：Ora-00054:resource busy and acquire with nowait specified.

  

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

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

悲观的缺陷是，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并 发访问性不好.

会话A：

在这里新开一个plsql窗口模拟会话A

--建表create table test (name varchar2(20));--新增数据insert into test values('liuxiansen');--提交数据commit ;--使用for update方式获取排他行级锁select * from test where name='liuxiansen' for update ;

乐观封锁

乐观的认为数据在select出来到update数据并提交的这段时间数据不会被更改。乐观锁多个会话可以同时操作数据。这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定，是存在一种可能被其他用户更改的可能。因此Oracle仍然建议是用悲观封锁，因为这样会更安全。

比较常见的方式使用版本列来，每次更新时都和旧版本的数据比较。

System Locks

oracle使用不同类型的系统锁来保护内部数据库和内存结构.

这些机制是用户无法访问的。

死锁

当两个用户希望持有对方的资源时就会发生死锁.

即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚。

场景

1：用户 1 对 A 表进行 Update，没有提交。 
2：用户 2 对 B 表进行 Update，没有提交。

此时双反不存在资源共享的问题。

3：如果用户 2 此时对 A 表作 update,则会发生阻塞，需要等到用户一的事物结束。 
4：如果此时用户 1 又对 B 表作 update，则产生死锁。此时 Oracle 会选择其中一个用户进行会滚，使另一个用户继续执行操作。

起因分析

Oracle 的死锁问题实际上很少见，如果发生，基本上都是不正确的程序设计造成的，经过调整后，基本上都会避免死锁的发生。

在 Oracle 系统中能自动发现死锁，并选择代价小的，即完成工作量少的事务予以撤消，释放该事务所拥有的全部锁，记其它的事务继续工作下去。

从系统性能上考虑，应该尽可能减少资源竞争，增大吞吐量，因此用户在给并发操作加锁时，应注意以下几点：

• 1、 对于 UPDATE 和 DELETE 操作，应只锁要做改动的行，在完成修改后立即提交。

• 2、 当多个事务正利用共享更新的方式进行更新，则不要使用共享封锁，而应采用共享更新锁，这样其它用户就能使用行级锁，以增加并行性。

• 3、 尽可能将对一个表的操作的并发事务施加共享更新锁，从而可提高并行性。

• 4、 在应用负荷较高的期间，不宜对基础数据结构（表、索引、簇和视图）进行修改

死锁后的解决办法

如果死锁不能自动释放，就需要我们手工的 kill session

生成Kill Session语句

1. 查看有无死锁对象，如有 kill session

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"  FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

alter system kill session '646,3953';

注意：应当注意对于 sid 在 100 以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以 kill

查看导致死锁的 SQL

SELECT s.sid, q.sql_textFROM v$sqltext q, v$session sWHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是步查询出来的ORDER BY piece;

查看谁锁了谁

SELECT s1.username|| '@'|| s1.machine|| ' ( SID='|| s1.sid|| ' ) is blocking '|| s2.username|| '@'|| s2.machine|| ' ( SID='|| s2.sid|| ' ) 'AS blocking_statusFROM v$lock l1,v$session s1,v$lock l2,v$session s2WHERE s1.sid = l1.sidAND s2.sid = l2.sidAND l1.BLOCK = 1AND l2.request > AND l1.id1 = l2.id1AND l2.id2 = l2.id2;

或者

SELECT LPAD (' ', DECODE (l.xidusn, , 3, ))|| l.oracle_usernameUser_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#FROM v$locked_object l, dba_objects o, v$session sWHERE l.object_id = o.object_id AND l.session_id = s.sidORDER BY o.object_id, xidusn DESC;

锁和阻塞

概念

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

SQL> desc v$lockName Type   Nullable Default Comments ------- ----------- -------- ------- -------- ADDR RAW(8) Y                        KADDR RAW(8) Y                        SID  NUMBER Y                        TYPE VARCHAR2(2) Y                        ID1  NUMBER Y                        ID2  NUMBER Y                        LMODE NUMBER Y                        REQUEST NUMBER Y                        CTIME NUMBER Y                        BLOCK NUMBER Y

我们关注的比较多的是 request 和 block 字段。 
如果某个 request 列是一个非 0 值，那么它就是在等待一个锁。如果 block 列是1，这个 SID 就持有了一个锁，并且阻塞别人获得这个锁。

这个锁的类型由 TYPE字段定义。锁的模式有 LMODE 字段定义， ID1 和 ID2 字段定义了这个锁的相关信息。

ID1 相同，就代表指向同一个资源。这样就有可能有加锁者和等待者。

LMODE 的 6 中模式参考上面的TM锁类型表

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

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 != )      --存在锁请求，即被阻塞    OR (sn.SID = m.SID       --不存在锁请求，但是锁定的对象被其他会话请求锁定       AND m.request =  AND lmode != 4 AND       (id1, id2) IN (SELECT s.id1, s.id2                         FROM v$lock s                        WHERE request !=                           AND s.id1 = m.id1                          AND s.id2 = m.id2)) ORDER BY id1, id2, m.request;

或者

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;

引起阻塞的几种常见情况

（ 1） DML 语句引起阻塞 
（ 2）外键没有创建索引

1.DML 语句引起阻塞

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

4 个常见的 dml 语句会产生阻塞:

• （ 1） INSERT

• （ 2） UPDATE

• （ 3） DELETE

• （ 4） 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.

2.外键没有创建索引

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

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

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

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

和第二个条件操作的时候，主表会在从表上创建一个锁定，以保证主表主键的修改不会导致从表的数据在引用上出现问题，这是一个数据引用完整性的要求。

如果主表上经常出现这样的删除或者是对主键列进行修改的操作，或者每次操作的记录数很多，都将会造成从表长时间被锁定，而影响其他用户的正常操作。

比如主表每次删除 1000 行数据，它就需要扫描从表 1000 次，以确定每一行记录的改变都不会造成从表数据在引用上的不完整。

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

Latch

latch概述

Latch属于 System Lock, 用于保护 SGA区中一共享数据结构的一种串行化锁定机制。

Latch 的实现是与操作系统相关的，尤其和一个进程是否需要等待一个latch、需要等待多长时间有关.

Latch 是 Oracle 提供的轻量级锁资源， 是一种能够极快地被获取和释放的锁，能快速，短时间的锁定资源，

Latch用于防止多个并发进程同时修改访问某个共享资源， 它只工作在 SGA 中， 通常用于保护描述 buffer cache 中 block 的数据结构。

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

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

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

与每个 latch 相联系的还有一个清除过程，当持有 latch 的进程成为死进程时，该清除过程就会被调用。

Latch 还具有相关级别，用于防止死锁，一旦一个进程在某个级别上得到一个 latch，它就不可能再获得等同或低于该级别的 latch。

Latch 不会造成阻塞，只会导致等待。阻塞是一种系统设计上的问题，等待是一种系统资源争用的问题。

spin概述

比如数据缓存中的某个块要被读取，我们会获得这个块的 latch， 这个过程叫做 spin，另外一个进程恰好要修改这个块，他也要 spin 这个块，此时他必须等待，当前一个进程释放 latch 后才能 spin 住，然后修改， 如果多个进程同时请求的话，他们之间将出现竞争，没有一个入队机制，一旦前面进程释放所定，后面的进程就蜂拥而上，没有先来后到的概念， 并且这一切都发生的非常快，因为Latch 的特点是快而短暂。

SPIN 与休眠（ sleep） 
Oracle 选择了 spin，让进程继续占有 CPU，运行一些空指令，之后继续请求，继续 spin，直到达到_spin_count 值，这时会放弃 CPU，进行短暂的休眠，再继续刚才的动作。

进程休眠的时间也是存在算法的.休眠的阀值限制由隐含参数_max_exponential_sleep控制， 默认是 2 秒.

如果当前进程已经占用了别的 Latch，则他的休眠时间不会太长(过长会引起别的进程的 Latch 等待)，此时的休眠大时间有隐含参数_max_sleep_holding_latch 决定， 默认是 4 厘秒.

总之，Latch 获取的流程：请求-SPIN-休眠-请求-SPIN-休眠 … … 占用。

Latch 和 Lock

从某种意义上说， Latch 是内存中的资源锁，数据库对象(表，索引等)的锁叫Lock。

Latch 和 Lock 的区别:

• （ 1） . Latch 是对内存数据结构提供互斥访问的一种机制，而 Lock 是以不同的模式来套取共享资源对象，各个模式间存在着兼容或排斥，从这点看出， Latch的访问，包括查询也是互斥的，任何时候，只能有一个进程能 spin 住内存的某一块，幸好这个过程是相当的短暂，否则系统性能将没的保障，从 9I 开始，允许多个进程同时查询相同的内存块。

• （ 2） . Latch 只作用于内存中，他只能被当前实例访问，而 Lock 作用于数据库对象，在 RAC 体系中实例间允许 Lock 检测与访问

• （ 3） . Latch 是瞬间的占用，释放， Lock 的释放需要等到事务正确的结束，他占用的时间长短由事务大小决定

• （ 4） . Latch 是非入队的，而 Lock 是入队的

• （ 5） . Latch 不存在死锁，而 Lock 中存在。

Latch 争用

如果发现系统中经常由于 Lock 导致用户等待

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

如果发现系统慢是因为很多的 Latch 争用

就要考虑系统及数据库自身设计上是否存在问题，比如是否使用绑定变量，是否存在热快，数据存储参数设计是否合理等因素。 
导致 Latch 争用而等待的原因非常多，内存中很多资源都可能存在争用。

常见的两类 latch 争用如下： 
（ 1） 共享池中的 Latch 争用。 
（ 2）数据缓冲池中的 latch 争用。

共享池中的 Latch 争用

共享池中如果存在大量的 SQL 被反复分析，就会造成很大的 Latch 争用和长时间的等待， 常见的现象就是没有绑定变量。

常见的集中一共享池里的 Latch 是 library cache。

可以通过一下 SQL 来查询：

select * from v$latchname where name like 'library cache%';

在分析系统性能时，如果看到有 library cache 这样的 Latch 争用，就可以断定是共享池中出现了问题，这种问题基本是由 SQL 语句导致的，比如没有绑定变量 或者一些存储过程被反复分析。

资源的争用可以通过如下 SQL 来查看

 select event,count(*) from v$session_wait group by event;

数据缓冲池中的 latch 争用

访问频率非常高的数据块被称为热快（ Hot Block），当很多用户一起去访问某几个数据块时，就会导致一些 Latch 争用.

常见的 latch 争用有：

• （ 1） buffer busy waits

• （ 2） cache buffer chain

这两个 Latch 的争用分别发生在访问数据块的不同时刻。

产生这些 Latch 争用的直接原因是太多的会话去访问相同的数据块导致热快问题， 造成热快的原因可能是数据库设置导致或者重复执行的 SQL 频繁访问一些相同的数据块导致。

                码字不易，喜欢就个赞吧

原文链接：https://mp.weixin.qq.com/s/rKUbKQscS3NFbSUoVYUUnQ