Oracle锁系列一文全搞定

0    342    1

Tags:

👉 本文共约26752个字,系统预计阅读时间或需101分钟。

前言部分

导读和注意事项

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

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

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

③ 锁的参数(DML_LOCKS、DDL_LOCK_TIMEOUT)

④ FOR UPDATE及FOR UPDATE OF系列

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

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

⑦ ORA-08104解决方法

本文简介

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

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

锁的基本概念

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

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

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

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

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

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

Oracle 从来不会升级锁,但是它会执行锁转换(lock conversion)或锁提升(lock promotion)。

A lock is a mechanism that prevents destructive 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.

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

并发和并行

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

使用锁

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

  • 一致性

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

  • 完整性

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

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

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

锁模式(Lock Modes)--共享和排它

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

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

Oracle锁系列一文全搞定

锁的持续时间

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

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

显式锁定和隐式锁定

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

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

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

显式锁定

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

隐式锁定

隐式锁定:

悲观锁和乐观锁

Oracle锁系列一文全搞定

悲观锁

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

用户发出这条命令之后,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.

乐观锁

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

更新丢失问题的解决方法

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

解决方法如下:

Oracle锁系列一文全搞定

锁转换和锁升级(Lock Conversion and Escalation)

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

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

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

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

锁的分类

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

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

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

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锁系列一文全搞定

Oracle锁系列一文全搞定

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语句自动获取下列类型的锁:

  • 行锁(TX)
  • 表锁(TM)

行锁(Row Locks,TX)

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

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

  • 读取操作无需等待对相同数据行的写入操作。
  • 写入操作无需等待对相同数据行的读取操作,除非读取操作使用了 SELECT ... FOR UPDATE 语句,此读取语句需要对数据加锁。
  • 写入操作只需等待并发地且针对相同数据行的其它写入操作。

