【锁】Oracle锁系列

【锁】Oracle锁系列

创新互联是一家成都网站建设、成都做网站，提供网页设计，网站设计，网站制作，建网站，按需搭建网站，网站开发公司，成立与2013年是互联行业建设者，服务者。以提升客户品牌价值为核心业务，全程参与项目的网站策划设计制作，前端开发，后台程序制作以及后期项目运营并提出专业建议和思路。

1 BLOG文档结构图

【锁】Oracle锁系列

2 前言部分

2.1 导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

①锁的概念、分类、及其模拟

②查询锁的视图及视图之间的关联

③锁的参数（DML_LOCKS、DDL_LOCK_TIMEOUT）

④FOR UPDATE及FOR UPDATE OF系列

⑤ 带ONLINE和不带ONLINE创建索引的锁情况（是否阻塞DML操作）

⑥ 包或存过不能编译的解决方法

⑦ORA-08104解决方法

Tips：

①本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。

②文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。

③若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。

④在本篇BLOG中，代码输出部分一般放在一行一列的表格中。其中，需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如在下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XLHRD_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。

2.2 本文简介

有网友一直催着说发一些锁系列的文章，其实小麦苗一直对锁这块也没有彻底去研究过，今年写书里边写到了锁的内容，干脆就彻底把这一块整理了一下，现在分享给大家，若有错误，还请大家及时指正。

文章很多内容来源于网络或Concepts的内容，若有侵权还请联系小麦苗删除。

第二章锁

2.1 锁的基本概念

锁的定义：锁（lock）机制用于管理对共享资源的并发访问，用于多用户的环境下，可以保证数据库的完整性和一致性。锁是防止访问相同资源的事务之间的破坏性交互的机制。既可以是用户对象(例如表或行)，也可以是对用户不可见的系统对象(例如共享数据结构和数据字典行)。

锁的解释：当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的完整性和一致性。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制。

锁的作用：在并发事务之间防止破坏性的交互作用，不需要用户的动作，自动使用最低的限制级别，在事务处理期间保持。

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

锁（lock）是防止访问相同资源（例如表或数据行等用户对象，或内存中的共享数据结构及数据字典等对用户不可见的系统对象）的事务产生破坏性交互的机制。

在任何情况下，Oracle都能够自动地获得执行SQL语句所必须的所有锁，无需用户干预。Oracle会尽可能地减少锁产生的影响，从而最大程度地保证数据的并发访问能力，并确保数据一致性及错误恢复。同时，Oracle也支持用户手工加锁的操作。

Oracle 从来不会升级锁，但是它会执行锁转换（lock conversion）或锁提升（lock promotion）。

Alock is a mechanism that preventsdestructive interactions, which are interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data. Locks play a crucial row in maintaining database concurrency and consistency.

锁是一种机制，用来防止多个共同访问共享数据的事务之间的破坏性交互，包括不正确地更新数据或不正确地更改基础数据结构。锁在维护数据库并发性和一致性当中扮演着一个关键的角色。

2.1.1 并发和并行

并发(concurrency)和并行(parallel)。并发意思是在数据库中有超过两个以上用户对同样的数据做修改，而并行的意思就是将一个任务分成很多小的任务，让每一个小任务同时执行，最后将结果汇总到一起。所以说，锁产生的原因就是并发，并发产生的原因是因为系统和客户的需要。

2.1.2 使用锁

在单用户数据库中，锁不是必需的，因为只有一个用户在修改信息。但是，当多个用户在访问和修改数据时，数据库必须提供一种方法，以防止对同一数据进行并发修改。锁实现了以下重要的数据库需求：

v ·一致性

一个会话正在查看或更改的数据不能被其它会话更改，直到用户会话结束。

v ·完整性

数据和结构必须按正确的顺序反映对他们所做的所有更改。数据库通过其锁定机制，提供在多个事务之间的数据并发性、一致性、和完整性。锁定将自动执行，并且不需要用户操作。

执行SQL语句时，Oracle数据库自动获取所需的锁。例如，在数据库允许某个会话修改数据之前，该会话必须先锁定数据。锁给予该会话对数据的独占控制权，以便在释放该锁之前，任何其它事务都不可以修改被锁定的数据。

因为数据库的锁定机制与事务控制紧密地绑定在一起，应用程序设计人员只需要正确地定义事务，而数据库会自动管理锁定。

2.1.3 锁模式（Lock Modes）--共享和排它

Oracle数据库自动使用最低适用的限制级别，来提供最高程度的数据并发，但还能提供非常安全的数据完整性。限制级别越低、则有更多的可用数据供其他用户访问。相反，限制级别越高，则其它事务为获取其所需的锁类型就将遭受更多的限制。

