Oracle DBA面试笔试之锁系列

0    223    1

Tags:

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

在Oracle中,锁分为哪几类?

在Oracle中,锁分为哪几类?

在Oracle中,显式锁和隐式锁的区别有哪些?

在Oracle中,显式锁和隐式锁的区别有哪些?

在Oracle中,与锁有关的数据字典视图有哪些?

在Oracle中,与锁有关的数据字典视图有哪些?

在Oracle中,DML_LOCKS和DDL_LOCK_TIMEOUT参数的作用有哪些?

在Oracle中,DML_LOCKS和DDL_LOCK_TIMEOUT参数的作用有哪些?

在Oracle中,SELECT ... FOR UPDATE加的是什么锁?

在Oracle中,SELECT ... FOR UPDATE加的是什么锁?

在Oracle中,在编译存储过程、函数等对象时无响应该怎么办?

在Oracle中,在编译存储过程、函数等对象时无响应该怎么办?

在Oracle中,对于错误“ORA-08104”应该如何处理?(如何清除创建失败的索引?)

在Oracle中,对于错误“ORA-08104”应该如何处理?(如何清除创建失败的索引?)

在Oracle中,死锁的产生情况有哪些?

在Oracle中,死锁的产生情况有哪些?

在Oracle中,模拟死锁产生的一个场景。

在Oracle中,模拟死锁产生的一个场景。

在Oracle中,Lock、Latch和Pin的区别有哪些?

在Oracle中,Lock、Latch和Pin的区别有哪些?

在Oracle中,在高并发、高负载的情况下,如何给表添加字段并设置DEFAULT值?

在Oracle中,在高并发、高负载的情况下,如何给表添加字段并设置DEFAULT值?

Oracle新建或重建索引过程中的锁有哪些变化

在Oracle中,在新建或重建索引时有哪些锁?

可以利用10704和10046事件跟踪新建或重建索引过程中的锁信息,命令为:

新建或重建索引的锁信息如下图所示:

Oracle DBA面试笔试之锁系列

不带ONLINE的新建或重建索引的SQL语句获取的是4级TM锁,它会阻塞任何DML操作。

在Oracle 10g中,带ONLINE的新建或重建索引的SQL语句在开始和结束的时候获取的是4级TM锁,而在读取表数据的过程中获取的是2级TM锁,所以,在Oracle 10g中,即使加上ONLINE也会阻塞其它会话的DML操作。

在Oracle 11g中,带ONLINE的新建或重建索引的SQL语句在整个执行过程中获取的是2级TM锁,并不会阻塞其它会话的DML操作,但是在创建或重建索引的过程中,其它的会话产生的事务会阻塞索引的创建或重建操作,所以必须结束其它会话的事务才能让创建或重建索引的操作完成。所以应该避免在业务高峰期创建索引。

在Oracle 11g带ONLINE的新建或重建索引的情况下:

① 过程中会持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)两种类型的锁,在Oracle 10g下只有DL锁没有OD锁。

② 表级锁TM的持有模式为2级RS(Row Share)与3级RX(Row Exclusive)类型的锁互相兼容,因此不会在表级发生阻塞。

③ 阻塞发生在行级锁申请阶段,即请求的4级S(Share)类型的锁与执行DML的会话已经持有的6级X(Exclusive)锁之间存在不兼容的情况;相比非ONLINE方式的表级锁,锁的粒度上更加细化,副作用更小。

④ 新增以“SYSJOURNAL”为前缀的IOT表,记录与索引创建动作同时进行的其它DML操作修改过的记录,等到索引创建完成前将IOT表里的记录合并至索引中并删除IOT表。

详情请参考:https://www.xmmup.com/oraclexinjianhuozhongjiansuoyinguochengzhongdesuoxinxi.html

其它

请问Oracle数据库锁的作用是什么?

多用户系统并发访问并修改数据时,保护数据的一致性和完整性。

锁是一种机制,多个事务同时访问一个数据库对象时,该机制可以实现对并发的控制。

Oracle正是使用锁机制来实现系统的高并发,利用不同类型的排它锁或者共享锁来管理并发会话对数据的操作。数据库是一个多用户使用的共享资源。当多个用户并发地存取修改同一数据时,若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性和完整性。Oracle数据库通过其锁机制在事务之间提供数据并发性、一致性和完整性。锁定是自动发生的,通常不需要用户操作。

请问Oracle锁存储在哪里?

在oracle数据库中,不存在真正意义上属于某个对象或数据的锁。oracle锁的信息是数据块的一个物理属性,而不是逻辑上属于某个表或某个行。Oracle的锁机制是一种轻量级的锁定机制,不是通过构建锁列表来进行数据的锁定管理,而是直接将锁作为数据块的属性,存储在数据块首部。在 Oracle 数据库中,它并不是对某个表加上锁或者某几行加上锁, 锁是以数据块的一个属性存在的。也就是说, 每个数据块本身就存储着自己数据块中数据的信息,这个地方叫 ITL( Interested Transaction List), 凡是在这个数据块上有活动的事务,它的信息就会记录在这里面供后续的操作查询,一保证事务的一致性。

