共计 33988 个字符，预计需要花费 85 分钟才能阅读完成。
本文结合示例详细的介绍下 Oracle 中锁的机制。
使用锁的目的是什么
为了解决多用户环境下并发操作相同的资源而造成的错误修改数据的问题。单用户环境下不需要考虑锁，因为所有操作都是串行的。
要点
锁的分类异常复杂，enqueue、latch、mutex 等，都是为了解决并发存在的，自己也有些混乱，所以也不过多解释了。下面列举一些对于 lock 的要点内容。
排他锁：
不允许相关的资源被共享。一个资源在一个时间点内只有一个事务能够获取该资源的排他锁，只有持有该锁的事务能够修改相关的资源，
其他想要获取锁的事务只能等待该事务因为 commit 或者 rollback 而释放排他锁。
共享锁：
允许相关的资源被共享。也就是说允许多个事务同时持有某个资源的共享锁。
对于一个 dml 操作，会对表以及行加锁，也就是 v$lock 中的 TM 锁和 TX 锁。
行级锁基本原理：
行级锁的信息是置于数据块中的，如果要修改某一条记录的值，其实就是在访问相应的 block，并且分配一个 ITL，然后通过 rowid 访问
rowpiece header，如果第二个字节 lock byte(lock byte 只占用 1 个字节，最大值为 255，这也是为什么 maxtrans 最大为 255）为 0，则将其改为分配的 ITL slot number。另外一个事务如果也想要修改数据，就会发现 lock byte 不为 0，如果第一个事务还没有结束，则第二个事务进入 enqueue 等待，也就是 transaction enqueue。
关于 transaction enqueue 有一个很有趣的例子，事务不一定是按照时间的先后顺序进行的。
具体地址在：
http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm#autoId12
对于 Table lock 来说可以分为以下几种类型:
1. Row Share (RS|SS)
2. Row Exclusive Table Lock (RX|SX)
3. Share Table Lock (S)
4. Share Row Exclusive Table Lock (SRX|SSX)
5. Exclusive Table Lock (X)
以下是 v$lock.LMODE 字段中的数字对应的锁类型
LMODE(Lockmode in which the session holds the lock):
0 -none
1 -null (NULL)
2 -row-S (SS)
3 -row-X (SX)
4 -share (S)
5 -S/Row-X (SSX)
6 -exclusive (X)
为了更好的开展下面的内容，这里列举一下各种 TM 锁类型的兼容情况。
详细验证情况会在 4 中给出。
表 1
| RS|SS | RX|SX | S | SRX|SSX | X |
RS|SS | √ | √ | √ | √ | × |
RX|SX | √ | √ | × | × | × |
S | √ | × | √ | × | × |
SRX|SSX | √ | × | × | × | × |
X | × | × | × | × | × |
顺便引用一下经典内容：
只有被修改时，行才会被锁定。
当一条语句修改了一条记录，只有这条记录上被锁定，在 Oracle 数据库中不存在锁升级。
当某行被修改时，它将阻塞别人对它的修改。
当一个事务修改一行时，将在这个行上加上行锁（TX），用于阻止其它事务对相同行的修改。
读永远不会阻止写。
读不会阻塞写，但有唯一的一个例外，就是 select …for update。
写永远不会阻塞读。
当一行被修改后，Oracle 通过回滚段提供给数据的一致性读
1. 分别模拟 insert，update 和 delete 造成阻塞
一个更新语句的简单描述
当我们更新一个表的记录的时候，会有两种锁产生，一种是 DML 锁（TM）也可以称作 table lock 还有一种事务锁（TX）也可以称作行锁
在 v$lock 中可以查看到。
例如下面的例子当中：
_dexter@FAKE>desc tun2_tab
Name Null? Type
————————————————————————- ——————————————–
ID NUMBER(38)
_dexter@FAKE>update tun2_tab set id =2 ;
2 rowsupdated.
_dexter@FAKE>select sid , type , lmode , request , block
from v$lock
where sid =(select sid from v$mystat where rownum<2) ;
SID TYPE LMODE REQUEST BLOCK
————– ———- ———- ———-
22 AE 4 0 0
22 TM 3 0 0
22 TX 6 0 0
AE 是和版本化（Workspace Manager）相关的东西，这里不再过多描述。
从上面的查询结果可以看到更新的时候
会添加一个 3 级的表锁，也就是 row-X (SX) 锁，保证在事务结束之前，表的结构不会被更改。多个事务可以同时持有相同表的 sx 锁。
还有一个 6 级的行锁，exclusive (X)，保证在事务结束之前，相关的行信息不会被更改。( 锁信息存放于 block 中)
ok 简单示例后，来进行这一小节的主要内容，阻塞示例。
insert 阻塞
insert 操作会对表加 3 级 rx 锁，和行排他锁，但是一般不会发生阻塞，因为读一致性的关系，在没提交之前只有当前 session 才可以操作新插入的行，对于其他事务来说 新增的记录是不可见的。
下面列举几种特殊的阻塞情况。
直接路径加载引发的阻塞
在 11gr2 中，可以使用 insert /*+ append */ intoselect 的方式执行直接路径加载。
或者 insert /*+append_values */ into values 的方式。
这里使用第二种。
Session1session_id=22：
_dexter@FAKE>insert /*+ append_values */ into tun2_tab values (1) ;
1 rowcreated.
_dexter@FAKE>select sid , type , lmode , request , block from v$lock where sid = (select sidfrom v$mystat where rownum<2) ;
SID TYPE LMODE REQUEST BLOCK
————– ———- ———- ———-
22 AE 4 0 0
22 TM 6 0 0
22 TX 6 0 0
可以看到使用直接路径加载的时候会对表加 6 级排他锁。根据 表 1 ，它会阻塞所有试图在表上加锁的事务。
Session2session_id=24：
_dexter@FAKE>update tun2_tab set id=3 ;
waiting…
看一下锁的情况：
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in (‘TM’, ‘TX’)
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
22 TM 82618 6 0 1 –session1 包含了表 6 级锁，它正在阻塞其他的事务
22 TX 524296 6 0 0
24 TM 82618 0 3 0 –session2 它正在请求表的 3 级锁。
Session1 | Session2 | Description | |
T1 | insert /*+ append_values */ into tun2_tab values (1) ; | 直接路径加载会对表加 6 级排他锁 | |
T2 | update tun2_tab set id=3 ; waiting… | update 需要对表加 3 级共享锁，因为互斥，session2 陷入阻塞状态 |
所以在直接路径加载的时候会对表加 6 级锁，阻塞其他事务对表加任意类型锁的操作。
（sqlldr 并行 + 直接路径加载的时候会加 4 级锁）
因为主键 | 唯一键引发的阻塞
_dexter@FAKE>alter table tun2_tab add primary key (id) ;
Table altered.
session1 session_id=22:
_dexter@FAKE>insert into tun2_tab values (1) ;
1 rowcreated.
session2 session_id=24:
_dexter@FAKE>insert into tun2_tab values (1) ;
waiting…
lockstatus :
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in (‘TM’, ‘TX’)
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
22 TM 82618 3 0 0
22 TX 196635 6 0 1
24 TX 65548 6 0 0
24 TM 82618 3 0 0
24 TX 196635 0 4 0
_sys@FAKE>select sid,seq#,event from v$session_wait where sid= 24 ;
SID SEQ# EVENT
——————– ———————————————–
24 104 enq: TX – row lock contention
这里发生了 row lock 等待事件。
可以看到
因为在拥有 primary key 列上插入了相同的值，第二个 session 除了持有自己本事务的 6 级排他锁之外，还在请求一个 4 级共享锁。这里发生了阻塞。如果第一个 session 提交。
第二个 session 会报错。
_dexter@FAKE>insert into tun2_tab values (1) ;
insert intotun2_tab values (1)
*
ERROR atline 1:
ORA-00001:unique constraint (DEXTER.SYS_C0014094) violated
Session1 | Session2 | Description | |
T1 | insert into tun2_tab values (1) ; | session1 插入数据这里涉及到了主键 | 唯一键 | |
T2 | insert into tun2_tab values (1) ; waiting … | session2 插入相同的记录，会发生阻塞，因为 session1 的操作是悬而未决的状态，session2 中的事务能否执行取决于 session1 中的事务是回滚还是提交 | |
T3 | commit | session 1 中的事务提交 | |
T4 | Raise error： ORA-00001: unique constraint (DEXTER.SYS_C0014094) violated |
Update 阻塞
这一部分的阻塞比较简单，只要发生 update 操作，就会对已有的行加 6 级排他锁，表上加 3 级共享锁。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
————————————————–
1 NN
2 NN
3 NN
session1 session_id=22:
_dexter@FAKE>update tun2_tab set name = ‘DEXTER’ where id=1 ;
1 rowupdated.
session2 session_id=18:
_dexter@FAKE>update tun2_tab set name =’dexter’ where id=2 ;
1 rowupdated.
session3 session_id=9:
_dexter@FAKE> update tun2_tab set name =’dexter’ where id=1;
waiting…
来看一下锁的情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 589850 0 6 0
9 TM 82618 3 0 0
18 TX 196629 6 0 0
18 TM 82618 3 0 0
22 TX 589850 6 0 1 –session1 正在阻塞 session 3
22 TM 82618 3 0 0
6 rowsselected.
由上可以看到，对 单个表可以加多个 3 级共享锁。
session2 因为修改的是 id=2 的记录，所以可以正常执行。
session3 由于修改的是 id=1 的记录，session1 这个时候正在修改，并且对这一行的资源加了 6 级的排他锁。所以 session3 发生了阻塞
需要等待 session 1 释放后才可以顺利执行。
Session1 | Session2 | Session3 | Description | |
T1 | update tun2_tab set name = ‘DEXTER’ where id=1 ; | session1 update 操作会对表加 3 级共享锁 | ||
T2 | update tun2_tab set name =’dexter’ where id=2 ; | session2 update 操作 也会对表加 3 级共享锁，由于更新的记录不包括 ssession1 中更新的记录 id=1。所以可以顺利执行 | ||
T3 | update tun2_tab set name =’dexter’ where id=1 ; waiting… | session3 update 操作 也会对表加 3 级共享锁，由于更新的记录包括 ssession1 中更新的记录 id=1。所以无法顺利执行 |
Delete 阻塞
其实对于 delete、update、insert 操作加锁操作大致相同，都会对表加 3 级共享锁，对修改的行加排他锁。
所以只要想要并发的修改表中相同的行，在 第一个获取锁的事务没有结束前，后面的时候都会发生阻塞。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
————————————————–
1 dexter
2 dexter
3 NN
session1 session_id=144 :
_dexter@FAKE>delete from tun2_tab where id =1 ;
1 rowdeleted.
session2 session_id=18 :
_dexter@FAKE>delete tun2_tab where id >1 ;
2 rowsdeleted.
session3 session_id=9 :
_dexter@FAKE>delete tun2_tab ;
waiting…
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 524317 0 6 0
9 TM 82618 3 0 0
18 TX 655383 6 0 0
18 TM 82618 3 0 0
144 TX 524317 6 0 1
144 TM 82618 3 0 0
6 rowsselected.
发生了阻塞，只有当 session 1 和 session 2 的事务结束后，session 3 才可以顺利完成。
Session1 | Session2 | Session3 | Description | |
T1 | delete from tun2_tab where id =1 ; | |||
T2 | delete tun2_tab where id >1 ; | session2 delete 操作因为不包括 session 1 中的 id= 1 的记录，所以可以顺利执行 | ||
T3 | delete tun2_tab ; waiting … | session3 delete 操做，因为需要获取 id=1，id>1 记录的事务锁，所以发生了等待。可以看到它首先是在等待 id= 1 的事务锁。 |
下面有两个有趣的实验
有趣小实验 1
_dexter@FAKE>select * from tun2_tab ;
ID NAME
————————————————–
1 dexter
2 dexter
3 NN
session1 session_id=22：
_dexter@FAKE>delete from tun2_tab where id =2 ;
1 rowdeleted.
session2 session_id=18：
_dexter@FAKE>update tun2_tab set name =’dexter’ where id>1 ;
waiting…
session3 session_id=9：
_dexter@FAKE>delete tun2_tab where id = 3 ;
1 rowdeleted.
查看一下锁的情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 393228 6 0 0
9 TM 82618 3 0 0
18 TX 131089 0 6 0
18 TM 82618 3 0 0
22 TX 131089 6 0 1
22 TM 82618 3 0 0
6 rowsselected.
这里比较有趣了，因为 session 2 update 的记录 包括 id= 2 这一行，所以在 id= 2 这一行加锁的时候，这里发生了 transaction enqueue，它还没来得及对任何记录加锁，就已经进入了等待中。
而 session3 执行的时候发现 id=3 的这一行还没有锁标示，所以它顺利的对 id=3 的记录加了锁。
这个时候我们 rollback 第一条记录后
session1：
_dexter@FAKE>rollback ;
Rollbackcomplete.
发现 session2 依然处于等待状态中
再看一下锁的情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 393228 6 0 1
9 TM 82618 3 0 0
18 TX 589840 6 0 0
18 TX 393228 0 6 0
18 TM 82618 3 0 0
这个时候我们可以看到 session2 又在等待 session3 的事务结束以便获取 id=3 这条记录的锁。
Session1 | Session2 | Session3 | Description | |
T1 | delete from tun2_tab where id =2 ; | |||
T2 | update tun2_tab set name =’dexter’ where id>1 ; waiting… | session 2 因为要获取 id= 2 的记录的事务锁所以发生阻塞，等待 session1 中的事务释放。 | ||
T3 | delete tun2_tab where id = 3 ; | 按照正常人的思维，比如说我。这一句应该等待 session2 中的事务才对。但是事实不是如此。因为 session2 陷入了阻塞，没还没有对 id= 3 的记录加上事务锁，所以 session3 可以顺利执行。 | ||
T4 | commit； | |||
T5 | still waiting | 因为需要 id= 3 的记录的事务锁，所以又被阻塞。 |
有趣小实验 2
session1session_id=144：
_dexter@FAKE>delete from tun2_tab where id =3 ;
1 rowdeleted.
session2session_id=18：
_dexter@FAKE> update tun2_tab set name =’dexter’ whereid>1 ;
waiting..
session3session_id=9：
_dexter@FAKE>delete tun2_tab where id = 2 ;
waiting..
看一下锁情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 196635 0 6 0
9 TM 82618 3 0 0
18 TX 196635 6 0 1
18 TM 82618 3 0 0
18 TX 458767 0 6 0
144 TM 82618 3 0 0
144 TX 458767 6 0 1
7 rowsselected.
session 3 也进入了等待中，因为 session2 先获取了 id=2 的行锁，然后等待 id=3 的行锁。
Session1 | Session2 | Session3 | Description | |
T1 | delete from tun2_tab where id =3 ; | |||
T2 | update tun2_tab set name =’dexter’ where id>1 ; | session 2 因为要获取 id= 3 的记录的事务锁所以发生阻塞，但是在阻塞之前，以及对 id=1| 2 的记录加了事务锁 | ||
T3 | delete tun2_tab where id = 2 ; waiting… | 发生等待。 |
更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-08/134077p2.htm
ITL 引起的阻塞
当 block 中没有多余的空间来添加 ITL entry 的时候，就会发生阻塞。具体可以看下面的例子：
_dexter@FAKE>create table tb_itl (id int , name varchar2(4000)) pctfree 0 initrans 1 ;
Tablecreated.
_dexter@FAKE>insert into tb_itl select level , ‘d’ from dual connect by level <= 10000 ;
10000 rowscreated.
_dexter@FAKE>commit ;
Commitcomplete.
_dexter@FAKE>update tb_itl set name=lpad(‘x’,2000,name) ;
10000 rowsupdated.
_dexter@FAKE>commit ;
Commitcomplete.
上面的操作保证至少第一个 block 中不会有多余的空间
selectt.id,
dbms_rowid.rowid_relative_fno(t.rowid)as “FNO#”,
dbms_rowid.rowid_block_number(t.rowid)as “BLK#”,
dbms_rowid.rowid_row_number(t.rowid) as”ROW#”
from dexter.tb_itl t
whererownum<5 ;
_dexter@FAKE>select t.id,
2 dbms_rowid.rowid_relative_fno(t.rowid) as “FNO#”,
3 dbms_rowid.rowid_block_number(t.rowid) as “BLK#”,
4 dbms_rowid.rowid_row_number(t.rowid) as “ROW#”
5 from dexter.tb_itl t
6 where id<5 ;
ID FNO# BLK# ROW#
——————– ———- ———-
1 6 187 0
2 6 187 1
3 6 187 2
4 6 187 3
先 dump 一下看一下 block 中剩余有几个 itl slot
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.016.00000a60 0x00c000ef.0284.14 C— 0 scn 0x0000.003d7a84
0x02 0x0003.01c.000009ea 0x00c00153.028c.1c —- 733 fsc 0x0000.00000000
只有 2 个事务槽了。
下面内容引用自网络。
每个 ITL entry 包括以下的内容：
Transactionid（Xid）: 8bytes。其中包括 rollback segment number, transaction table 中的 slot number 等。
Undoblock address（Uba）: 8bytes。其中包括 rollback segment block 的 DBA，sequence number 等。
Flags:1nibble。
—- =transaction is active, or committed pending cleanout
C— =transaction has been committed and locks cleaned out
-B– =this undo record contains the undo for this ITL entry
–U- =transaction committed (maybe long ago); SCN is an upper bound
—T =transaction was still active at block cleanout SCN
Locks:3nibbles. 也就是所谓的行级锁（row-level locks）
SCN orfree space credit: 6bytes. 如果这个事务已经 clean out，这个值就是 SCN；否则，前两个字节表示由这个事务释放的此 block 中的空间数。
我们来尝试更改一下数据
session1 session_id=144：
_dexter@FAKE>update tb_itl set name=lpad(‘x’,2000,name) where id =1 ;
1 rowupdated.
session2 session_id=18：
_dexter@FAKE> update tb_itl set name=lpad(‘x’,2000,name)where id =2 ;
1 rowupdated.
session3 session_id=21：
_dexter@FAKE>update tb_itl set name=lpad(‘x’,2000,name) where id =3 ;
waiting…
看一下锁信息
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in (‘TM’, ‘TX’)
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
18 TX 393241 6 0 1
18 TM 82846 3 0 0
21 TX 393241 0 4 0
21 TM 82846 3 0 0
144 TX 131088 6 0 0
144 TM 82846 3 0 0
6 rowsselected.
_sys@FAKE>select sid,seq#,event from v$session_wait where sid= 21 ;
SID SEQ# EVENT
———- ———————————————————
21 268 enq: TX – allocate ITL entry
_sys@FAKE>
因为在 block 187 中无法添加更多的ITL entry（拓展一个只需要24b）而引发的阻塞。
Session1 | Session2 | Session3 | Description | |
T1 | update tb_itl set name=lpad(‘x’,2000,name) where id =1 ; | |||
T2 | update tb_itl set name=lpad(‘x’,2000,name) where id =2 ; | |||
T3 | update tb_itl set name=lpad(‘x’,2000,name) where id =3 ; waiting… | 常理来说这里应该顺利执行才对，可是事实上，因为 block 中无法再拓展出 ITL entry，所以它被阻塞。 |
通常情况下不会发生这种情况。
解决办法：设置表的 inittrans 参数为合理值。
Bitmap 引起的阻塞
_dexter@FAKE>create table tb_bitmap_test (id number , gender varchar2(1)) ;
Tablecreated.
_dexter@FAKE>insert into tb_bitmap_test select level , ‘F’from dual connect by level <= 3;
3 rowscreated.
_dexter@FAKE>insert into tb_bitmap_test select level , ‘M’from dual connect by level <= 2;
2 rowscreated.
_dexter@FAKE>create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test(gender) ;
Indexcreated.
_dexter@FAKE>select * from tb_bitmap_test ;
ID GE
————
1 F
2 F
3 F
1 M
2 M
session1 session_id=144:
_dexter@FAKE>update tb_bitmap_test set gender=’M’ where id=1 and gender=’F’ ;
1 rowupdated.
session2 session_id=18:
_dexter@FAKE>delete tb_bitmap_test where gender=’M’ and id = 1;
waiting…
session3 session_id=9 :
_dexter@FAKE>insert into tb_bitmap_test values (1,’S’) ;
1 rowcreated.
锁情况：
_sys@FAKE>@lock
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TM 82847 3 0 0
9 TX 196626 6 0 0
18 TX 327710 6 0 0
18 TM 82847 3 0 0
18 TX 589854 0 4 0
144 TX 589854 6 0 1
144 TM 82847 3 0 0
7 rowsselected.
不管是 gender=’M’ 或者 ‘F’ , 只要涉及到这两个字段的值的 dml 操作都将进入等待当中（包括 insert）
因为第一个 session 锁住了整个 bitmap segment。但是只要gender 的值不涉及 M 或者 F 即可顺利执行。所以 session3 顺利的执行。
Session1 | Session2 | Session3 | Description | |
T1 | update tb_bitmap_test set gender=’M’ where id=1 and gender=’F’ ; | 因为有了 Bitmap 索引，所以这个操作会索引表中所有 gender=’M’和‘F’的记录，并且会阻塞相关的 insert 操作 | ||
T2 | delete tb_bitmap_test where gender=’M’ and id = 1; waiting… | 这里发生了阻塞 | ||
T3 | insert into tb_bitmap_test values (1,’S’) ; | 只要 gender 的值不等于 M 或者 F 即可顺利执行 |
2. 模拟 RI 锁定导致阻塞的场景。
初始化环境
_dexter@FAKE>create table tun2_p (id int primary key) ;
Tablecreated.
_dexter@FAKE>create table tun2_c (pid references tun2_p(id)) ;
Tablecreated.
_dexter@FAKE>insert into tun2_c values (1) ;
insert intotun2_c values (1)
*
ERROR atline 1:
ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated – parent key not found
这里因为有引用完整性约束，子表中的内容必须与父表中的内容匹配。因为父表中没有 id= 1 的记录，所以这里报错
主表插入
_dexter@FAKE>insert into tun2_p values (2) ;
1 rowcreated.
lock status：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
TUN2_P 9 TM 82949 3 0 0
9TX 589829 6 0 0
TUN2_C 9 TM 82952 3 0 0
主表更新（子表中没有引用的记录）
_dexter@FAKE>update tun2_p set id=3 where id=2 ;
1 rowupdated.
lock status：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
9TX 262144 6 0 0
TUN2_P 9 TM 82949 3 0 0
主表删除（子表中没有引用的记录）
_dexter@FAKE>delete tun2_p where id=3 ;
1 rowdeleted.
lock status:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
9TX 524294 6 0 0
TUN2_P 9 TM 82949 3 0 0
如果 upadte 和 delete 操作中不包含子表引用的记录，就不会对子表加锁。
而 insert 相对比较复杂一点，它会级联的将子表锁定。
如果在子表引用的记录上发生更改，则会报错。例如：
_dexter@FAKE>update tun2_p set id=3 where id=1 ;
updatetun2_p set id=3 where id=1
*
ERROR atline 1:
ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated – child record found
子表插入
_dexter@FAKE>insert into tun2_c values (2) ;
1 rowcreated.
lock:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
TUN2_P 9 TM 82949 3 0 0
9TX 589825 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表更新
_dexter@FAKE>update tun2_c set pid=1 where pid=2 ;
1 rowupdated.
lock
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
TUN2_P 9 TM 82949 3 0 0
9TX 655390 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表删除
_dexter@FAKE>delete from tun2_c where pid=1 ;
2 rowsdeleted.
lock:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
TUN2_P 9 TM 82949 3 0 0
9TX 196635 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表的记录一定会引用到父表的记录，所以在对子表进行 dml 操作的时候，都会锁定父表。
复杂示例
两个表中现在么有任何记录。
session1 session_id=9:
_dexter@FAKE>insert into tun2_p values (1) ;
1 rowcreated.
看一下锁的情况：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
TUN2_C 9 TM 82952 3 0 0
TUN2_P 9 TM 82949 3 0 0
9TX 262149 6 0 0
可以看到，当向父表中插入记录的时候，会同时锁定父表和子表，加表的 3 级共享锁。
session1 没提交之前其他事���无法看到父表中的 id= 1 的记录，我们再来尝试一下向子表中插入 pid= 1 的记录
session2 session_id=18:
_dexter@FAKE>insert into tun2_c values (1) ;
waiting …
可以看到 session2 进入了阻塞状态，我们来查看一下锁的情况
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
—————————————- —- ———- ———- ———- ———-
TUN2_C 9 TM 82952 3 0 0
9TX 262149 6 0 1
TUN2_P 9 TM 82949 3 0 0
18TX 262149 0 4 0
TUN2_C 18 TM 82952 3 0 0
18TX 589848 6 0 0
TUN2_P 18 TM 82949 3 0 0
7 rowsselected.
首先我们可以看到，session2也有两个 TM 表锁 ，分别锁定了子表和父表。 这说明在子表更新数据的时候，也会对引用的对象加锁。
然后我们还看到，子表陷入了等待当中。
这是因为 session2 中的事务是否能够成功执行，取决于 session1 中的事务状态。而 session1 中的事务现在是悬而未决的状态。
是不是有点和读一致性搞混了？觉得第二个 session 中的事务不应该进入阻塞当中，而是直接报错？
它不像读一致性，可以在查询的时候根据 undo 获取一个一致性视图。
在事务执行的时候，只和数据的当前状态相关。
第一个 session 的事务 rollback 后
session2 就会报错
_dexter@FAKE>insert into tun2_c values (1) ;
insert intotun2_c values (1)
*
ERROR atline 1:
ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated – parent key not found
Session1 | Session2 | Description | |
T1 | insert into tun2_c values (1) ; | ||
T2 | insert into tun2_c values (1) ; waiting… | 正常理解，这里应该直接报错，ORA-02291 才对，但是这里没有，因为父表中 id= 1 的记录还是悬而未决的状态。这是智能呢？还是智能呢？还是智能呢？那就是智能吧。 | |
T3 | rollback | ||
T4 | Raise error ORA-02291: integrity constraint (DEXTER.SYS_C0014143) violated – parent key not found | 一切都明了了，报错了。 |
3. 从 mode 2-6 的 TM 锁相互间的互斥示例。
再次引用这张表
| RS|SS | RX|SX | S | SRX|SSX | X |
RS|SS | √ | √ | √ | √ | × |
RX|SX | √ | √ | × | × | × |
S | √ | × | √ | × | × |
SRX|SSX | √ | × | × | × | × |
X | × | × | × | × | × |
介绍一些操作
lock table tun2_tab in ROW SHARE mode ; | lmode=2 |
lock table tun2_tab in ROW EXCLUSIVE mode ; | lmode=3 |
lock table tun2_tab in SHARE MODE ; | lmode=4 |
lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ; | lmode=5 |
lock table tun2_tab in EXCLUSIVE MODE ; | lmode=6 |
下面的示例演示验证上表的内容
Row Share (RS)
Also called a subshare table lock (SS)
Session1 session_id=35 :
dexter@STARTREK>create table tun2_tab (x int) ;
Tablecreated.
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode nowait ;
Table(s)Locked.
session2 session_id=160:
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s)Locked.
dexter@STARTREK>commit;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in EXCLUSIVE MODE ;
waiting …
看一下锁的情况
sys@STARTREK>@lock
SID TY ID1 LMODE REQUEST BLOCK
———— ———- ———- ———- ———-
35 TM 76917 2 0 1
160 TM 76917 0 6 0
RS|SS锁和 X 锁是不能并发的，但是可以兼容其他类型的锁。
Row Exclusive TableLock（RX|SX）
Also called a subexclusive table lock (SX)
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
Session2 session_id=160：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
waiting …
看一下锁的情况
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
———— ———- ———- ———- ———-
35 TM 76917 3 0 1
160 TM 76917 0 4 0
RX|SX 与 S 锁是无法并发的，经测试 SRX|SSX 锁也一样无法与 RX|SX 锁并发。
本文结合示例详细的介绍下 Oracle 中锁的机制。
使用锁的目的是什么
为了解决多用户环境下并发操作相同的资源而造成的错误修改数据的问题。单用户环境下不需要考虑锁，因为所有操作都是串行的。
要点
锁的分类异常复杂，enqueue、latch、mutex 等，都是为了解决并发存在的，自己也有些混乱，所以也不过多解释了。下面列举一些对于 lock 的要点内容。
排他锁：
不允许相关的资源被共享。一个资源在一个时间点内只有一个事务能够获取该资源的排他锁，只有持有该锁的事务能够修改相关的资源，
其他想要获取锁的事务只能等待该事务因为 commit 或者 rollback 而释放排他锁。
共享锁：
允许相关的资源被共享。也就是说允许多个事务同时持有某个资源的共享锁。
对于一个 dml 操作，会对表以及行加锁，也就是 v$lock 中的 TM 锁和 TX 锁。
行级锁基本原理：
行级锁的信息是置于数据块中的，如果要修改某一条记录的值，其实就是在访问相应的 block，并且分配一个 ITL，然后通过 rowid 访问
rowpiece header，如果第二个字节 lock byte(lock byte 只占用 1 个字节，最大值为 255，这也是为什么 maxtrans 最大为 255）为 0，则将其改为分配的 ITL slot number。另外一个事务如果也想要修改数据，就会发现 lock byte 不为 0，如果第一个事务还没有结束，则第二个事务进入 enqueue 等待，也就是 transaction enqueue。
关于 transaction enqueue 有一个很有趣的例子，事务不一定是按照时间的先后顺序进行的。
具体地址在：
http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm#autoId12
对于 Table lock 来说可以分为以下几种类型:
1. Row Share (RS|SS)
2. Row Exclusive Table Lock (RX|SX)
3. Share Table Lock (S)
4. Share Row Exclusive Table Lock (SRX|SSX)
5. Exclusive Table Lock (X)
以下是 v$lock.LMODE 字段中的数字对应的锁类型
LMODE(Lockmode in which the session holds the lock):
0 -none
1 -null (NULL)
2 -row-S (SS)
3 -row-X (SX)
4 -share (S)
5 -S/Row-X (SSX)
6 -exclusive (X)
为了更好的开展下面的内容，这里列举一下各种 TM 锁类型的兼容情况。
详细验证情况会在 4 中给出。
表 1
| RS|SS | RX|SX | S | SRX|SSX | X |
RS|SS | √ | √ | √ | √ | × |
RX|SX | √ | √ | × | × | × |
S | √ | × | √ | × | × |
SRX|SSX | √ | × | × | × | × |
X | × | × | × | × | × |
顺便引用一下经典内容：
只有被修改时，行才会被锁定。
当一条语句修改了一条记录，只有这条记录上被锁定，在 Oracle 数据库中不存在锁升级。
当某行被修改时，它将阻塞别人对它的修改。
当一个事务修改一行时，将在这个行上加上行锁（TX），用于阻止其它事务对相同行的修改。
读永远不会阻止写。
读不会阻塞写，但有唯一的一个例外，就是 select …for update。
写永远不会阻塞读。
当一行被修改后，Oracle 通过回滚段提供给数据的一致性读
1. 分别模拟 insert，update 和 delete 造成阻塞
一个更新语句的简单描述
当我们更新一个表的记录的时候，会有两种锁产生，一种是 DML 锁（TM）也可以称作 table lock 还有一种事务锁（TX）也可以称作行锁
在 v$lock 中可以查看到。
例如下面的例子当中：
_dexter@FAKE>desc tun2_tab
Name Null? Type
————————————————————————- ——————————————–
ID NUMBER(38)
_dexter@FAKE>update tun2_tab set id =2 ;
2 rowsupdated.
_dexter@FAKE>select sid , type , lmode , request , block
from v$lock
where sid =(select sid from v$mystat where rownum<2) ;
SID TYPE LMODE REQUEST BLOCK
————– ———- ———- ———-
22 AE 4 0 0
22 TM 3 0 0
22 TX 6 0 0
AE 是和版本化（Workspace Manager）相关的东西，这里不再过多描述。
从上面的查询结果可以看到更新的时候
会添加一个 3 级的表锁，也就是 row-X (SX) 锁，保证在事务结束之前，表的结构不会被更改。多个事务可以同时持有相同表的 sx 锁。
还有一个 6 级的行锁，exclusive (X)，保证在事务结束之前，相关的行信息不会被更改。( 锁信息存放于 block 中)
ok 简单示例后，来进行这一小节的主要内容，阻塞示例。
insert 阻塞
insert 操作会对表加 3 级 rx 锁，和行排他锁，但是一般不会发生阻塞，因为读一致性的关系，在没提交之前只有当前 session 才可以操作新插入的行，对于其他事务来说 新增的记录是不可见的。
下面列举几种特殊的阻塞情况。
直接路径加载引发的阻塞
在 11gr2 中，可以使用 insert /*+ append */ intoselect 的方式执行直接路径加载。
或者 insert /*+append_values */ into values 的方式。
这里使用第二种。
Session1session_id=22：
_dexter@FAKE>insert /*+ append_values */ into tun2_tab values (1) ;
1 rowcreated.
_dexter@FAKE>select sid , type , lmode , request , block from v$lock where sid = (select sidfrom v$mystat where rownum<2) ;
SID TYPE LMODE REQUEST BLOCK
————– ———- ———- ———-
22 AE 4 0 0
22 TM 6 0 0
22 TX 6 0 0
可以看到使用直接路径加载的时候会对表加 6 级排他锁。根据 表 1 ，它会阻塞所有试图在表上加锁的事务。
Session2session_id=24：
_dexter@FAKE>update tun2_tab set id=3 ;
waiting…
看一下锁的情况：
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in (‘TM’, ‘TX’)
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
22 TM 82618 6 0 1 –session1 包含了表 6 级锁，它正在阻塞其他的事务
22 TX 524296 6 0 0
24 TM 82618 0 3 0 –session2 它正在请求表的 3 级锁。
Session1 | Session2 | Description | |
T1 | insert /*+ append_values */ into tun2_tab values (1) ; | 直接路径加载会对表加 6 级排他锁 | |
T2 | update tun2_tab set id=3 ; waiting… | update 需要对表加 3 级共享锁，因为互斥，session2 陷入阻塞状态 |
所以在直接路径加载的时候会对表加 6 级锁，阻塞其他事务对表加任意类型锁的操作。
（sqlldr 并行 + 直接路径加载的时候会加 4 级锁）
因为主键 | 唯一键引发的阻塞
_dexter@FAKE>alter table tun2_tab add primary key (id) ;
Table altered.
session1 session_id=22:
_dexter@FAKE>insert into tun2_tab values (1) ;
1 rowcreated.
session2 session_id=24:
_dexter@FAKE>insert into tun2_tab values (1) ;
waiting…
lockstatus :
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in (‘TM’, ‘TX’)
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
22 TM 82618 3 0 0
22 TX 196635 6 0 1
24 TX 65548 6 0 0
24 TM 82618 3 0 0
24 TX 196635 0 4 0
_sys@FAKE>select sid,seq#,event from v$session_wait where sid= 24 ;
SID SEQ# EVENT
——————– ———————————————–
24 104 enq: TX – row lock contention
这里发生了 row lock 等待事件。
可以看到
因为在拥有 primary key 列上插入了相同的值，第二个 session 除了持有自己本事务的 6 级排他锁之外，还在请求一个 4 级共享锁。这里发生了阻塞。如果第一个 session 提交。
第二个 session 会报错。
_dexter@FAKE>insert into tun2_tab values (1) ;
insert intotun2_tab values (1)
*
ERROR atline 1:
ORA-00001:unique constraint (DEXTER.SYS_C0014094) violated
Session1 | Session2 | Description | |
T1 | insert into tun2_tab values (1) ; | session1 插入数据这里涉及到了主键 | 唯一键 | |
T2 | insert into tun2_tab values (1) ; waiting … | session2 插入相同的记录，会发生阻塞，因为 session1 的操作是悬而未决的状态，session2 中的事务能否执行取决于 session1 中的事务是回滚还是提交 | |
T3 | commit | session 1 中的事务提交 | |
T4 | Raise error： ORA-00001: unique constraint (DEXTER.SYS_C0014094) violated |
Update 阻塞
这一部分的阻塞比较简单，只要发生 update 操作，就会对已有的行加 6 级排他锁，表上加 3 级共享锁。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
————————————————–
1 NN
2 NN
3 NN
session1 session_id=22:
_dexter@FAKE>update tun2_tab set name = ‘DEXTER’ where id=1 ;
1 rowupdated.
session2 session_id=18:
_dexter@FAKE>update tun2_tab set name =’dexter’ where id=2 ;
1 rowupdated.
session3 session_id=9:
_dexter@FAKE> update tun2_tab set name =’dexter’ where id=1;
waiting…
来看一下锁的情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 589850 0 6 0
9 TM 82618 3 0 0
18 TX 196629 6 0 0
18 TM 82618 3 0 0
22 TX 589850 6 0 1 –session1 正在阻塞 session 3
22 TM 82618 3 0 0
6 rowsselected.
由上可以看到，对 单个表可以加多个 3 级共享锁。
session2 因为修改的是 id=2 的记录，所以可以正常执行。
session3 由于修改的是 id=1 的记录，session1 这个时候正在修改，并且对这一行的资源加了 6 级的排他锁。所以 session3 发生了阻塞
需要等待 session 1 释放后才可以顺利执行。
Session1 | Session2 | Session3 | Description | |
T1 | update tun2_tab set name = ‘DEXTER’ where id=1 ; | session1 update 操作会对表加 3 级共享锁 | ||
T2 | update tun2_tab set name =’dexter’ where id=2 ; | session2 update 操作 也会对表加 3 级共享锁，由于更新的记录不包括 ssession1 中更新的记录 id=1。所以可以顺利执行 | ||
T3 | update tun2_tab set name =’dexter’ where id=1 ; waiting… | session3 update 操作 也会对表加 3 级共享锁，由于更新的记录包括 ssession1 中更新的记录 id=1。所以无法顺利执行 |
Delete 阻塞
其实对于 delete、update、insert 操作加锁操作大致相同，都会对表加 3 级共享锁，对修改的行加排他锁。
所以只要想要并发的修改表中相同的行，在 第一个获取锁的事务没有结束前，后面的时候都会发生阻塞。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
————————————————–
1 dexter
2 dexter
3 NN
session1 session_id=144 :
_dexter@FAKE>delete from tun2_tab where id =1 ;
1 rowdeleted.
session2 session_id=18 :
_dexter@FAKE>delete tun2_tab where id >1 ;
2 rowsdeleted.
session3 session_id=9 :
_dexter@FAKE>delete tun2_tab ;
waiting…
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 524317 0 6 0
9 TM 82618 3 0 0
18 TX 655383 6 0 0
18 TM 82618 3 0 0
144 TX 524317 6 0 1
144 TM 82618 3 0 0
6 rowsselected.
发生了阻塞，只有当 session 1 和 session 2 的事务结束后，session 3 才可以顺利完成。
Session1 | Session2 | Session3 | Description | |
T1 | delete from tun2_tab where id =1 ; | |||
T2 | delete tun2_tab where id >1 ; | session2 delete 操作因为不包括 session 1 中的 id= 1 的记录，所以可以顺利执行 | ||
T3 | delete tun2_tab ; waiting … | session3 delete 操做，因为需要获取 id=1，id>1 记录的事务锁，所以发生了等待。可以看到它首先是在等待 id= 1 的事务锁。 |
下面有两个有趣的实验
有趣小实验 1
_dexter@FAKE>select * from tun2_tab ;
ID NAME
————————————————–
1 dexter
2 dexter
3 NN
session1 session_id=22：
_dexter@FAKE>delete from tun2_tab where id =2 ;
1 rowdeleted.
session2 session_id=18：
_dexter@FAKE>update tun2_tab set name =’dexter’ where id>1 ;
waiting…
session3 session_id=9：
_dexter@FAKE>delete tun2_tab where id = 3 ;
1 rowdeleted.
查看一下锁的情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 393228 6 0 0
9 TM 82618 3 0 0
18 TX 131089 0 6 0
18 TM 82618 3 0 0
22 TX 131089 6 0 1
22 TM 82618 3 0 0
6 rowsselected.
这里比较有趣了，因为 session 2 update 的记录 包括 id= 2 这一行，所以在 id= 2 这一行加锁的时候，这里发生了 transaction enqueue，它还没来得及对任何记录加锁，就已经进入了等待中。
而 session3 执行的时候发现 id=3 的这一行还没有锁标示，所以它顺利的对 id=3 的记录加了锁。
这个时候我们 rollback 第一条记录后
session1：
_dexter@FAKE>rollback ;
Rollbackcomplete.
发现 session2 依然处于等待状态中
再看一下锁的情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 393228 6 0 1
9 TM 82618 3 0 0
18 TX 589840 6 0 0
18 TX 393228 0 6 0
18 TM 82618 3 0 0
这个时候我们可以看到 session2 又在等待 session3 的事务结束以便获取 id=3 这条记录的锁。
Session1 | Session2 | Session3 | Description | |
T1 | delete from tun2_tab where id =2 ; | |||
T2 | update tun2_tab set name =’dexter’ where id>1 ; waiting… | session 2 因为要获取 id= 2 的记录的事务锁所以发生阻塞，等待 session1 中的事务释放。 | ||
T3 | delete tun2_tab where id = 3 ; | 按照正常人的思维，比如说我。这一句应该等待 session2 中的事务才对。但是事实不是如此。因为 session2 陷入了阻塞，没还没有对 id= 3 的记录加上事务锁，所以 session3 可以顺利执行。 | ||
T4 | commit； | |||
T5 | still waiting | 因为需要 id= 3 的记录的事务锁，所以又被阻塞。 |
有趣小实验 2
session1session_id=144：
_dexter@FAKE>delete from tun2_tab where id =3 ;
1 rowdeleted.
session2session_id=18：
_dexter@FAKE> update tun2_tab set name =’dexter’ whereid>1 ;
waiting..
session3session_id=9：
_dexter@FAKE>delete tun2_tab where id = 2 ;
waiting..
看一下锁情况：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
————– ———- ———- ———- ———-
9 TX 196635 0 6 0
9 TM 82618 3 0 0
18 TX 196635 6 0 1
18 TM 82618 3 0 0
18 TX 458767 0 6 0
144 TM 82618 3 0 0
144 TX 458767 6 0 1
7 rowsselected.
session 3 也进入了等待中，因为 session2 先获取了 id=2 的行锁，然后等待 id=3 的行锁。
Session1 | Session2 | Session3 | Description | |
T1 | delete from tun2_tab where id =3 ; | |||
T2 | update tun2_tab set name =’dexter’ where id>1 ; | session 2 因为要获取 id= 3 的记录的事务锁所以发生阻塞，但是在阻塞之前，以及对 id=1| 2 的记录加了事务锁 | ||
T3 | delete tun2_tab where id = 2 ; waiting… | 发生等待。 |
更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-08/134077p2.htm
Share Table Lock（S）
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
Table(s)Locked.
Session2 session_id=160：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
waiting …
锁情况：
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
———— ———- ———- ———- ———-
35 TM 76917 4 0 1
160 TM 76917 0 3 0
S 锁比较特殊，它不允许与 RX|SX 也就是 3 级锁并发，但是允许多个 S 锁并发的在多个事务中持有。
例如两个 sessoin 同时执行下面的命令
locktable tun2_tab in SHARE MODE ;
可以看到下面的 lock 信息：
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
———— ———- ———- ———- ———-
35 TM 76917 4 0 0
129 TM 76917 4 0 0
但是 S 锁无法与 SRX|SSX 和 X 锁并发。
Share Row ExclusiveTable Lock (SRX|SSX）
Also called a share-subexclusive table lock (SSX)
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s)Locked.
Session2 session_id=129：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
waiting …
锁情况：
sys@STARTREK>@lock
SID TY ID1 LMODE REQUEST BLOCK
———— ———- ———- ———- ———-
35 TM 76917 5 0 1
129 TM 76917 0 3 0
SRX|SSX锁无法与 RX|SX 以上的锁并发持有。
Exclusive Table Lock(X)
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in EXCLUSIVE MODE ;
Table(s)Locked.
Session2 session_id=129：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
waiting …
X锁无法与任何锁并发。
上面的内容笔者已经使用 PLSQL 模拟出来，详情见附录1
下面列举几种常用操作的加锁情况，是否会因为 table lock 发生阻塞，只要代入表中即可。
| RS|SS | RX|SX | S | SRX|SSX | X |
RS|SS | √ | √ | √ | √ | × |
RX|SX | √ | √ | × | × | × |
S | √ | × | √ | × | × |
SRX|SSX | √ | × | × | × | × |
X | × | × | × | × | × |
insert /*+ append_values */ into values | X |
insert /*+ append */ into select | X |
insert into values | RX |
insert into select | RX |
sql*ldr Conventional | RX |
sql*ldr append | X |
sql*ldr append+parallel | S |
update | RX |
delete | RX |
select for update | RX |
4. 导致死锁的 SQL 示例。
下面给出一个最简单的示例。
_dexter@FAKE>select * from a ;
X
———-
1
2
_dexter@FAKE>select * from b ;
X
———-
1
2
s1 t1:
_dexter@FAKE>update b set x=3 where x= 1 ;
1 rowupdated.
s2 t2:
_dexter@FAKE>update a set x=3 where x=1 ;
1 rowupdated.
s1 t3:
_dexter@FAKE>update a set x=5 where x= 1 ;
s2 t4:
_dexter@FAKE>update b set x=5 where x=1 ;
s1 t5:
_dexter@FAKE>update a set x=5 where x= 1 ;
update aset x=5 where x= 1
*
ERROR atline 1:
ORA-00060:deadlock detected while waiting for resource
raise error00600 deadlock
_dexter@FAKE>select * from b ;
X
———-
3
2
s2 t6:
stillwaiting
直到 s1 结束事务
Session1 | Session2 | Description | |
T1 | update b set x=3 where x= 1 ; | Session1 更新 B 表记录 | |
T2 | _dexter@FAKE> update a set x=3 where x=1 ; | Session2 更新 A 表记录 | |
T3 | update a set x=5 where x= 1 ; waiting… | Session1 在事务中尝试修改 Sessino2 事务中修改的 A 表的数据 | |
T4 | update b set x=5 where x=1 ; waiting… | Session2 在事务中尝试修改 Sessino1 事务中修改的 B 表的数据 | |
T5 | ERROR at line 1: ORA-00060: deadlock detected while waiting for resource raise error 00600 deadlock | 上面的就是死锁的情况。Session1 会 Raise 一个 00060 死锁的 error。Sessino2 still waiting。 |
从这里可以看到，由于逻辑错误锁引发的死锁。两个事务都在等待对方释放锁资源。
第一个争抢资源导致死锁的语句会被取消（只是取消这一个语句，而不是结束整个事务）
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134077.htm