在多用户的数据库系统中，Oracle使用两种模式的锁：

【锁】Oracle锁系列

2.1.4 锁的持续时间

事务内各语句获得的锁在事务执行期内有效，以防止事务间破坏性的相互干扰，例如：脏读取（dirty read），无效地更新（lost update），以及其它并发事务中具有破坏性的DDL操作。如果某个事务中的SQL语句对数据进行了修改，只有在此事务提交后开始的事务才能看到前者修改的结果。

当用户提交（commit）或撤销（undo）一个事务后，Oracle将释放此事务内各个SQL语句获得的锁。当用户在事务内回滚到某个保存点（savepoint）后，Oracle也会释放此保存点后获得的锁。只有当前没有等待被锁资源的事务才能获得可用资源的锁。等待事务不会对可用资源加锁而是继续等待，直至拥有其所等待资源的事务完成提交或回滚。

2.2 显式锁定和隐式锁定

有两种类型：显式锁定和隐式锁定。Oracle锁被自动执行，并且不要求用户干预的锁为隐式锁。对于SQL语句隐式锁是必须的，依赖被请求的动作。隐式锁定除SELECT外，对所有的SQL语句都发生。用户也可以手动锁定数据，这是显式锁定。

隐式锁定：这是Oracle中使用最多的锁。通常用户不必声明要对谁加锁，Oracle自动可以为操作的对象加锁，这就是隐式锁定。

显式锁定：用户可以使用命令明确的要求对某一对象加锁。显式锁定很少使用。

2.2.1 显式锁定

LOCK TABLE没有触发行锁，只有TM表锁。

LOCK TABLE TABLE_NAME IN ROW SHARE MODE NOWAIT; --2：RS

LOCK TABLE TABLE_NAME IN SHARE UPDATE MODE; --2：RS

LOCK TABLE TABLE_NAME IN ROW EXCLUSIVE MODE NOWAIT; --3：RX

LOCK TABLE TABLE_NAME IN SHARE MODE; --4：S

LOCK TABLE TABLE_NAME IN SHARE ROW EXCLUSIVE MODE; --5：SRX

LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE NOWAIT; --6：X

2.2.2 隐式锁定

隐式锁定：

Select * from table_name……

Insert into table_name……

Update table_name……

Delete from table_name……

Select * from table_name for update

2.3 悲观锁和乐观锁

【锁】Oracle锁系列

2.3.1 悲观锁

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

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.

会话1：

SYS@lhrdb S1> create table t_lock_lhr as select rownum as id,0 as type from dual connect by rownum <=3;

Table created.

SYS@lhrdb S1> select * from t_lock_lhr where id=2 and type =0 for update nowait;

ID TYPE

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

2 0

会话2：

SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait;

select * from t_lock_lhr where id=2 and type=0 for update nowait

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

会话1：

SYS@lhrdb S1> update t_lock_lhr set type=1 where id=2 and type=0;

1 row updated.

SYS@lhrdb S1> commit;

Commit complete.

SYS@lhrdb S1> select * from t_lock_lhr where id=2;

ID TYPE

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

2 1

会话2：

SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait;

no rows selected

2.3.2 乐观锁

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

会话1：

SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;

ID TYPE ORA_ROWSCN

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

3 0 37698547

会话2：

SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;

ID TYPE ORA_ROWSCN

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

3 0 37698547

会话1：

SYS@lhrdb S1> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id = 3;

1 row updated.

SYS@lhrdb S1> commit;

Commit complete.

SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;

ID TYPE ORA_ROWSCN

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

3 1 37698591

会话2：

SYS@lhrdb S2> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id =3;

0 rows updated.

SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;

ID TYPE ORA_ROWSCN

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

3 1 37698591

2.3.3 更新丢失问题的解决方法

更新丢失是指多个用户通过应用程序访问数据库时，由于查询数据并返回到页面和用户修改完毕点击保存按钮将修改后的结果保存到数据库这个时间段（即修改数据在页面上停留的时间）在不同用户之间可能存在偏差，从而最先查询数据并且最后提交数据的用户会把其他用户所作的修改覆盖掉。

解决方法如下：

【锁】Oracle锁系列

2.4 锁转换和锁升级（Lock Conversion and Escalation）

数据库在必要时执行锁转换。在锁转换中，数据库自动将较低限制的表锁转换为较高限制的其它锁定。一个事务在该事务中所有执行插入、更新、或删除的行上持有行独占锁。因为行锁是在最高程度限制下获得的，因此不要求锁转换，也不执行锁转换。锁转换不同于锁升级，锁升级发生在当某个粒度级别持有许多锁（例如行），数据库将其提高到更高粒度级别（例如表）。如果一个用户锁定了一个表中的许多行，则某些数据库自动将行锁升级到单个表锁。锁的数量减少了，但被锁定的东西却增加了。