请问Oracle锁有哪些类别?分别介绍下?

Oracle DBA面试笔试之锁系列

一:根据获取锁的方式,锁分为以下两种

排它锁(Exclusive Locks,即X锁)和共享锁(Share Locks,即S锁)。

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

可以理解为写锁,这种锁是防止资源的共享,用于修改数据。如果一个事物给某个数据加了排它锁,其它事物就不能对它再加任何锁,直到事物完结,排它锁释放。

1.2 共享锁:共享锁使一个事务对特定数据库资源进行共享访问,另一事务也可对此资源进行访问或获得相同共享锁。共享锁为事务提供高并发性,容易造成死锁和数据更新丢失。

可以理解为读锁,加了共享锁的数据,只能共享读,不能再给它加排它锁进行写的操作。

二:根据系统并发情况,锁分为以下两种

悲观锁和乐观锁。

2.1悲观锁:

所谓的悲观锁:顾名思义,就是很悲观,每次去修改数据的时候都认为别人也会去修改相同的书,所以每次修改数据的时候都会上锁。这样别人修改数据的时候就要等待直到锁的释放。以保证数据的一致性。

悲观锁有两种方式(从SQL语句的区别来看):

1. 执行select xxx for update操作时,数据会被锁定,只有执行comit或rollover才会释放

2. 执行select xxx for update nowait操作时,数据也会被锁定,其他人访问时或返回ORA-00054错误,内容是资源正忙,需要采取相应的业务措施进行处理。

2.2乐观锁:

所谓的乐观锁:就是很乐观,每次去修改数据的时候都认为别人不会去修改相同的数据,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。

因此,需要在提交时保证数据一致性,如果不一致,则返回错误,由程序本身的逻辑进行处理。

乐观锁的实现主要有三种方式:

a. 通过比较提交前后的数据是否发生变化来判断是否存在数据冲突

b. 通过在表中增加版本戳列,来标示是否发生了变化

c. 通过比对表的时间戳来判断是否出现了版本变化

可以通过trigger或存储过程实现该乐观锁。

oracle在数据更新时通常时候的是乐观锁,任何一个以UPDATE...SET开始并且不是以SELECT...FOR UPDATE进行操作的命令就是一个乐观锁的例子。

悲观锁和乐观锁使用场景:

\1. 如果系统并发量不大且不允许脏读,可以使用悲观锁解决并发问题。

\2. 如果系统并发非常大的话,悲观锁会带来很大性能问题,所以一般采用乐观锁。

\3. 如果系统读比较多,写比较少,也应该使用乐观锁,可以提高吞吐量

三:根据保护对象的不同,Oracle数据库锁可以分为以下几大类

(1) DML lock(data locks,数据锁):用于保护数据的完整性;

(2) DDL lock(dictionary locks,字典锁):用于保护数据库对象的结构(例如表、视图、索引的结构定义);

(3) System Locks:保护内部数据库结构,例如Latches,mutexes,internal locks;

Oracle DBA面试笔试之锁系列

3.1 DML锁

DML锁(也称为数据锁)保证了多个用户并发访问的数据的完整性。

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

3.1.1行锁(TX)

行级锁是粒度最细的DML锁,主要用来控制数据行的修改、删除操作。当对表中的某行数据进行修改时,需要对其加上行级排他锁,防止其他事物也对其进行修改,等数据修改完,事物提交时,自动释放。

3.1.2表锁(TM)

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

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

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

R是ROW行,S是SHARE共享,X是exclusive排他,独占锁的意思。

锁模式:0 锁描述:none

说明:没有锁,一般的SELECT,在表和行上都是0级锁

锁模式:1 锁描述:NULL

说明:Select有时会在v$locked_object出现。 其实起不了锁定的作用,他就是有一个通知的功能,根本阻止不了DDL,类似把执行计划中的对象通知对象所属的会话。

锁模式:2 锁描述:SS(Row-S)行级共享锁

说明:表结构共享锁,只跟X冲突,因为其他都是共享锁,RX,SRX虽然也有X,但是是行的X,表上还是共享的意思,2级锁在表级别和0-5级不冲突。

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

相关操作:Select for update,Lock For Update,Lock Row Share

锁模式:3 锁描述:SX(Row-X)行级排他锁

说明:表结构共享锁+被操作的记录的排它锁(若有DML操作),这个锁,也称为subexclusive table lock (SX),通常表示持有这个锁的事务已经更新了表行或发出了SELECT…for update。SX锁允许其他事务并发地查询、插入、更新、删除或锁定同一表中的行。因此,SX锁允许多个事务同时获得同一表的SX和子共享表锁。

