oracle锁相关知识与锁表分析

 

文章转载：https://www.cnblogs.com/zhoading/p/8547320.htm《oracle-数据库的各种-锁-详解》

1、oracle锁

Oracle的锁机制是一种轻量级的锁定机制，不是通过构建锁列表来进行数据的锁定管理，而是直接将锁作为数据块的属性，存储在数据块首部。

也就是说， 每个数据块本身就存储着自己数据块中数据的信息，这个地方叫 ITL（ Interested Transaction List）， 凡是在这个数据块上有活动的事务，它的信息就会记录在这里面供后续的操作查询，以保证事务的一致性。

在oracle数据库中，不存在真正意义上属于某个对象或数据的锁。oracle锁的信息是数据块的一个物理属性，而不是逻辑上属于某个表或某个行。

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

自动锁（ Automatic Locks）

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

自动锁分为三种：

• DML 锁
• DDL 锁
• systemlocks 

显示锁（ Manual Data Locks）

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

按锁级别分可以分为排它锁和共享锁

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

排他锁（exclusive lock，即X锁）

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

---

共享锁（share lock，即S锁）

共享锁使一个事务对特定数据库资源进行共享访问——另一事务也可对此资源进行访问或获得相同共享锁。

共享锁为事务提供高并发性，但如拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。

---

 

按操作分可以分为DML锁、DLL锁和System Locks

DML锁

DML 锁用于控制并发事务中的数据操纵，保证数据的一致性和完整性。

DML 锁主要用于保护并发情况下的数据完整性。

DML 语句能够自动地获得所需的表级锁（TM）与行级（事务）锁（TX）。

它又分为：

（ 1） TM 锁（表级锁） （ 2） TX 锁（事务锁或行级锁）

当 Oracle 执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM 锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。

这样在事务加锁前检查 TX锁相容性时就不用再逐行检查锁标志，而只需检查 TM 锁模式的相容性即可，大大提高了系统的效率。

在数据行上只有 X 锁（排他锁）。

在 Oracle 数据库中，当一个事务首次发起一个 DML 语句时就获得一个 TX 锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML 语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后， TX 锁被释放，其他会话才可以加锁。

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

TM 锁（表级锁）

TM 锁用于确保在修改表的内容时，表的结构不会改变，例如防止在 DML 语句执行期间相关的表被移除。当用户对表执行 DDL 或 DML 操作时，将获取一个此表的表级锁。

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

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

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

TM 锁类型表

 

TX 锁（ 事务锁或行级锁）

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

事务发起第一个修改时会得到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）

共享更新加锁是对一个表的一行或多行进行加锁，因而也称作行级加锁。表级加锁虽然保证了数据的一致性，但却减弱了操作数据的并行性。

行级加锁确保在用户取得被更新的行到该行进行更新这段时间内不被其它用户所修改。 因而行级锁即可保证数据的一致性又能提高数据操作的迸发性。

可通过如下的两种方式来获得行级封锁： （ 1）、执行如下的 SQL 封锁语句，以显示的方式获得：

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

（ 2）、用如下的 SELECT …FOR UPDATE 语句获得：

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 定义语句作语法分析时自动地加锁，无需用户干予。

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

• （ 1）字典操作锁： 用于对字典操作时，锁住数据字典，此封锁是独占的，从而保护任何一个时刻仅能对一个字典操作。
• （ 2） 字典定义锁： 用于防止在进行字典操作时又进行语法分析，这样可以避免在查询字典的同时改动某个表的结构。
• （ 3）表定义锁： 用于一个 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.

 

在分析系统性能时，如果看到有 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 频繁访问一些相同的数据块导致。