提示:读取操作可能会等待对相同数据块(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锁称为事务锁。

这里可通过实验来验证所说 结论。

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

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

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

表锁(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锁系列一文全搞定

锁的兼容模式如下表所示:

Oracle锁系列一文全搞定

表锁,也称为TM锁,当一个表被INSERT、UPDATE、DELETE、MERGE、带FOR UPDATE子句的SELECT等修改时,由相关事务获取该锁。DML操作需要表锁来为事务保护DML对表的访问,并防止可能与事务冲突的DDL操作。

表锁可能以下列模式之一持有:

Oracle锁系列一文全搞定

行共享(RS) Row Share (RS)

这种锁也被称为子共享表锁(SS,subshare table lock),表示在表上持有锁的事务在表中有被锁定的行,并打算更新它们。行共享锁是限制最少的表级锁模式,提供在表上最高程度的并发性。

实验

ROW SHARE模式允许同时访问被锁定的表,但是禁止用户以排它方式锁定整个表。ROW SHARE与SHARE UPDATE相同,只是为了兼容早期的Oracle版本。对应lmode2,row-S (SS)。

版本:11.2.0.4

Oracle锁系列一文全搞定

由BLOCK列可以看到sid为6的会话阻塞了一个会话,这里其实就是114,而114正在请求模式为6的锁。将2个会话提交后继续测试:

行独占表锁 Row Exclusive Table Lock (RX)

这种锁也被称为子独占表锁(SX,subexclusive table lock),通常表示持有锁的事务已更新了表行或发出了SELECT...FOR UPDATE。一个SX锁允许其它事务并发地查询、插入、更新、删除、或锁定在同一个表中的其它行。因此,SX锁允许多个事务对同一个表同时获得SX和子共享表锁。

ROW EXCLUSIE类似于ROW SHARE模式,但是不能应用在SHARE模式中。当update,insert,delete发生时,ROW EXCLUSIVE会自动获得。对应lmode3,row-X (SX) 。

实验

实验内容:but it also prohibits locking in SHARE mode

这里可以看到会话1的TM4阻塞了会话2的TM3。

提交2个会话后,接着实验:ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

当会话1做了修改而没有commit或者rollback时,这里有两个锁,其中一个就是TM3的,一个是TX6的。

共享表锁 Share Table Lock (S)

由某个事务拥有的共享表锁允许其它事务查询(而不使用SELECT...FOR UPDATE),但是更新操作只能在仅有单个事务持有共享表锁时才允许。因为可能有多个事务同时持有共享表锁,所以持有此锁不足以确保一个事务可以修改该表。

SHARE允许同时查询,但是禁止更新被锁定的表。对应lmode4,share (S) 。

实验

这里可以看到会话1的TM4阻塞了会话2的TM3。

共享行独占表锁 Share Row Exclusive Table Lock (SRX)

这种锁也称为共享子独占表锁(SSX,share-subexclusive table lock),比共享表锁的限制性更强。一次只能有一个事务可以获取给定的表上的SSX锁。由某个事务拥有的SSX锁允许其它事务查询该表(除SELECT...FOR UPDATE)但不能更新该表。

共享行级排它锁有时也称共享子排它锁(Share Subexclusive Table Lock,SSX),它比共享锁有更多限制。定义共享行级排它锁的语法为:

Lock Table TableName In Share Row Exclusive Mode;

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
实验

这里可以看到会话1的TM5阻塞了会话2的TM4。

独占表锁 Exclusive Table Lock (X)

这种锁是最严格的锁,禁止其它事务执行任何类型的DML语句,或在表上放置任何类型的锁。

EXCLUSIVE EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

EXCLUSIVE模式允许查询被锁表上的数据,但是禁止任何其他任何活动(这里我理解是禁止添加其他任何模式的锁)。对应lomde6,exclusive (X) 。

实验

在这里,从BLOCK字段可以看到会话1的TM3并没堵塞会话2的TM3,这里真正发生堵塞的是会话1的TX6。

这里还有一个锁定对象的问题。上面两个TM3的锁针对的对象是object_id为77624的表,既然描述是类似行共享,自然是不会堵塞的。而两个TX6的锁针对的对象可以理解成表中的行,在这些行上添加EXCLUSIVE锁(lmode6,exclusive (X) )自然是会堵塞其他的EXCLUSIVE锁的。

解决这种类型的锁堵塞当然就是在代码中尽早commit结束事务。很多地方都写到尽早commit可以提高运行效率,这里所指的是释放锁(特别是lmode6的EXCLUSIVE锁)减少堵塞,以提高并发性。(不是以减少数据的量来提高效率的,事实上不管多大的数据量,一个commit的过程都是很"平"的。

INSERT /+APPEND/ INTO加6级TM和TX独占锁

Oracle锁系列一文全搞定

会话3:

Oracle锁系列一文全搞定

其中,会话1的sid为27,分别在TX和TM级别,拥有LMODE为6的X锁。BLOCK为1说明会话1阻塞了其它会话(0表示没有阻塞,2表示RAC环境需要用GV$LOCK)。CTIME表示拥有此锁的时间,单位为秒。会话2的sid为162,REQUEST为6表示正在请求模式为6的锁。

当TYPE列为TM的时候,即对于TM锁来说,ID1列表示被锁定的对象的对象ID,ID2始终为0,如下:

当TYPE列为TX的时候,即对于TX锁来说,ID1列表示事务使用的回滚段编号以及在事务表中对应的记录编号,ID2表示该记录编号被重用的次数(wrap),ID1列表示事务的信息,如下:

Oracle锁系列一文全搞定

从视图DBA_DML_LOCKS可以非常直观的看出锁的情况,会话1即SID为27,拥有Exclusive的排它锁,没有请求其它锁,而会话2即SID为162正在请求Exclusive的排它锁。

Oracle锁系列一文全搞定

从会话查询锁的信息:

Oracle锁系列一文全搞定

会话1提交,查看会话2的情况:

总结

执行不同的 DML 语句时,Oracle自动地对数据加锁。

查询操作默认获取的锁

执行查询(query)的 SQL 语句不易与其他 SQL 语句冲突,因为查询只需读取数据。除了 SELECT 之外,INSERT,UPDATE,及 DELETE 语句中也可能包含隐式的查询。因此,以下语句都属于查询操作:

但是以下语句不属于查询操作:

查询操作具备以下特性:

  • 查询无需获取数据锁。因此当某事务查询数据表时,其它事务可以并发地查询、更新同一个表,包括此表中正在被查询的数据行。没有使用 FOR UPDATE 子句的 SELECT 语句无需获取任何数据锁,因此也不会阻塞任何操作,此类查询在 Oracle 中被称为非阻塞查询(nonblocking query)。
  • 执行查询也不受数据锁的限制。(在某些特殊情况下,查询需要等待挂起的分布式事务所拥有的数据锁)
INSERT,UPDATE,DELETE,及 SELECT ... FOR UPDATE 语句默认获取的锁

INSERT,UPDATE,DELETE,及 SELECT ... FOR UPDATE 语句默认获取的锁有以下特点:

  • 包含 DML 语句的事务需要获得被其修改的数据行上的排它行级锁(exclusive row lock)。在拥有锁的事务提交或回滚前,其它事务不能更新或删除被加锁的数据行。
  • 事务无需获取 DML 语句内的子查询(subquery)或隐式查询(implicit query)(例如 WHERE 子句内的查询)所选择的行上的行级锁。DML 内的子查询或隐式查询获得的数据相对查询开始的时间点满足一致性,这些查询不会看到 DML 语句自身对数据的影响。
  • 事务内的查询能够看到本事务内之前执行的 DML 语句对数据的修改,但无法看到本事务开始后执行的其它事务对数据的修改。
  • 事务内的 DML 语句除了需要获得必要的排它行级锁(exclusive row lock)外,至少还需获得包含被修改数据行的表上的行排它表级锁(row exclusive table lock)。如果事务已经获得了相关表上的共享表级锁(share),共享行排它表级锁(share row exclusive),或排它表级锁(exclusive),那么就无需获取行排它表级锁了。如果事务已经获得了相关表上的行共享表级锁(row share table lock),Oracle 将自动地将此锁转换为行排它表级锁。

DDL锁(DDL Locks)

当某个运行中的DDL操作正在操作或引用某模式对象时,数据字典(DDL)锁保护该模式对象的定义。在DDL操作的过程中,只有被修改或引用的单个模式的对象被锁定。数据库绝不会锁定整个数据字典。

Oracle数据库将为任何要求锁的DDL事务自动获取DDL锁。用户不能显式请求DDL锁。例如,如果用户创建一个存储过程,则数据库自动为过程定义中引用的所有模式对象获取DDL锁。DDL锁防止在过程编译完成之前,这些对象被更改或删除。

数据字典锁(data dictionary lock,DDL)的作用是在执行 DDL 操作时对被修改的方案对象或其引用对象的定义进行保护。管理员及开发者应该意识到 DDL 语句将会隐式地提交一个事务。例如,用户创建一个存储过程时,相当于执行一个只包含一条 SQL 语句的事务,Oracle 会自动获取过程定义中所引用的所有方案对象的 DDL 锁。DDL 锁能够防止编译期间过程所引用的对象被其它事务修改或移除。

当 DDL 事务需要时 Oracle 将自动地为其获取数据字典锁。用户不能显示地获取 DDL 锁。只有在 DDL 操作中被修改或引用的对象才会被加锁,整个数据字典不会被加锁。

当用户发布DDL(Data Definition Language)语句时会对涉及的对象加DDL锁。由于DDL语句会更改数据字典,所以该锁也被称为字典锁。

DDL锁能防止在用DML语句操作数据库表时,对表进行删除,或对表的结构进行更改。

对于DDL锁,要注意的是:

  • DDL锁只锁定DDL操作所涉及的对象,而不会锁定数据字典中的所有对象。
  • DDL锁由Oracle自动加锁和释放。不能显式地给对象加DDL锁,即没有加DDL锁的语句。
  • 在过程中引用的对象,在过程编译结束之前不能被改变或删除,即不能被加排它DDL锁。

DDL 锁可以分为三类:排它 Ddl 锁(Exclusive DDL Lock),共享 Ddl 锁(Share DDL Lock),及可中断的解析锁(Breakable Parse Lock)。

排它DDL锁(eXclusive DDL Locks,XDDL)--独占DDL锁

大多数DDL 都带有一个排它DDL 锁。如果发出如下一条语句:

在执行这条语句时,表T 不能被别人修改。在此期间,可以使用SELECT 查询这个表,但是大多数其他操作都不允许执行,包括所有DDL 语句。

独占DDL锁可防止其它会话获取DDL或DML锁。除了那些在"共享DDL锁"中所述操作之外,绝大多数DDL操作需要对资源获取独占锁,以防止和其它可能会修改或引用相同模式对象的DDL之间的破坏性干扰。例如,当ALTER TABLE正在将一列添加到表时,不允许DROP TABLE删除表,反之亦然。

独占DDL锁在整个DDL语句执行期间一直持续,并自动提交。在独占DDL锁获取过程中,如果另一个操作在该模式对象上持有另一个DDL锁,则这个锁获取将一直等待,直到前一个DDL锁被释放,才能继续。

共享DDL锁(Share DDL Locks,SDDL)

create index t_idx on t(x) ONLINE;

ONLINE 关键字会改变具体建立索引的方法。Oracle 并不是加一个排它DDL 锁 防止数据修改,而只会试图得到表上的一个低级 (mode 2 )TM 锁。这会有效地防止其他DDL 发生,同时还允许DML 正常进行。Oracle 执行这一壮举”的做法是,为DDL 语句执行期 间对表所做的修改维护一个记录,执行CREATE 时再把这些修改应用至新的索引。这样能大大增加数据的可用性。

另外一类DDL 会获得共享DDL 锁。在创建存储的编译对象(如过程和视图)时,会对依赖的对象加这种共享DDL 锁。例如,如果 执行以下语句:

Create view MyView as select * from emp, dept where emp.deptno = dept.deptno;

表EMP 和DEPT 上都会加共享DDL 锁,而CREATE VIEW 命令仍在处理。可以修改这些表的内容,但是不能修改它们的结构。

A share DDL lock for a resource prevents destructive interference with conflicting DDL operations, but allows data concurrency for similar DDL operations.

在资源上的共享DDL锁可防止与冲突的DDL操作发生破坏性干扰,但允许类似的DDL操作的数据并发。

例如,当CREATE PROCEDURE语句运行时,所在事务将为所有被引用的表获取共享DDL锁。其它事务可以同时创建引用相同表的过程,并在相同的表上同时获得共享DDL锁,但没有任何事务能在任何被引用表上获取独占DDL锁。

共享DDL锁在整个DDL语句执行期间持续存在,并自动提交。因此,持有一个共享DDL锁的事务,可保证在事务过程中,被引用模式对象的定义保持不变。

某些 DDL 操作需要获取相关资源上的共享 DDL 锁(share DDL lock)以防止与之冲突的 DDL 操作造成破坏性的干扰,但与之类似的 DDL 操作可以并发地访问数据,不受共享 DDL 锁的限制。例如,执行 CREATE PROCEDURE 语句时,事务将获取所有引用对象上的共享 DDL 锁。此时,其它事务可以并发地获取相同表上的共享 DDL 锁并创建引用了相同表的过程。但任何事务都无法获取被引用表上的排它 DDL 锁(exclusive DDL lock),即任何事务都无法对表进行修改或移除操作。因此获得了共享 DDL 锁的事务能够保证在其执行期间,所有引用对象的定义不会被修改。

执行以下 DDL 语句时,需要获取引用对象上的共享 DDL 锁:AUDIT,NOAUDIT,COMMENT,CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER,CREATE SYNONYM,及 CREATE TABLE(没有使用 CLUSTER 参数时)。

分析锁(Breakable Parse Locks,可中断解析锁,BPL)

SQL语句或PL/SQL程序单元,为每个被其引用的模式对象持有一个解析锁。获取解析锁的目的是,如果被引用的对象被更改或删除,可以使相关联的共享SQL区无效。解析锁被称为可中断的解析锁,因为它并不禁止任何DDL操作,并可以被打破以允许冲突的DDL操作。

解析锁是在执行SQL语句的分析阶段,在共享池中获取的。只要该语句的共享SQL区仍保留在共享池中,该锁就一直被持有。

位于共享池(shared pool)内的 SQL 语句(或 PL/SQL 程序结构)拥有其引用的所有方案对象上的解析锁(parse lock)。解析锁的作用是,当共享 SQL 区(shared SQL area)所引用的对象被修改或移除后,此共享 SQL 区能够被置为无效。解析锁不会禁止任何 DDL 操作,当出现与解析锁冲突的 DDL 操作时,解析锁将被解除,因此也称之为可解除的解析锁。

解析锁是在 SQL 语句执行的解析阶段(parse phase)获得的,在共享 SQL 区被清除出共享池(shared pool)前一直保持。

你的会话解析一条语句时,对于该语句引用的每一个对象都会加一个解析锁。加这些锁的目的是:如果以某种方式删除或修改了一个被引用的对象,可以将共享池中已解析的缓存语句置为无效(刷新输出)。

查看分析锁

要看到一个实际的可中断解析锁,下面先创建并运行存储过程P_BPL_LHR:

过程P_BPL_LHR现在会出现在DBA_DDL_LOCKS 视图中。我们有这个过程的一个解析锁:

Oracle锁系列一文全搞定

然后重新编译这个过程,并再次查询视图:

Oracle锁系列一文全搞定

可以看到,现在这个视图中没有P_BPL_LHR了。我们的解析锁被中断了。这个视图对 发人员很有用,发现测试或开发系统中某段代码无法编译时,将会挂起并最终超时。这说明,有人正在使用这段代码 (实际上在运行这段代码),你可以使用这个视图 查看这个人是谁。对于GRANTS 和对象的其他类型的DDL 也是一样。例如,无法对正在运行的过程授予EXECUTE 权限。可以使用同样的方法 发现潜在的阻塞者和等待者。

DDL 锁的持续时间

DDL 锁的持续时间取决于其类型。共享 DDL 锁(share DDL lock)及排它 DDL 锁(exclusive DDL lock)在 DDL 语句执行期间一直存在,在 DDL 语句自动提交后释放。而解析锁一直存在,直至相关的共享 SQL 区从共享池中被清除。

DDL 锁与簇

对簇(cluster)执行的 DDL 操作需要获取簇及簇内所有表及物化视图上的排它 DDL 锁(exclusive DDL lock)。对簇内表及物化视图的 DDL 操作需要获取簇上的共享 DDL 锁(share DDL lock),以及表或物化视图上的共享 DDL 锁或排它 DDL 锁。簇上的共享 DDL 锁能够防止操作期间其他 DDL 操作将簇移除。

系统锁(System Locks)

Oracle数据库使用各种类型的系统锁,来保护数据库内部和内存结构。由于用户不能控制其何时发生或持续多久,这些机制对于用户几乎是不可访问的。闩锁、互斥体、和内部锁是完全自动的。

闩锁(Latches)

闩锁(latche)是一种简单的底层串行化机制,用于保护 SGA 内的共享数据结构。例如,用于记录当前正在访问数据库的用户的列表,或用于记录位于数据库缓存(buffer cache)内的数据块的数据结构,都可通过闩锁进行保护。当服务进程(background process)或后台进程(server process)需要操作或查询此类数据结构时,就需要获取一个闩锁,但其加锁时间极短。闩锁的实现与操作系统有关,例如进程是否需要等待栓锁以及等待多长时间等。

闩锁是简单、低级别的串行化机制,用于协调对共享数据结构、对象、和文件的多用户访问。闩锁防止共享内存资源被多个进程访问时遭到破坏。具体而言,闩锁在以下情况下保护数据结构:

  • 被多个会话同时修改
  • 正在被一个会话读取时,又被另一个会话修改
  • 正在被访问时,其内存被释放(换出)

通常,一个单一的闩锁保护SGA中的多个对象。例如,后台进程(如DBWn和LGWR)从共享池分配内存来创建数据结构。为分配此内存,这些进程使用共享池闩锁来串行化对内存的访问,以防止两个进程同时尝试检查或修改共享池。内存分配后,其它进程可能需要访问共享池区域,如用于解析所需的库高速缓存。在这种情况下,进程只在库缓存获取闩锁,而不是在整个共享池。

与行锁之类的入队闩锁不同,闩锁不允许会话排队。当闩锁可用时,请求闩锁的第一个会话将获得它的独占访问权限。闩锁旋转(Latch spinning)发生在当一个进程不断地循环来请求一个闩锁时,而闩锁睡眠(latch sleeping)发生在重新发起闩锁请求之前,释放CPU时。

通常,一个Oracle进程在操作或查看一种数据结构时,只需在一个极短的时间内获得闩锁。例如,仅仅为某一名员工处理工资更新,数据库就可能需要获取并释放成千上万个闩锁。闩锁的实现依赖于操作系统,特别是在一个进程是否会在闩锁上等待以及会在闩锁等待多长时间方面。

闩锁的增加意味着并发的降低。例如,过度硬解析操作会产生库缓存闩锁争用。V$LATCH视图包含每个闩锁的详细使用情况的统计信息,包括每个闩锁被请求和被等待的次数。

互斥对象(Mutexes)

互斥对象(mutual exclusion object,mutex),也叫互斥体,它是一种底层机制,用于防止在内存中的对象在被多个并发进程访问时,被换出内存或遭到破坏。互斥对象类似于闩锁,但闩锁通常保护一组对象,而互斥对象通常保护单个对象。

互斥对象提供以下几个优点:

  1. 互斥体可以减少发生争用的可能性。

由于闩锁保护多个对象,当多个进程试图同时访问这些对象的任何一个时,它可能成为一个瓶颈。而互斥体仅仅串行化对单个对象的访问,而不是一组对象,因此互斥体提高了可用性。

  1. 互斥体比闩锁消耗更少的内存。
  2. 在共享模式下,互斥体允许被多个会话并发引用。

内部锁(Internal Locks)

内部锁是比闩锁和互斥体更高级、更复杂的机制,并用于各种目的。数据库使用以下类型的内部锁:

  1. 字典缓存锁(Dictionary cache locks)

这些锁的持续时间很短,当字典缓存中的条目正在被修改或使用时被持有。它们保证正在被解析的语句不会看到不一致的对象定义。字典缓存锁可以是共享的或独占的。共享锁在解析完成后被释放,而独占锁在DDL操作完成时释放。

当用户更新或使用时数据字典缓存内的条目(entry)时,需要获取条目上的数据字典缓存锁(dictionary cache lock),此类锁的持续时间极短。此类锁的作用是确保正在被解析的语句不会看到不一致的对象定义。数据字典缓存锁可以为共享或排它的。当语句解析结束时共享锁将被释放,而当 DDL 操作结束时排它锁将被释放。

  1. 文件和日志管理锁(File and log management locks)

这些锁保护各种文件。例如,一种内部锁保护控制文件,以便一次只有一个进程可以对其进行更改。而另一种锁用于协调联机重做日志文件的使用和归档。数据文件被锁定,确保数据库被多个实例以共享模式装载,或以独占模式被单个实例装载。因为文件和日志锁表示文件的状态,这些锁必要时会被持有较长一段时间。

此类内部锁(internal lock)用于保护各种文件。例如,保护控制文件(control file)的锁,确保同一时间只有一个进程能够对其进行修改。还有协调重做日志文件(redo log file)使用与归档的锁。以及数据文件(datafile)锁,实现多实例在共享模式下挂载数据库,或一个实例在排它模式下挂载数据库。由于文件及重做日志锁反映的是 物理文件的状态,因此此类锁的持续时间较长。

  1. 表空间和撤销段锁(Tablespace and undo segment locks)

这些锁保护的表空间和撤销段。例如,访问数据库的所有实例对一个表空间是否处于联机或脱机必须保持一致。撤销段被锁定,以便只能有一个数据库实例可以写入该段。

此类锁用于保护表空间及回滚段(rollback segment)。例如,一个表空间处于联机(online)还是脱机(offline)状态对访问同一数据库的所有实例应该是一致的。回滚段上的锁保证 同一时间只有一个实例能够对其执行写操作。

死锁(Deadlock)

有关死锁的内容之前发布过一次,具体内容参考:http://blog.itpub.net/26736162/viewspace-2127247/,本篇文章不再讲解。

数据字典

常用的数据字典视图有DBA_DML_LOCKS、DBA_DDL_LOCKS、V$LOCK、DBA_LOCK、V$LOCKED_OBJECT。

---查询的都是当前实例的锁

V$LOCK和dba_lock、dba_locks

本视图列出Oracle 服务器当前拥有的锁以及未完成的锁或栓锁请求。

Oracle锁系列一文全搞定

三者关系

v$lock和dba_locks和 dba_lock 内容一样,dba_locks是dba_lock的同义词。可以用动态性能视图的定义来查看它们的关系V$FIXED_VIEW_DEFINITION。

V$LOCKED_OBJECT

注意:V$LOCKED_OBJECT记录的是DML锁信息,DDL锁的信息不在里面。

这个视图列出系统上的每个事务处理所获得的所有锁。 记录了当前已经被锁定的对象的信息

XIDUSN表示当前事务使用的回滚段的编号

XIDSLOT说明该事务在回滚段头部的事务表中对应的记录编号

XIDSQN说明序列号

OBJECT_ID说明当前被锁定的对象的ID号,可以根据该ID号到dba_objects里查找被锁定的对象名称

LOCKED_MODE说明锁定模式的数字编码

Oracle锁系列一文全搞定

V$LOCKED_OBJECT中的列说明:

示例:1.以DBA角色查看当前数据库里锁的情况可以用如下SQL语句:

V$locked_object视图列出当前系统中哪些对象正被锁定.

V$lock视图列出当前系统持有的或正在申请的所有锁的情况.

DBA_DDL_LOCKS

DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock.

Oracle锁系列一文全搞定

查询所有DDL锁的信息:

Oracle锁系列一文全搞定

如果提示没有这个视图,可以在sys用户下执行$ORACLE_HOME/rdbms/admin/catblock.sql脚本进行创建(这个脚本还包含其他一些非常有意义的锁相关视图)

这里省略创建过程

打印一下catblock.sql脚本的内容,这个创建脚本其实可以当做一个参考文档来用,尤其是其中关于锁类型的描述。

DBA_DML_LOCKS

DBA_DML_LOCKS lists all DML locks held in the database and all outstanding requests for a DML lock.

Oracle锁系列一文全搞定

一些字段的说明

Oracle锁系列一文全搞定

V$SESSION视图的TADDR列表示事务处理状态对象的地址,对应于V$TRANSACTION.ADDR列;V$SESSION视图的LOCKWAIT列表示等待锁的地址,对应于V$LOCK的KADDR列;若当前会话没有被阻塞则为空。V$SESSION视图的SADDR列对应于V$TRANSACTION的SES_ADDR列。可以通过ROW_WAIT_OBJ#、ROW_WAIT_FILE#、ROW_WAIT_BLOCK#、ROW_WAIT_ROW#这几个字段查询现在正在被锁的表的相关信息(ROWID),例如,表名、文件名及行号。P1和P2根据等待事件的不同所代表的含义不同,可以从V$EVENT_NAME视图获知每个参数的含义。

可以看到被锁的行的地址。

在V$LOCK中,当TYPE列的值为TM时,ID1的值为DBA_OBJECTS.OBJECT_ID;当为TX锁时,ID1对应视图V$TRANSACTION中的XIDUSN字段(Undo segment number:事务对应的撤销段序列号)和XIDSLOT字段(Slot number:事务对应的槽位号)。其中ID1的高16位为XIDUSN,低16位为XIDSLOT。计算公式为:SELECT TRUNC(ID1/POWER(2,16)) AS XIDUSN,BITAND(ID1,TO_NUMBER('FFFF','XXXX')) + 0 AS XIDSLOT , ID2 XIDSQN FROM DUAL;

在V$LOCK中,当TYPE列的值为TM锁时,ID2的值为0;当为TX锁时,ID2对应视图V$TRANSACTION中的XIDSQN字段(Sequence number:事务对应的序列号)。

从V$SESSION视图可以得到所有内容:

Oracle锁系列一文全搞定

关联关系图

Oracle锁系列一文全搞定

参数

DML_LOCKS参数

可以获得的TX锁定的总个数由初始化参数transactions决定,而可以获得的TM锁定的个数则由初始化参数dml_locks决定

select name,value from V$parameter where name in ('transactions','dml_locks');

DML_LOCKS参数属于推导参数,DML_LOCKS=4 * TRANSACTIONS

系统中允许的TM 锁总数可以由你配置(有关细节请见Oracle Database Reference 手册中的DML_LOCKS 参数定义)。实际上,这个数可能设置为0。但这并不是说你的数据库变成了一个只读数据库(没有锁),而是说不允许DDL。在非常专业的应用(如RAC 实现)中,这一点就很有用,可以减少实例内可能发生的协调次数。通过使用ALTER TABLE TABLENAME DISABLE TABLE LOCK 命令,还可以逐对象地禁用TM 锁。这是一种快捷方法,可以使意外删除表的难度更大”,因为在删除表之前,你必须重新启用表锁。还能用它检测由于外键未加索引而导致的全表锁(前面已经讨论过)。

PropertyDescription
Parameter typeInteger
Default valueDerived: 4 * TRANSACTIONS
ModifiableNo
Range of values20 to unlimited; a setting of 0 disables enqueues
BasicNo
Oracle RACYou must set this parameter for every instance, and all instances must have positive values or all must be 0.

A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.

The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.

Enqueues are shared memory structures that serialize access to database resources. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you should be aware of the following restrictions when you set you DML_LOCKS to 0:

  • You cannot use DROP TABLE, CREATE INDEX statements
  • You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE
  • Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0

Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter.

DDL_LOCK_TIMEOUT

11g以前,DDL 语句是不会等待DML语句的,当DDL语句访问的对象正在执行的DML语句,会立即报错ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效。而在11g以后,DDL_LOCK_TIMEOUT参数可以修改这一状态,当DDL_LOCK_TIMEOUT=0时,DDL 不等待DML,当DDL_LOCK_TIMEOUT 为N(秒)时,DDL等待DML N 秒,该值默认为0。

PropertyDescription
Parameter typeInteger
Default value0
ModifiableALTER SESSION
Range of values0 to 1,000,000 (in seconds)
BasicNo

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.

If a lock is not acquired before the timeout period expires, then an error is returned.

会话1:

会话2:

综上,设置ddl_lock_timeout为N(秒)后,DDL执行后将等待N秒钟后才抛出报错信息。在ddl_lock_timeout为默认值 0 时,DDL语句提交之后马上报错。

for update、for update of、for update nowait

SELECT...FOR UPDATE 语句的语法如下:

其中:

  • OF 这个OF子句在牵连到多个表时,具有较大作用,如不使用OF指定锁定的表的列,则所有表的相关行均被锁定,若在OF中指定了需修改的列,则只有与这些列相关的表的行才会被锁定。
  • WAIT 子句指定等待其他用户释放锁的秒数,防止无限期的等待。

“使用FOR UPDATE WAIT”子句的优点如下:

1防止无限期地等待被锁定的行;

2允许应用程序中对锁的等待时间进行更多的控制。

3对于交互式应用程序非常有用,因为这些用户不能等待不确定

4 若使用了skip locked,则可以越过锁定的行,不会报告由wait n 引发的‘资源忙’异常报告

FOR UPDATE 和 FOR UPDATE NOWAIT 的区别

for update nowait和 for update都会对所查询到得结果集进行加锁,所不同的是,如果另外一个进程正在修改结果集中的数据,for update nowait不会进行资源等待,只要发现结果集中有些数据被加锁,立刻返回“ORA-00054错误,内容是资源正忙, 但指定以 NOWAIT 方式获取资源”。

for update 和 for update nowait加上的是一个行级锁,也就是只有符合where条件的数据被加锁。如果仅仅用update语句来更改数据时,可能会因为加不上锁而没有响应地、莫名其妙地等待,但如果在此之前,for update NOWAIT语句将要更改的数据试探性地加锁,就可以通过立即返回的错误提示而明白其中的道理,或许这就是For Update和NOWAIT的意义之所在。

阻塞,不返回错误。提交第一个会话,第二个回话自动执行,然后提交第二个会话

SELECT...FOR UPDATE OF COLUMNS

select for update of,这个of子句在牵连到多个表时,具有较大作用,如不使用of指定锁定的表的列,则所有表的相关行均被锁定,若在of中指定了需修改的列,则只有与这些列相关的表的行才会被锁定。

可以看到,会话1在SCOTT.EMP和SCOTT.DEPT表上都加上了3级的行级排它锁。

提交以上的会话,然后继续试验OF特性:

可以看到,会话1在SCOTT.EMP表上加上了3级的行级排它锁,而会话2在和SCOTT.DEPT表上加上了3级的行级排它锁。

9i中的SELECT FOR UPDATE锁

可以看到在Oracle 10g之前,SELECT FOR UPDATE获取的是2级TM锁,在Oracle 10g及其之后的版本中,SELECT FOR UPDATE获取的是3级TM锁。

总结

  1. SELECT * FROM TABLE1 FOR UPDATE 锁定表的所有行,其它会话只能读不能写
  2. SELECT * FROM TABLE1 WHERE PKID = 1 FOR UPDATE 只锁定PKID=1的行
  3. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID FOR UPDATE 锁定两个表的所有记录
  4. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID WHERE A.PKID = 10 FOR UPDATE 锁定两个表的中满足条件的行
  5. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID WHERE A.PKID = 10 FOR UPDATE OF A.PKID 只锁定TABLE1中满足条件的行

FOR UPDATE 是把所有的表都锁定。FOR UPDATE OF 根据OF后表的条件锁定相对应的表。

Oracle包被锁定的原因分析及解决方案

摘抄自网络,小麦苗感觉自己对这个部分也没啥可写的,主要是包不能编译的时候需要查询DBA_DDL_LOCKS视图,最后杀会话的时候需要稳重一点。

在数据库的开发过程中,经常碰到包、存储过程、函数无法编译或编译时会导致PL/SQL 无法响应的问题。碰到这种问题,基本上都要重启数据库解决,严重浪费开发时间。本文将就产生这种现象的原因和解决方案做基本的介绍。

问题分析

从事数据库开发的都知道锁的概念,如:执行 Update Table xxx Where xxx 的时候就会产生锁。这种常见的锁在Oracle里面被称为DML锁。在Oracle中还有一种DDL锁,主要用来保证存储过程、表结构、视图、包等数据库对象的完整性,这种锁的信息可以在DBA_DDL_LOCKS中查到。注意:V$LOCKED_OBJECT记录的是DML锁信息,DDL锁的信息不在里面。

对应DDL锁的是DDL语句,DDL语句全称数据定义语句(Data Define Language)。用于定义数据的结构或Schema,如:CREATE、ALTER、DROP、TRUNCATE、COMMENT、RENAME。当我们在执行某个存储过程、或者编译它的时候Oracle会自动给这个对象加上DDL锁,同时也会对这个存储过程所引用的对象加锁。

举例:

  1. 打开一个PL/SQL,开始调试某个函数(假设为:FUN_CORE_SERVICECALL),并保持在调试状态
  2. 打开一个SQL Window,输入Select * From dba_ddl_locks a Where a.name = 'FUN_CORE_SERVICECALL' 会发现一行记录:Oracle锁系列一文全搞定
  3. 打开一个新的PL/SQL,重新编译这个函数。我们会发现此时已经无法响应了
  4. 回到第一个PL/SQL,重新执行Select * From dba_ddl_locks a Where a.name = 'FUN_CORE_SERVICECALL' 我们将会看到如下记录:

Oracle锁系列一文全搞定

  1. 上述的情况表明发生了锁等待的情况。

当我们试图编译、修改存储过程、函数、包等对数据对象的时候,如果别人也正在编译或修改他们时就会产生锁等待;或者我们在编译某个存储过程的时候,如果它所引用的数据库对象正在被修改应该也会产生锁等待。这种假设有兴趣的兄弟可以测试下,不过比较困难。

解决方案

碰到这种问题,如果知道是被谁锁定了(可以查出来的),可以让对方尽快把锁释放掉;实在查不出来只能手工将这个锁杀掉了。步骤如下:

  1. 首先查出哪些进程锁住了这个对象,语句如下:

    Select b.SID,b.SERIAL#
    From dba_ddl_locks a, V$session b
    Where a.session_id = b.SID
    And a.name = 'FUN_CORE_SERVICECALL';

  2. 执行如下语句杀进程:alter system kill session 'sid,serial#' IMMEDIATE;

  3. 执行了以上的语句后,有的时候不一定能够将进程杀掉。这个时候就需要连到数据库服务器上杀掉服务器端的进程了,查询语句:

在服务器上执行如下语句:

kill -9 spid(UNIX平台)

orakill sid thread(Windows平台 SID是Oracle的实例名,thread是上面查出来的SID)

执行完4步以后基本上就可以杀掉这些锁死的进程了,不放心的话可以再执行第一步确认下。

实验

查看DDL锁:

Oracle锁系列一文全搞定

Oracle锁系列一文全搞定

创建索引的锁

创建或重建索引会阻塞DML操作

版本:11.2.0.3

可以发现在会话1中,在创建索引的过程中会生成2个TM锁,锁类别分别为4和3,根据查询结果发现lmode=4的object_id为77629的对象对应的是T_INDEX_161113这个表,对应的是TM的S锁。另一个lmode=3的锁对象是系统基表OBJ\$表,允许其它会话对该表执行DML操作。可以得出这样一个结论:当对表进行创建索引操作时,会伴随出现LMODE=4的S锁。根据锁的兼容模式可以发现S锁和任何DML操作都是冲突的!所以,尤其是在生产上,当在一个很大的表上进行索引创建的时候,任何对该表的DML操作都会被夯住!!!

从DBA_DDL_LOCKS视图可以看到,建索引的同时有6级排它DDL锁。

Oracle 11g下ONLINE选项不会堵塞DML操作

版本:11.2.0.3

接着上面的实验,重建索引的时候加上ONLINE,由于会话断开了,重新开2个会话,会话1为22,会话2为142:

Oracle锁系列一文全搞定

可以发现在会话1中,在加上ONLINE重建索引的过程中会生成2个TM锁,锁类别分别为2和4,根据查询结果发现lmode=2的object_id为77629的对象对应的是T_INDEX_161113这个表,对应的是TM的Row-S (SS)锁即行级共享锁,该锁允许其它会话对该表执行DML操作。另一个lmode=4的锁对象是SYS_JOURNAL_77631,应该为系统临时创建的对象,对应的是TM的S锁。

在会话2中,TX为6的锁,阻塞了其它会话,在这里其实是阻塞了会话1的重建索引的操作。

可以得出这样一个结论:当对表进行创建或重建索引操作时,可以加上ONLINE选项,不阻塞其它会话的DML操作,但是在创建或重建索引的过程中,其它的会话产生的事务会阻塞索引的创建或重建操作,所以必须结束其它会话的事务才能让创建或重建索引的操作完成。

注意:在加上ONLINE选项创建索引的过程中,若手动CTRL+C取消后,可能导致索引被锁,出现ORA-08104: this index object 77645 is being online built or rebuilt的错误,这个时候可以利用如下的脚本清理对象,77645为对象的OBJECT_ID:

Oracle 10g下ONLINE选项会堵塞DML操作

版本为:10.2.0.1.0

重新开3个会话,会话1为143,会话2为152,会话3为158:

Oracle锁系列一文全搞定

可以发现在会话1中,在加上ONLINE重建索引的过程中会生成2个TM锁,锁类别分别为2和4,根据查询结果发现lmode=2的object_id为53121的对象对应的是T_INDEX_161113这个表,对应的是TM的Row-S (SS)锁即行级共享锁,该锁允许其它会话对该表执行DML操作,但是该会话在请求模式为4的S锁。另一个lmode=4的锁对象是SYS_JOURNAL_53122,为系统临时创建的索引组织表(IOT),对应的是TM的S锁。

在会话2中,请求3级TM锁。会阻塞关系可以看出,会话3阻塞了会话1,而会话1阻塞了会话2,所以提交会话3即可让索引创建完成。

实验10.2.0.1.0

版本为:10.2.0.1.0

重新开3个会话,会话1为143,会话2为152,会话3为158,会话1插入一条记录:

Oracle锁系列一文全搞定

从上面的结果可以知道,会话2即创建索引的会话一共出现了4个锁,两个DL锁,一个针对表T_INDEX_161113的TM锁,一个是online rebuild index时需要的一个中间表的TM锁,中间表用于记录rebuild期间的增量数据,原理类似于物化视图日志,其object_id为53162,这是一个索引组织表(IOT),从这里我们也可以发现IOT的优点和适合的场合,这张中间表只有插入,不会有删除和修改操作,而且只有主键条件查询,正是IOT最合适的场景。

会话2在请求一个模式为4的TM锁,模式4会阻塞这个表上的所有DML操作,所以这时再往这个表上执行DML也会挂起。

会话3删除一条语句:

会话3请求模式为3的TM锁无法获得,会话被阻塞。这是因为锁请求是需要排队的,即使会话3和会话1是可以并发的,但由于会话2先请求锁并进入等待队列,后来的会话3也只好进入队列等待。所以,如果在执行rebuild index online前有长事务,并且并发量比较大,则一旦执行alter index rebuild online,可能因为长事务阻塞,可能导致系统瞬间出现大量的锁,对于压力比较大的系统,这是一个不小的风险。这是需要迅速找出导致阻塞的会话kill掉,rebuild index online一旦执行,不可轻易中断,否则可能遇到ORA-08104。

从会话级别可以看出,会话1阻塞了会话2,会话2阻塞了会话3,在会话1执行rollback,可以发现很短时间内会话3也正常执行完毕,说明会话2持有模式4的TM锁的时间很短,然后在rebuild online的进行过程中,对表加的是模式为2的TM锁,所以这段时间不会阻塞DML操作:

回滚会话1,然后观察锁的情况:

会话2又开始在请求模式4的TM锁,被会话3阻塞!这时在会话1再执行DML操作,同样会被会话2阻塞,进入锁等待队列。

在会话3执行rollback或者commit以后,会话2和会话3都很快执行完毕。

从上面的试验可以发现,虽然rebuild index online在执行期间只持有模式2的TM锁,不会阻塞DML操作,但在操作的开始和结束阶段,是需要短暂的持有模式为4的TM锁的,这段会阻塞表上的所有DML操作。我们在做rebuild index online的时候,一定要在开始和结束阶段观察系统中是否有长事务的存储,对于并发量较大的系统,最严重的后果,可能在这两个关键点导致数据库产生大量锁等待,系统负载飙升,甚至宕机。

实验11.2.0.3.0

版本为:11.2.0.3.0

开3个会话,会话1为16,会话2为27,会话3为150,会话1删除一条记录: