万字长文深入探究Oracle DML锁机制

1.1. 锁的基本概念

• 锁的定义：锁（lock）机制用于管理对共享资源的并发访问，用于多用户的环境下，可以保证数据库的完整性和一致性。锁是防止访问相同资源的事务之间的破坏性，交互的机制。既可以是用户对象(例如表或行)，也可以是对用户不可见的系统对象(例如共享数据结构和数据字典行)。
• 锁的作用：在并发事务之间防止破坏性的交互作用，不需要用户的动作，自动使用最低的限制级别，在事务处理期间保持。
• 为了确保并发用户在存取同一数据库对象时的正确性（即无丢失修改、可重复读、不读“脏”数据），数据库中引入了锁机制。基本的锁类型有两种：排它锁（Exclusive locks记为X锁）和共享锁（Share locks记为S锁）。

1. 排它锁：若事务T对数据D加X锁，则其它任何事务都不能再对D加任何类型的锁，直至T释放D上的X锁；一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。
2. 共享锁：若事务T对数据D加S锁，则其它事务只能对D加S锁，而不能加X锁，直至T释放D上的S锁；一般要求在读取数据前要向该数据加共享锁，所以共享锁又称为读锁。

引用： 共享锁（S）： 小明和小胖是好朋友，有一天他们俩得到了一份纸质版且纸比较破武功秘籍，为了让这份武功秘籍不再丢失什么内容，于是就用一个玻璃盒子存储起来。

这时候小胖提议在盒子上加了一把锁，保护好里面的内容不被破坏。

加上锁之后，大家都能看，但是不能修改，难度增大了！大家都看到你们加锁了，并且这东西是免费的，所以我也要加锁，于是这秘籍的外表框上加上了很多的锁，这些锁统称为共享锁（S）。

我们通过图可以知道，共享属锁加上之后，我们只能看到秘籍（内容），但是想撕去一点点秘籍或修改上面的内容都不行，所以大家都只能看，不能修改，这就是共享锁为什么又称为读锁的原因。 如果要修改上面的内容或者换一本秘籍在玻璃里面展示的话，这个时候需要将所有的锁都去除掉，才能操作，否则就只能看。 排它锁（X）： 同样，有一本武功秘籍，这次被小胖捡到了，于是：

这个时候小明来了，看到这个箱子被小胖加上了排它锁（X），别人看不到也修改不了里面的内容，同时别人想加上其他锁也不能。

最后，小胖给了小明一个排它锁的钥匙，这个时候小明也能看箱子里面的内容，同时也可以修改箱子里面的内容。

1.2. Oracle 锁机制介绍

根据保护对象的不同，单实例Oracle数据库锁可以分为以下几大类：

• DML lock（data locks，数据锁）：用于保护数据的完整性；
• DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）；
• internal locks，latches，mutex，pin：保护内部数据库结构；

本文主要讨论DML（也可称为data locks，数据锁）锁。从封锁粒度（封锁对象的大小）的角度看，Oracle DML锁共有两个层次，即行级锁和表级锁。

1.3. 显式锁定和隐式锁定

有两种类型：显式锁定和隐式锁定。

• 隐式锁：Oracle锁被自动执行，并且不要求用户干预的锁。对于SQL语句隐式锁是必须的，依赖被请求的动作。隐式锁定除SELECT外，对所有的SQL语句都发生。
• 显式锁：用户也可以手动锁定数据。
• 隐式锁定：这是Oracle中使用最多的锁。通常用户不必声明要对谁加锁，Oracle 自动可以为操作的对象加锁，这就是隐式锁定。
• 显式锁定：用户可以使用命令明确的要求对某一对象加锁。显式锁定很少使用。

1.3.1. 显式锁定

只有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

1.3.2. 隐式锁定

隐式锁定：

Select * from table_name…… Insert into table_name…… Update table_name…… Delete from table_name…… Select * from table_name for update

1.4. Oracle的TX锁（行级锁、事务锁）