相关操作:Insert, Update, Delete, Lock Row Exclusive

锁模式:4 锁描述:S(Share)共享锁

说明:表结构共享锁+所有记录共享锁(隐含),事务持有的共享表锁允许其他事务查询该表(SELECT…for update除外),但仅当单个事务持有共享表锁时才允许更新。因为多个事务可能同时持有一个共享表锁,持有这个锁不足以确保一个事务可以修改表。

相关操作:Create Index, Lock Share

锁模式:5 锁描述:SRX(S/Row-X)共享行级排他锁

说明:表结构共享锁+所有记录排它锁(隐含),这个锁也称为share-subexclusive table lock (SSX),比共享锁的限制更严格。一次只有一个事务可以获得给定表上的SSX锁。事务持有的SSX锁允许其他事务查询表(除了SELECT…for update)但不更新表。

相关操作:Lock Share Row Exclusive

锁模式:6 锁描述:X(Exclusive)排他锁

说明:表结构排它锁,这个锁是最严格的,禁止其他事务执行任何类型的DML语句或在表上放置任何类型的锁。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

相关操作:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

这几种模式看上去是不是感觉特别乱,不好理解,网上有一个通俗易懂的例子如下:

http://blog.itpub.net/30126024/viewspace-2156232/

参考Maclean的讲座,用一个珠宝店来做比喻说明:

珠宝店可以给大家免费参观,可以让你预定,可以试用后觉得好再买,可以把店都买下来。

第0类人,免费参观珠宝店的人

第1类人,免费参观珠宝店的老弱病残孕的客人

第2类人,预定了试用期,先买来几天,如果试用后觉得好再买

第3类人,直接到店里的目的就是立即购买

第4类人,把整个店的珠宝包下来,让别人参观,预定,但是不能买卖(这在ORACLE中叫只读锁,只允许别人读,也就是只允许第0,1,2类人来珠宝店,让别人只读方式的参观,不允许买卖,再来个第4类人,还是允许的,因为大家虽然都想包,但是大家的目的都是分享,而不是独占,所以是可以兼容的)

第5类人,它跟第4类人的区别只有一条,就是第5类人包下整个珠宝店后,另一个第5类人就不允许再包了(这在ORACLE中叫写锁定),也就是第5类人是单通道的,你在珠宝店里只能找到1个第5类人,不可能找出第2个第5类人,但是第5类人把珠宝店包下来后,仍然可以让第0,1,2类人参观,但不允许买卖

第6类人,它把整个珠宝店盘下来,不允许任何人有目的的参观,只允许免费参观,它是独占的,只允许0,1类人参观,其他人都不允许

--以上第2类人预定的,所以第3类跟6类人不兼容

--以上第3类人是要买珠宝的,所以第3类跟4,5,6类人都不兼容。

3.2 DDL锁

当正在进行的DDL操作作用于或引用对象时,数据字典(DDL)锁保护模式对象的定义。

在DDL操作期间,只有修改或引用的单个架构对象才会被锁定。数据库从不锁定整个数据字典。

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

3.2.1 DDL排他锁

排他DDL锁阻止其他会话获得DDL或DML锁。

大多数DDL操作需要对一个资源使用独占DDL锁,以防止对其他DDL操作的破坏干扰,这些操作可能修改或引用相同的模式对象。例如,当ALTER TABLE添加一个列时,DROP TABLE不允许删除一个表,反之亦然。

3.2.2 DDL共享锁

资源的共享DDL锁可以防止对冲突的DDL操作的破坏性干扰,但允许对类似的DDL操作进行数据并发。

这些锁会保护所引用对象的结构,使之不会被其他会话修改,但是允许修改数据。

例如,在运行CREATE PROCEDURE语句时,包含的事务为所有引用的表获取共享DDL锁。其他事务可以并发地创建引用相同表的过程,并获取相同表上的并发共享DDL锁,但是任何事务都不能获得任何引用表上的独占DDL锁。

共享DDL锁在DDL语句执行和自动提交期间有效。因此,持有共享DDL锁的事务可以保证引用的模式对象的定义在事务期间保持不变。

3.2.3 DDL可分解的解析锁

一个解析锁被称为一个可分解的解析锁,因为它不禁止任何DDL操作,并且可以被分解为允许冲突的DDL操作。

这些锁允许一个对象(如共享池中缓存的一个查询计划)向另外某个对象注册其依赖性。如果在被依赖的对象上执行DDL,Oracle会查看已经对该对象注册了依赖性的对象列表,并使这些对象无效。因此,这些锁是“可分解的”,它们不防止DDL出现。

3.3 系统锁

Oracle数据库使用各种类型的系统锁来保护内部数据库和内存结构。这些机制对于用户来说是透明的。