Oracle数据库永远不会升级锁。锁升级极大地增加了死锁的可能性。假定一个系统尝试升级事务1中的锁，但因为事务2持有该锁，故不能成功。如果事务2在它可以继续操作之前也需要在相同的数据上进行锁升级，则将发生一个死锁。

ORACLE的锁是block里面实现的,SQLSERVER,DB2是内存里面实现的.内存实现有资源消耗问题,当内存不足会引发锁升级,但是ORACLE不会发生锁升级。

事务拥有在此事务内被插入（insert），更新（update），删除（delete）的数据行的排它行级锁（exclusive row lock）。对于数据行来说，排它行级锁已经是限制程度最高的锁，因此无需再进行锁转换（lock conversion）。

2.5 锁的分类

Oracle能够自动地选择不同类型的锁对数据并发访问进行控制，防止用户间破坏性的交互操作。Oracle将自动地为事务进行锁管理，防止其它事务对需要排它访问的资源执行操作。当事务不再需要加锁的资源并触发某个事件后，锁能够被自动地释放。

在事务执行期间，Oracle能够根据加锁的资源及需要执行的操作自动地决定锁的类型（types of lock）及对资源的限制级别（level of restrictiveness）。

V$LOCK_TYPE 该视图是对DML锁的类型的解释。

select * from V$LOCK_TYPE vwhere v.IS_USER='YES';

【锁】Oracle锁系列

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

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

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

【锁】Oracle锁系列

2.5.1 DML锁（DML Locks）

当Oracle执行DELETE,UPDATE,INSERT,SELECT FOR UPDATE DML语句时，oracle首先自动在所要操作的表上申请TM类型的锁。当TM锁获得后，再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位(lb即lock bytes)进行置位。在记录被某一会话锁定后，其它需要访问被锁定对象的会话会按先进先出的方式等待锁的释放，对于select操作而言，并不需要任何锁，所以即使记录被锁定，select语句依然可以执行，实际上，在此情况下，oracle是用到undo的内容进行一致性读来实现的。

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。DML语句能够自动地获得所需的表级锁（table-level lock）与行级锁（row-level lock）。

DML锁，也称为数据锁，确保由多个用户并发访问的数据的完整性。例如，DML锁可防止两个客户从一个在线书店购买某一本书所剩的最后一个拷贝。DML锁也可以防止多个相互冲突的DML或DDL操作产生破坏性干扰。

DML语句自动获取下列类型的锁：

n 行锁(TX)

n 表锁(TM)

2.5.1.1 行锁（Row Locks，TX）

【锁】Oracle锁系列

行级锁（row-level lock）的作用是防止两个事务同时修改相同的数据行。当一个事务需要修改一行数据时，就需对此行数据加锁。Oracle对语句或事务所能获得的行级锁的数量没有限制，Oracle也不会讲行级锁的粒度升级（lock escalation）。行级锁是粒度最精细的锁，因此行级锁能够提供最好的数据并发访问能力及数据处理能力。

Oracle同时支持多版本并发访问控制（multiversion concurrency control）及行级锁技术（row-level locking），因此用户只有在访问相同数据行时才会出现竞争，具体来说：

l 读取操作无需等待对相同数据行的写入操作。

l 写入操作无需等待对相同数据行的读取操作，除非读取操作使用了 SELECT ... FOR UPDATE语句，此读取语句需要对数据加锁。

l 写入操作只需等待并发地且针对相同数据行的其它写入操作。

提示：读取操作可能会等待对相同数据块（data block）的写入操作，这种情况只会在出现挂起的分布式事务（pending distributed transaction）时偶尔出现。

在执行下列语句时，事务需要获得被修改的每一数据行的排它行级锁（exclusive row lock）：INSERT，UPDATE，DELETE，及使用了FOR UPDATE子句的SELECT语句。

在事务被提交或回滚前，此事务拥有在其内部被修改的所有数据行的排它锁，其它事务不能对这些数据行进行修改操作。但是，如果事务由于实例故障而终止，在整个事务被恢复前，数据块级的恢复将使数据块内数据行上的锁释放。执行前面提到的 4 种SQL语句时，Oracle能自动地对行级锁进行管理。

当事务获得了某些数据行上的行级锁时，此事务同时获得了数据行所属表上的表级锁（table lock）。表级锁能够防止系统中并发地执行有冲突的DDL操作，避免当前事务中的数据操作被并发地DDL操作影响。

行级锁机制：