许多对Oracle不太了解的技术人员可能会以为每一个TX锁代表一条被封锁的数据行，其实不然。TX的本义是Transaction（事务），当一个事务第一次执行数据更改（Insert、Update、Delete）或使用SELECT… FOR UPDATE语句进行查询时，它即获得一个TX（事务）锁，直至该事务结束（执行COMMIT或ROLLBACK操作）时，该锁才被释放。所以，一个TX锁，可以对应多个被该事务锁定的数据行。

在Oracle的每行数据上，都有一个标志位来表示该行数据是否被锁定。Oracle不像其它一些DBMS（数据库管理系统）那样，建立一个链表来维护每一行被加锁的数据，这样就大大减小了行级锁的维护开销，也在很大程度上避免了其它数据库系统使用行级封锁时经常发生的锁数量不够的情况。

1.5. TM锁（表级锁）

1.5.1. 意向锁的引出

表是由行组成的，当我们向某个表加锁时，一方面需要检查该锁的申请是否与原有的表级锁相容；另一方面，还要检查该锁是否与表中的每一行上的锁相容。比如一个事务要在一个表上加S锁，如果表中的一行已被另外的事务加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

意向锁的含义是：如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。如：对表中的任一行加锁时，必须先对它所在的表加意向锁，然后再对该行加锁。这样一来，事务对表加锁时，就不再需要检查表中每行记录的锁标志位了，系统效率得以大大提高。

1.5.2. 意向锁的类型

由两种基本的锁类型（S锁、X锁），可以自然地派生出两种意向锁：

• 意向共享锁（Intent Share Lock，简称IS锁）：如果要对一个数据库对象加S锁，首先要对其上级结点加IS锁，表示它的后裔结点拟（意向）加S锁；
• 意向排它锁（Intent Exclusive Lock，简称IX锁）：如果要对一个数据库对象加X锁，首先要对其上级结点加IX锁，表示它的后裔结点拟（意向）加X锁。

另外，基本的锁类型（S、X）与意向锁类型（IS、IX）之间还可以组合出新的锁类型，理论上可以组合出4种，即：S+IS，S+IX，X+IS，X+IX，但稍加分析不难看出，实际上只有S+IX有新的意义，其它三种组合都没有使锁的强度得到提高（即：S+IS=S，X+IS=X，X+IX=X，这里的“=”指锁的强度相同）。所谓锁的强度是指对其它锁的排斥程度。

这样我们又可以引入一种新的锁的类型：

• 共享意向排它锁（Shared Intent Exclusive Lock,简称SIX锁） ：如果对一个数据库对象加SIX锁，表示对它加S锁，再加IX锁，即SIX=S+IX。例如：事务对某个表加SIX锁，则表示该事务要读整个表（所以要对该表加S锁），同时会更新个别行（所以要对该表加IX锁）。

这样数据库对象上所加的锁类型就可能有5种：S、X、IS、IX、SIX。

具有意向锁的多粒度封锁方法中任意事务T要对一个数据库对象加锁，必须先对它的上层结点加意向锁。申请封锁时应按自上而下的次序进行；释放封锁时则应按自下而上的次序进行；具有意向锁的多粒度封锁方法提高了系统的并发度，减少了加锁和解锁的开销。

1.5.3. Oracle的TM锁（表级锁）

Oracle的DML锁（数据锁）正是采用了上面提到的多粒度封锁方法，其行级锁虽然只有一种（即X锁），但其TM锁（表级锁）类型共有5种，分别称为共享锁（S锁）、排它锁（X锁）、行级共享锁（RS锁）、行级排它锁（RX锁）、共享行级排它锁（SRX锁），与上面提到的S、X、IS、IX、SIX相对应。需要注意的是，由于Oracle在行级只提供X锁，所以与RS锁（通过SELECT … FOR UPDATE语句获得）对应的行级锁也是X锁（但是该行数据实际上还没有被修改），这与理论上的IS锁是有区别的。

一方面，当Oracle执行SELECT…FOR UPDATE、INSERT、UPDATE、DELETE等DML语句时，系统自动在所要操作的表上申请表级RX锁（SELECT…FOR UPDATE、INSERT、UPDATE、DELETE），当表级锁获得后，系统再自动申请TX锁，并将实际锁定的数据行的锁标志位置位（指向该TX锁）；另一方面，程序或操作人员也可以通过LOCK TABLE语句来指定获得某种类型的TM锁。

下表总结了Oracle中各SQL语句产生TM锁的情况：

我们可以看到，通常的DML操作（SELECT…FOR UPDATE、INSERT、UPDATE、DELETE），在表级获得的只是意向锁（RX），其真正的封锁粒度还是在行级；另外，Oracle数据库的一个显著特点是：在缺省情况下，单纯地读数据（SELECT）并不加锁，Oracle通过回滚段（Rollback segment）来保证用户不读“脏”数据。这些都极大地提高了系统的并发程度。

由于意向锁及数据行上锁标志位的引入，极大地减小了Oracle维护行级锁的开销，这些技术的应用使Oracle能够高效地处理高度并发的事务请求。

1.6. 锁转换和锁升级

数据库在必要时执行锁转换。在锁转换中，数据库自动将较低限制的表锁转换为较高限制的其它锁定。(锁转换不同于锁升级，锁升级发生在当某个粒度级别持有许多锁（例如行），数据库将其提高到更高粒度级别（例如表）Oracle数据库永远不会升级锁。

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

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

1.7. 实验探究

1.7.1. 同表操作同一行数据引发的锁阻塞

（此处省略修改同一数据行引发,现象于SELECT…FOR UPDATE基本相同）

首先建立3个会话，其中两个（以下用Session 1、Session 2表示）以SCOTT用户连入数据库，以操作Oracle提供的示例表（DEPT、EMP）；另一个（以下用Session 3表示）以SYS用户连入数据库，用于监控动态性能视图。

Session 1:

Session 3:

执行完SELECT…FOR UPDATE 语句后，Session 1（SID为33）在DEPT表上获得Row Exclusive锁（v$locked_object , all_objects 关联查询）

DEPT表中有4条记录被锁定，但TX锁只有1个，这也与上面的理论分析一致。（查询v$lock所得）

Session 2:

Session 3:

在DEPT表上除了Session 1（SID为33）持有Row Exclusive锁外，又增加了Session 2（SID为164）持有的Row Exclusive锁；而从第二个脚本看到，Session 2的TX锁的LOCK_TYPE为None，其申请的锁类型（REQUEST）为6（即Exclusive），而其ID1、ID2的值与Session 1所持有的TX锁的ID1、ID2相同，Session 1的TX锁的阻塞域（BLOCK）为1，这就说明了由于Session 1持有的TX锁，阻塞了Session 2的更新操作（Session 2所更新的行与Session 1所锁定的行相冲突）。还可以看出，Session 2先申请表级的TM锁，后申请行（事务）级的TX锁，这也与前面的理论分析一致。

当锁导致阻塞我们也可以从等待事件维度分析锁问题:

下面，将Session1的事务进行回滚，解除对Session 2的阻塞，再对系统进行监控。

Session3：

可以看到，Session 1的事务所持有的锁已经释放，系统为Session 2的事务分配了回滚段，而其TX锁也已经获得。再将会话2的事务进行回滚。可以看到，TM与TX锁已全部被释放。

1.7.2. 深入探究：

根据官方文档归纳总结：

v$lock视图列出当前系统持有的或正在申请的所有锁的情况，其主要字段说明如下：

其中在TYPE字段的取值中，本文只关心TM、TX两种DML锁类型；

关于ID1、ID2，TYPE取值不同其含义也有所不同：

v$locked_object视图列出当前系统中哪些对象正被锁定，其主要字段说明如下：

在v$lock视图中,有两列id1和id2

其中id1就是xidusn+xidslot的组合,而id2就是xidsqn的值表示slot实际使用的次数

因此可以算出他们的实际值 xidusn=trunc(id1/65536) xidslot=mod(id1,65536)

因此transaction id就表示为(xidusn,xidslot,xidsqn)

通过上述实验我们可以进行运算 TX锁对应id1与transaction的关系。 SQL> select trunc(262161/65536) xidusn,mod(262161,65536) xidslot from dual;

得出vlocked_object对应xidusn xidslot字段相等。同时也可以在vtransaction中查到。进而分析当拥有TX锁时，会占用相关的回滚段。同时我们也可以查到对应的回滚段信息。

继续向下分析我们可以发觉，两个TX等级分别为none锁和exclusive。并且ID1,ID2编号相同，所以发生堵塞。而且我们可以发现表级锁意向锁申请成功。这与上面概念相同。

1.7.3. 子表完整性引发所等待

构造父子表: t3表有如下字段：id , name , product_id ，其中列为product_id外键，其父表为t2。

SQL>create table t3 (id number primary key,name varchar2(20),product_id number); –创建主表t2 SQL>create table t2 (id number primary key,name varchar2(20)); –给t3表加入外键约束 – SQL> alter table t3 add constraint FK_PRODUCTSTAT_PRODUCTID foreign key (PRODUCT_id) references t2 (ID) ON DELETE CASCADE ; 插入表数据 SQL> insert into t2 values(1,‘dh’); SQL> insert into t2 values(2,‘cc’); SQL> insert into t2 values(3,‘cc’); SQL> insert into t3 values(1,‘dh’,1); SQL> insert into t3 values(2,‘dh’,1); SQL> insert into t3 values(3,‘dh’,2); SQL> commit;

Session 1:

Session 3:

Session 1（SID为164）在t2表中先插入一条id为4的记录，session 1获得了T2,T3表上的Row Exclusive锁，及一个TX锁。

Session 2:

被阻塞 Session 3:

Session 2（SID为162）向T3表中出入一条新记录，Session 1刚插入，但还未提交，Session 2获得了T3 表上的Row Exclusive锁，另外由于插入记录，还分配了回滚段及一个TX锁，但由于Session 2的插入语句是否成功取决于Session 1的事务是否进行提交，所以它被阻塞，表现为Session 2以Share（REQUEST=4）方式等待Session 1释放其持有的TX锁。这时Session 1如果提交，Session 2的插入也将执行成功，而如果Session 1回滚，由于不符合参照完整性，Session 2将报错：

Session 1:

Session 2:

Session 2持有的锁也被全部释放。

1.7.4. 外键未加索引引发的锁阻塞

EMP表上的DEPTNO列为外键，但没有在该列上建索引。

Session 1:

Session 3：

首先Session 1（SID为164）做了一个删除操作，但由于条件（0=1）为永假，所以实际上并没有一行被删除，从监控脚本可以看出Session 1在EMP,DEPT表上获得Row Exclusive锁，但由于没有实际的行被删除，所以并没有TX锁，也没有为Session 1分配回滚段。

Session 2：

该语句虽然也不会删除实际数据，但却被阻塞，查看系统的锁情况：

Session 3：

Session 2申请在EMP表上加SHARE锁（REQUEST=4），但该申请被Session 1阻塞，因为Session 1已经在EMP表上获得了Row Exclusive锁，与SHARE锁不相容。

下面我们对Session 1进行回滚后，再进行监控。

Session 3：

Session 2在8i版本在会在EMP表上获得Share锁，又在DEPT表上获得Row Exclusive锁。

但由于测试环境为11g，获得的锁为row exclusive，并且由于没有实际的行被修改，Session 2并没有获得TX锁。 在Oracle中，如果子表的外键上没有加索引，当在父表上删除记录时，会先在子表上申请获得Share锁，之后再在父表上申请Row Exclusive锁。由于表级Share锁的封锁粒度较大，所以容易引起阻塞，从而造成性能问题。 当在外键上建立索引后，在父表上删除数据将不再对子表上加Share锁，如下所示：

Session 1：

Session 3：

可以看到，在EMP表DEPTNO列上建立索引后，在DEPT表上执行DELETE操作，不再要求在EMP表上加Share锁，只是在DEPT表上加Row Exclusive锁，封锁的粒度减小，引起阻塞的可能性也减小。

并且Session 2执行不会再引起锁等待。

本文部分源自于互联网，如有侵权，请联系：
wechat: 704012932
email:  pkweibu@163.com
或在文末留言。

墨天轮原文链接：https://www.modb.pro/db/32312（复制链接至浏览器或点击文末阅读原文查看）

关于作者

卜维，云和恩墨技术顾问，Oracle 11g OCM。中国DBA联盟成员,拥有OCM、OBCA证书,长期服务于金融行业。现负责公司Oracle、MySQL数据库运维方面的技术工作；热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。

END