Oracle中的锁(LOCK)机制

最新推荐文章于 2023-02-14 23:50:22 发布

hotmocha

最新推荐文章于 2023-02-14 23:50:22 发布

阅读量832

点赞数

使用锁的目的是什么

为了解决多用户环境下并发操作相同的资源而造成的错误修改数据的问题。单用户环境下不需要考虑锁，因为所有操作都是串行的。下面的文章简要的介绍了一下

要点

锁的分类异常复杂，enqueue、latch、mutex等，都是为了解决并发存在的，自己也有些混乱，所以也不过多解释了。下面列举一些对于lock的要点内容。

l 排他锁：

不允许相关的资源被共享。一个资源在一个时间点内只有一个事务能够获取该资源的排他锁，只有持有该锁的事务能够修改相关的资源，

其他想要获取锁的事务只能等待该事务因为commit或者rollback而释放排他锁。

l 共享锁：

允许相关的资源被共享。也就是说允许多个事务同时持有某个资源的共享锁。

对于一个dml操作，会对表以及行加锁，也就是v$lock中的TM锁和TX锁。

l 行级锁基本原理：

行级锁的信息是置于数据块中的，如果要修改某一条记录的值，其实就是在访问相应的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

l 对于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)

数字越大锁级别越高, 影响的操作越多。

1级锁有：Select，有时会在v$locked_object出现。

2级锁有：Select for update,Lock For Update,Lock Row Share

select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。

3级锁有：Insert, Update, Delete, Lock Row Exclusive

没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

4级锁有：Create Index, Lock Share

locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。

00054, 00000, "resource busy and acquire with NOWAIT specified"

// *Cause: Resource interested is busy.

// *Action: Retry if necessary.

5级锁有：Lock Share Row Exclusive

具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。

6级锁有：

Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

为了更好的开展下面的内容，这里列举一下各种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

insert /*+ append_values */ into tun2_tab values (1) ;

直接路径加载会对表加6级排他锁

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…	发生等待。

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锁并发。

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
*sqlldr Conventional**	RX
*sqlldr append**	X
*sqlldr append+parallel**	S
update	RX
delete	RX
select for update	RX

4.导致死锁的SQL示例。

下面给出一个最简单的示例。

_dexter@FAKE>select * from a ;

----------

_dexter@FAKE>select * from b ;

----------

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 ;

----------

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 。