3.3.1 闩锁 Latches

闩锁是一种简单的低级序列化机制,用于协调对共享数据结构、对象和文件的多用户访问。

一般来说,latch由三种内存元素成:pid(进程id),内存地址和内存长度。Latch保证对共享数据结构的排它性访问,以此来保证内存结构的完整性不受到损坏。在多个会话同时修改或者检视(inspect)sga中同一个内存结构时,必须串行化访问以 保证sga中数据结构的完整性,latch 不会造成阻塞,是只会等待。当前一个进程释放latch后,如果其他多个进程同时请求的话,他们之间将出现竞争,没有一个入队机制,一旦前面进程释放锁定,后面的进程就蜂拥而上,没有先来后到的概念,并且这一切都发生的非常快,因为Latch的特点是快而短暂。

latch 争用多半要考虑系统及数据库自身设计问题,如绑定变量,热块及参数设置是否合理。

如多用户对主键的删除或者修改,是否有用户使用select… for update这样的语法,外键是否创建索引等。

闩锁保护共享内存资源在被多个进程访问时不受损坏。具体来说,锁存保护数据结构不受以下情况的影响:

•多个会话并发修改

•当被一个会话读取的同时被另一个会话修改

•当访问时释放重新分配内存

通常,在SGA中单个闩保护多个对象,例如,后台进程(例如DBWn和LGWR)从共享池(shared pool)分配内存创建数据结构,为了分配这些内存,这些进程用一个共享池闩来序列化访问,防止两个进程同时检查或修改共享池。内存被分配后,其它进程可能需要访问共享池,例如库缓存(library cache),用于解析,这时,进程闩只锁库缓存,不锁整个共享池。

与队列锁(如行锁)不同,闩不允许session排队。当一个闩锁可用时,发起请求的第一个session获取到闩。当一个进程在一个循环中重复地请求一个闩,叫做Latch spinning,而一个进程等待请求闩期间睡眠释放CPU,则叫做latch sleeping。

通常,一个Oracle进程在操作或查看一个数据结构时只获取闩极短的时间,例如,当处理一个雇员的薪水更新时,数据库可以获取和释放几千次锁。闩的实现依赖于操作系统,特别是对于怎么处理闩的等待。

闩的增加意味着并行度的减少,例如,过多的硬操作会竞争库缓存闩。V$LATCH视图包含了每种闩使用的统计信息,包括请求和等待每个闩锁的次数。

3.3.2 互斥锁 Mutexes

Mutex作为Latch的替代品,具有更快速获得,更小等优势。

获取一个mutex进需要大约30~35个指令, 而Latch则需要150~200个指令。一个mutex结构的大小大约为16 bytes,而在10.2版本中一个latch需要112个bytes,在更早的版本中是200个bytes。

互斥对象(互斥对象)是一种低级别的机制,它可以防止内存中的对象在被并发进程访问时老化或损坏。互斥锁类似于闩,但闩通常保护一组对象,而互斥锁保护单个对象。

互斥锁有几个好处:

•互斥锁可以减少争用。

因为一个闩保护多个对象,所以当多个进程试图同时访问这些对象时,它可能成为瓶颈。通过序列化对单个对象而不是组的访问,互斥锁减速了竞争。

•互斥锁消耗的内存比闩更少。

•在共享模式下,互斥锁允许多个会话并发引用。

3.3.3 内部锁 Internal Locks

内部锁是比闩和互斥锁更高级、更复杂的机制,可用于各种目的。

数据库使用以下类型的内部锁:

•字典缓存锁

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

•文件和日志管理锁

这些锁保护各种文件。例如,一种内部锁保护控制文件保证一次只有一个进程可以修改它。另一个锁协调在线重做日志文件的使用和归档。数据文件被锁定,以确保多个实例以共享模式挂载数据库,或一个实例以独占模式挂载数据库。因为文件锁和日志锁指示文件的状态,所以这些锁必须保持很长时间。

•表空间和撤销段锁

这些锁保护表空间和撤销段。例如,访问数据库的所有实例必须就表空间是联机还是脱机达成一致。undo段被锁定,保证只有一个数据库实例可以写入undo。

参考

https://www.modb.pro/db/51697?ms

https://docs.oracle.com/en/database/oracle/oracle-database/20/cncpt/index.html

https://baijiahao.baidu.com/s?id=1633934567936531363&wfr=spider&for=pc

https://www.cnblogs.com/jiyuqi/p/3701716.html

https://www.cnblogs.com/yingsong/p/7645965.html

https://blog.csdn.net/zdp072/article/details/25008873

https://www.cnblogs.com/sopost/archive/2011/11/17/2253352.html

https://www.askmaclean.com/archives/understanding-oracle-mutex.html

http://blog.itpub.net/30126024/viewspace-2156232/?t=1537068073785

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部