当一个事务开始时，必须申请一个TX锁，这种锁保护的资源是回滚段、回滚数据块。因此申请也就意味着：用户进程必须先申请到回滚段资源后才开始一个事务，才能执行DML操作。申请到回滚段后，用户事务就可以修改数据了。具体顺序如下：

1、首先获得TM锁，保护事务执行时，其他用户不能修改表结构

2、事务修改某个数据块中记录时，该数据块头部的ITL表中申请一个空闲表项，在其中记录事务项号，实际就是记录这个事务要使用的回滚段的地址（应该叫包含）

3、事务修改数据块中的某条记录时，会设置记录头部的ITL索引指向上一步申请到的表项。然后修改记录。修改前先在回滚段将记录之前的状态做一个拷贝，然后修改表中数据。

4、其他用户并发修改这条记录时，会根据记录头部ITL索引读取ITL表项内容，确认是否事务提交。

5、若没有提交，必须等待TX锁释放

从上面的机制来看，无论一个事务修改多少条记录，都只需要一个TX锁。所谓的“行级锁”其实也就是数据块头、数据记录头的一些字段，不会消耗额外的资源。从另一方面也证明了，当用户被阻塞时，不是被某条记录阻塞，而是被TX锁堵塞。也正因为这点，很多人也倾向把TX锁称为事务锁。这里可通过实验来验证所说结论。

会话1：

SQL> select * from test;

ID NAME

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

1 A

2 B

3 C

SQL> savepoint a;

Savepoint created.

SQL> update test set name='ssss' where id=2;

1 row updated.

会话2，更新同一行发生阻塞：

SQL> update test set name='ssdsdsds'where id=2;

会话1：

SQL> rollback to a;

Rollback complete.

可以看到，虽然会话1已经撤销了对记录的修改，但是会话2仍然处于等待状态这是因为会话2是被会话1的TX锁阻塞的，而不是被会话1上的行级锁阻塞(rollback to savepoint不会结束事务)。

会话3：

SQL> select username,event,sid,blocking_session from v$session where SID IN (146,159);

USERNAME EVENT SID BLOCKING_SESSION

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

HR enq: TX - row lock contention 146 159

HR SQL*Net message from client 159

会话1：

SQL> rollback;

会话2：

SQL> update test set name='ssdsdsds'where id=2;

1 row updated.

会话3：

SQL> select username,event,sid,blocking_session from v$session where username='HR';

USERNAME EVENT SID BLOCKING_SESSION

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

HR SQL*Net message from client 159

事务结束，tx锁释放，会话2update执行成功。

行锁，也称为TX 锁，是一个表中单个行上的锁。一个事务在被INSERT、UPDATE、DELETE、MERGE、或SELECT ... FOR UPDATE等语句所修改的每一行上获取一个行锁。行锁一直存在直到事务提交或回滚。行锁主要作为一种排队的机制，以防止两个事务修改相同的行。数据库始终以独占模式锁定修改的行，以便其它事务不能修改该行，直到持有锁的事务提交或回滚。行锁定提供了近乎最细粒度的锁定，并因此提供了近乎最佳的并发性和吞吐量。

如果一个事务因为数据库实例失效而终止，会先进行块级恢复以使行可用，之后进行整个事务恢复。

2.5.1.2 表锁（Table Locks，TM）

表级锁（table-level lock）的作用是对并发的DDL操作进行访问控制，例如防止在DML语句执行期间相关的表被移除。当用户对表执行DDL或DML操作时，将获取一个此表的表级锁。表级锁不会影响其他并发的DML操作。对于分区表来说，表级锁既可以针对整个表，也可以只针对某个分区。

当用户执行以下 DML 语句对表进行修改：INSERT，UPDATE，DELETE，及SELECT ... FOR UPDATE，或执行LOCK TABLE语句时，事务将获取一个表级锁。这些DML语句获取表级锁的目的有两个：首先保证自身对表的访问不受其它事务DML语句的干扰，其次阻止其它事务中和自身有冲突的DDL操作执行。任何类型的表级锁都将阻止对此表的排它DDL锁（exclusive DDL lock），从而阻止了必须具备排它DDL锁才能执行的DDL操作。例如，当一个未提交的事务拥有某个表上的锁时，此表就无法被修改定义或被移除。

表级锁具有以下几种模式：行共享（row share，RS），行排它（row exclusive，RX），共享（share，S），共享行排它（share row exclusive，SRX），及排它（exclusive，X）。各种模式的表级锁具有的限制级别决定了其是否能与其他表级锁共处于同一数据表上。

下表显示了各种语句所获得的表级锁的模式，以及此模式下被允许或禁止的操作。

ORACLE里锁有以下几种模式:

【锁】Oracle锁系列