一文搞懂Oracle 0 至 6 级锁(附案例详解)

原文链接：https://mp.weixin.qq.com/s/b2nXJm1OhDjsRO_g5f9OCg （复制链接至浏览器，进入最佳阅读状态。关注“数据和云”公众号，了解更多行业资讯，阅读更多技术文章，专业技能up up up！）

11g Concepts中摘录的锁的信息
Table Locks (TM)
A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.
当事务通过INSERT、UPDATE、DELETE、MERGE和FOR UPDATE对表进行修改时，就会获得一个表锁，也称为TM锁子句，或锁表语句。DML操作需要表锁来为事务保留对表的DML访问权限，并防止DDL与事务冲突的操作。
A table lock can be held in any of the following modes:
Row Share (RS)
This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.
这个锁，也称为子共享表锁(SS)，表示持有表上锁的事务已锁定表中的行并打算锁定更新它们。行共享锁是表锁中限制最少的一种模式，它为表提供最高程度的并发性。
Row Exclusive Table Lock (RX)
This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.
这个锁，也称为subexclusive table lock (SX)，通常表示持有锁的事务已经更新了表行或发出了SELECT…FOR UPDATE。SX锁允许其他事务在同一表中同时查询、插入、更新、删除或锁定行。因此,SX锁允许多个事务为同一个表获取同步的SX和子共享表锁。
Share Table Lock (S)
A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.
事务持有的共享表锁允许其他事务查询表(除了SELECT…FOR UPDATE)，但只允许更新如果一个事务持有共享表锁。由于多个事务可能同时持有一个共享表锁，因此持有此锁不足以确保事务可以修改表。
Share Row Exclusive Table Lock (SRX)
This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.
这个锁，也称为共享-subexclusive table锁(SSX)，比共享表锁有更多的限制。一次只能获得一个事务SSX锁定给定的表。事务持有的SSX锁允许其他事务查询表(除了SELECT…FOR UPDATE)，但不更新表。
Exclusive Table Lock (X)
This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.
此锁是最严格的，禁止其他事务执行任何类型的DML语句或将任何类型的锁放在表上。
因为ORACLE要处理不同的并发功能，一旦处理不了那么多并发，就需要排队，为保证排队的公平就会出现各种优先级，因此衍生出很多锁模式，来支持不同业务层的并发需求。
在同一个session里面，你执行一个UPDATE语句，在表上有DML锁，那自己能去做DDL语句吗，比如DROP?
因为是同一个session，所以不涉及并发，自己做一个update不提交，随后drop table也是可以的
行锁：0、6两类锁
表锁：0、1、2、3、4、5、6七类锁
0（none）
1（null）
2（RS）
3（RX）
4（S）
5（SRX）
6（X）
R是ROW行，S是SHARE共享，X是eXclusive排他，独占锁的意思
0：null 空
一般的SELECT，在表和行上都是0级锁
1：null 空
1级锁有：Select有时会在v$locked_object出现。
2：Row-S 行共享(RS)：共享表锁，sub share
2级锁有：Lock Row Share，create index online
表锁的情况下
locked_mode 2不影响后一个locked_mode 2、3、4、5的会话，如果后一个会话locked_mode为6，则后一个会话操作会提示ora-00054错误。
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
行锁的情况下
locked_mode 2对应行锁0级锁，不影响其他会话。
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
3级锁有：Insert, Update, Delete, Select for update,Lock Row Exclusive
表锁的情况下
locked_mode 3不影响后一个locked_mode 3的会话，但如果后一个会话locked_mode为4,5,6，则后一个会话操作会提示ora-00054错误。
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
行锁的情况下
locked_mode 3的表锁对应行锁6级锁，两个会话对同一行则影响。
4：Share 共享锁(S)：阻止其他DML操作，share
4级锁有：Create Index, Lock Share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update/delete ... ; 可能会产生4,5的锁。
6：exclusive 独占(X)：独立访问使用，exclusive
6级锁有：Drop table, Drop Index, Alter table，Truncate table, Lock Exclusive
参考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类人都不兼容
把珠宝店当成表，那珠宝店里的珠宝柜子当成行
珠宝店，7种人对应7种模式，对应表的7种锁，0、1、2、3、4、5、6
柜子，打开或关闭2种状态对应2种模式，对应行的2种锁，0、6
珠宝店
（能不能同时进店，可以的）
表级锁相当于珠宝店大门锁，由门卫把关，表锁有 0,1,2,3,4,5,6对应7类人群，7类人群能出现其中几类人同时进店的情况，比如0、1、2、3类人同时进来了，或3类人同时进来好多人。
0级锁：就是没有锁，只有纯粹的select语句
0类人：免费参观，不跟其他顾客有任何竞争
1级锁：其实起不了锁定的作用，他就是有一个通知的功能，根本阻止不了DDL，类似把执行计划中的对象通知对象所属的会话
1类人：（老弱病残）免费参观，不跟其他顾客有任何竞争，但是这个顾客有权知道这个店以后的动态，比如是否拆了。
比如会话A执行select * from T，然后把执行计划保存到内存，为了保护执行计划是正确的，会话A要享受老弱病残孕幼的待遇，因为如果T表被别人删除了，那会话A生成的执行计划还有用吗？如果你不通知，A怎么知道这个表对象已经失效了，也就是有1号锁的对象，一旦被删除，它会通知拥有该对象的会话，这个对象删除了，请你重新再分析下你的SQL，1号锁是系统自动生成的
2级表锁：只跟X冲突，因为其他都是共享锁，RX,SRX虽然也有X,但是是行的X,表上还是共享的意思，2级锁在表级别和0-5级不冲突
2类人：有意图买珠宝的人，但现在只是先来查看下货是不是值得我买，所以要打开柜台，它只是一个SELECT动作。不会正面跟有免费参观、有买卖企图的顾客冲突。
2级表锁的产生方式
显式产生表级锁（LOCK TABLE table IN ROW SHARE MODE，显式产生一个RS表级锁）
注意, 显式产生表级锁只产生表级锁,不会级联产生行级锁，所以不会和其他会话产生行锁
3级锁：产生的原因（update、delete、select for update、显示锁表LOCK TABLE table IN ROW EXCLUSIVE MODE）
3类人：直接购买珠宝的人，所以要打开柜台
6号的X是整个表级的排它锁，显示锁表 LOCK TABLE table IN Exclusive MODE
珠宝柜子
(能不能同时打开同一个柜子,不能啊，没有这种概念)
行级锁相当于珠宝店柜台锁，由营业员把关，行锁有0、6两种对应柜台两种状态关闭、打开
通常顾客如果进入珠宝店，跑到柜台前有哪几种目的？
参观
柜子状态是关闭:0号模式
只是以参观为目的的顾客(第0类人、第1类人)，不存在资源竞争的问题，那还需要营业员拿锁出来打开柜台吗？不需要，因为没有资源竞争就不需要锁了。
0号模式的行级锁是因为0、1号的表级锁造成的，简单的select语句既是0级表级锁也是0级行级锁，也就是没锁
购买
柜子状态是打开：6号模式
第2类人，试用期（试用期间不能让别人用）
第3类人，立即购买（相当于我们的update、delete、select for update、LOCK TABLE table IN ROW EXCLUSIVE MODE语句）
总结：update、delete、select for update在行上都是产生排他锁
共享锁将允许别的共享锁存在，也就是共享跟共享是不冲突的。
比如用户A在表T上执行了UPDATE第1行，那么表t上有个表级的共享锁，那用户B在表T上执行了UPDATE第2行，那么也会在表t上有个表级的共享锁，虽然行上都是排它锁，但不是同一行，所以他们在行上没有冲突，在表上也没有冲突
比如用户A执行LOCK TABLE T IN ROW EXCLUSIVE MODE，用户B可以同时执行LOCK TABLE T IN ROW EXCLUSIVE MODE或LOCK TABLE T IN ROW SHARE MODE
有行级锁,必有表级锁（3级表锁引起6级行锁）
有表级锁,可以没有行级锁（显式锁，2,3,6号显示锁对应的表级锁）
6号模式的行级锁是因为2、3号的表级锁造成的
ORACLE的锁放在DATABASE BUFFER、LIBRARY CACHE的块里，不占用其他内存。其他db2、informix里，锁会占用内存,所以db2行锁多会升级成表锁
锁的类型根据锁的对象，分三大类
DML锁
DDL锁
内部锁或LATCH
DML和DDL涉及可见的SCHEMA对象
DML就是我们的DELETE,UPDATE,INSERT语句,它操作的是表，视图等,是可见的SCHEMA对象
DDL语句是ALTER TABLE,CREATE TABLE等语句同样对象是表，视图，存储过程等,也是可见的SCHEMA对象
内部锁或LATCH
用户是看不到的，看不见被封装起来的对象有哪些,就是内部锁（LIBRARY CACHE，DATABASE BUFFER），因为这些对象都是共享的，共享的对象就涉及到资源竞争，所以必须要用锁来进行限制资源的访问，对于保护内存的低级锁，我们叫做latch，它的机制类似红绿灯，一条马路是公用的，我们要设红绿灯吧。如果就是私人的，那就没必要设红绿灯，所以PGA没有latch。
DML是数据维护锁
DML锁是用来控制多个用户并行访问的数据确保一致性，SELECT是没有任何锁，只有select for update才有锁
select...for update会锁住结果行，导致其他session无法更新
DML锁是确保在某一事务期间修改的数据，不允许其他事务进行修改
DML锁确保被修改的表的事务还没有结束时，不允许其他事务在表上做DDL。
（当然本用户当前会话对表update不提交，本用户当前会话可以直接对该表做ddl，本用户重新开一个session是不可以对该表做ddl的，其他用户更是不能对该表做ddl）
DML锁定按对象级别不同分：
表级锁 TM（作用在表对象上，Table Manager）
行级锁 TX（作用在行对象上，Transaction eXclusive）
ORACLE不会发生行级锁升级成表级锁
这就好像四合院，四间房门合成一个院，四合院的大门就是表锁，每个房间就是行锁
如果在sqlserver数据库，当有3间房门要锁起来的话，那我就直接锁大门，因为sqlserver数据库，锁钥匙很贵重，为了节省钥匙就有了锁升级，从行级锁升级到页级锁，再从页级锁升级成表级锁。
查看当前会话sid
SQL> select distinct sid from v$mystat;
查询两个会话的锁信息
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (sid1,sid2) order by sid;
查询锁类型的具体含义
SQL> select * from V$LOCK_TYPE where type in ('TX','AE','TM','TO','OD');
TYPE NAME ID1_TAG ID2_TAG IS_USE DESCRIPTION
----- -------------- ----------------- ----------------- ------ ----------------------------------------------------------------------
TM DML object # table/partition YES Synchronizes accesses to an object
TX Transaction usn<<16 | slot sequence YES Lock held by a transaction to allow other transactions to wait for it
AE Edition Lock edition obj# 0 NO Prevent Dropping an edition in use
OD Online DDLs object # 0 NO Lock to prevent concurrent online DDLs
TO Temp Object object # 1 NO Synchronizes DDL and DML operations on a temp object
案例1
会话1的sid是161,会话2的sid是189
sid1 不commit
SQL> update test set id=11;
1 row updated
sid2一直创建不成功
SQL> alter table test add hid3 number;
sid3查询结果，发现sid1和和sid2的表级锁都是3
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
161 65547 1930 TX 6 0
161 88539 0 TM 3 0 --sid1的表级锁为3
161 100 0 AE 4 0
161 79833 1 TO 3 0
189 196612 2185 TX 6 0
189 88539 0 TM 3 0 --sid2的表级锁为3
189 100 0 AE 4 0
189 88539 0 OD 6 0
189 65547 1930 TX 0 4
189 79833 1 TO 3 0
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
SID FINAL_BLOCKING_SESSION EVENT
----------------- ---------------------- -----------
189 161 enq: TX - row lock contention
案例2
会话1的sid是161,会话2的sid是189
sid1不commit
SQL> update test set id=11;
1 row updated
sid2，直接报错
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
sid3修改ddl后，sid2再执行一次，sid查询结果
SQL> alter system set ddl_lock_timeout=60
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
161 88539 0 TM 3 0 --sid1的表级锁为3
161 100 0 AE 4 0
161 79833 1 TO 3 0
161 458768 1934 TX 6 0
189 88539 0 TM 0 6 --sid2当前表级锁为0，但是请求表级锁6
189 100 0 AE 4 0
189 0 1 AE 4 0
189 79833 1 TO 3 0
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
SID FINAL_BLOCKING_SESSION EVENT
--- ---------------------- -------------
189 161 enq: TM - contention
CREATE INDEX ONLINE
create index online会堵塞update吗？
不会
先执行update后不提交，后执行create index online不会报错，但是create index online一直处于堵塞状态
先执行create index online后，后执行update正常update，但是如果update不提交，则create index online一直处于堵塞状态
理解到：create index online在一行行创建索引过程中，并不是说这一行创建好索引了，再对这一行执行update时必须等到所有行都create index online完成后才会正常udpate，也就是说不管update在create index online前还是后，create index online都不影响update，倒是update如果没有提交会影响create index online。
如下两个实验会话1的sid是161,会话2的sid是189
实验1，先执行create index online,创建到一半后，update最小rowid的一行，按理说create index online应该已经过了这一行，应该会堵塞update会话，实际上并没有堵塞，update一样很快，到时最后查询下来发现update倒是把create index online堵塞了
sid1执行
SQL> select object_id from test1 where rowid in (select min(rowid) from test1);
OBJECT_ID
----------
4559
sid2执行，创建正常耗时6秒
SQL> create index ind_obd on test1 (OBJECT_ID) online;
Index created.
Elapsed: 00:00:06.06
SQL> drop index ind_obd;
Index dropped.
Elapsed: 00:00:00.14
SQL> create index ind_obd on test1 (OBJECT_ID) online;
在sid2执行的6秒期间，马上在sid1执行，发现sid1执行很快，并不堵塞
SQL> update test1 set object_id=1 where OBJECT_ID=4559;
32 rows updated.
sid3执行如下，发现sid1 161堵塞了sid2 189
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
SID FINAL_BLOCKING_SESSION EVENT
---- ---------------------- -------------------------
189 161 enq: TX - row lock contention
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
161 79833 1 TO 3 0
161 262151 1938 TX 6 0
161 88544 0 TM 3 0
161 100 0 AE 4 0
189 100 0 AE 4 0
189 79833 1 TO 3 0
189 131075 2139 TX 6 0
189 88544 0 DL 3 0
189 262151 1938 TX 0 4
189 88552 0 TM 4 0
189 88544 0 DL 3 0
189 88544 0 OD 4 0
189 88544 0 TM 2 0
13 rows selected.
实验2，先执行create index online,创建到一半后，update最大rowid的一行，按理说create index online应该还没到这一行，不会堵塞update会话，实验也发现确实是这样，update很快，到时最后查询下来是update把create index online堵塞了
sid1执行
SQL> select object_id from test1 where rowid in (select max(rowid) from test1);
OBJECT_ID
----------
85998
sid2执行，创建正常耗时6秒
SQL> create index ind_obd on test1 (OBJECT_ID) online;
Index created.
Elapsed: 00:00:06.06
SQL> drop index ind_obd;
Index dropped.
Elapsed: 00:00:00.14
SQL> create index ind_obd on test1 (OBJECT_ID) online;
在sid2执行的6秒期间，马上在sid1执行，发现sid1执行很快，并不堵塞
SQL> update test1 set object_id=1 where OBJECT_ID=85998;
32 rows updated.
sid3执行如下，发现sid1 161堵塞了sid2 189
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
SID FINAL_BLOCKING_SESSION EVENT
---- ---------------------- -------------------------
189 161 enq: TX - row lock contention
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
161 79833 1 TO 3 0
161 88544 0 TM 3 0
161 393242 2315 TX 6 0
161 100 0 AE 4 0
189 79833 1 TO 3 0
189 88544 0 TM 2 0
189 88546 0 TM 4 0
189 458777 1936 TX 6 0
189 100 0 AE 4 0
189 88544 0 DL 3 0
189 88544 0 DL 3 0
189 393242 2315 TX 0 4
189 88544 0 OD 4 0
13 rows selected.
查询锁对象是哪张表，哪一行的SQL
先查出堵塞的会话的SID，再如下查询堵塞的是哪张表，行是哪行
select a.sid, a.row_wait_obj#, a.row_wait_file#, a.row_wait_block#, a.row_wait_row#,b.owner,b.object_name from v$session a,dba_objects b where a.row_wait_obj#=b.object_id and sid in (XX);
select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid in (XX);--此次查询到row_wait_obj#=-1表示是持有锁的会话
row_wait_obj#:被等待的这行在哪个对象上
row_wait_file#:被等待的这行在哪个文件上
row_wait_block#:被等待的这行在哪个块上
row_wait_row#:被等待的这行在哪行上
统计信息收集遇到的锁
DBMS_STATS: GATHER_STATS_JOB encountered errors
ORA-04021: timeout occurred while waiting to lock object
收集统计信息的时候，需要对表或者索引的定义进行lock，其实这里的lock是library cache lock/pin~
不是锁定这个对象，而当收集某个对象的统计信息时，发现所需的对象已经被其它会话锁定，且在等待了一定时间后，其他会话仍然没有释放已持有该对象的锁，导致统计信息会话无法得到这个对象的锁。
收集统计信息会持有X mode的library cache lock（表在library cache里的representation），所以会有锁，但不是我们通常理解的enqueue锁。
其它用户在解析用到这个表的SQL时需要申请S mode的表 library cache object的library cache lock，此时就会有冲突/阻塞。