DML锁之间的相容关系以及实验

最新推荐文章于 2022-05-20 09:48:03 发布

daimin1983

最新推荐文章于 2022-05-20 09:48:03 发布

阅读量1k

点赞数

分类专栏： Oracle 文章标签： insert table object sql delete 数据库

本文链接：https://blog.csdn.net/daimin1983/article/details/2554852

版权

Oracle 专栏收录该内容

61 篇文章 1 订阅

订阅专栏

主要讲DML锁之间的相容关系以及一些实验：

DML锁主要分：行级锁和表级锁

行级锁

行级锁（TX锁）：即事务锁，当事务进行insert,update,delete以及使用select ..for update语句时会获取到一个TX锁，直至事务结束（commit或者rollback）该锁才被释放。

在每个数据块的头部都有一个ITL（相关事务列表），当一个事务需要修改数据块中的数据（insert,update,delete以及使用select ..for update）时，ITL会将该数据块的事务记录在ITL中的一项（即slot）中。

数据块中的项数由数据块所在表定义中的INITRANS和MAXTRANS参数所决定

表级锁

表级锁（TM锁）

在给表加锁时，一方面要检查申请加的锁是否和与原有的表级锁相容；另一方面还要检查该锁是否与该表中的每一行上的锁是否相容；在进行第而个检查时会带来一定的性能问题。所以引入意向锁

1、意向锁指：如果对一个节点加意向锁，则需要指明该节点的下一层正在被加的锁。也就是说对任意一个节点加锁时，都需要在其上一层节点加意向锁。比如：当对表中一行记录加锁时，先对行所在的表加意向锁，然后再对该行加锁。

2、意向锁的类型：

意向共享锁（IS锁）：给某个节点加IS锁，那么该节点的下层节点意向加S锁；

意向排它锁（IX锁）：给某个节点加IX锁，那么该节点的下层节点意向加X锁；

基本锁类型（X锁，S锁）与意向锁（IS锁，IX锁）组合有：S+IS，S+IX，X+IS，X+IX，其中S+IS，S+IX，X+IS，X+IX组合锁并没有使锁对其他锁的排斥程度（即锁的强度）提高，所以S+IX该组合锁很特殊，提高了锁的强度。

所以将数据库中的表级锁的模式有：S，X，IS，IX，SIX，对应与通常所说的S，X，RS，RX，SRX。

3、加锁和解锁的顺序：

加锁顺序：自上层向下层方向进行加锁；

解锁顺序：自下层向上层方向给解锁；

图1：数据库中TM锁的相容距阵

T2 T1	S	X	RS	RX	SRX
S	Y	N	Y	N	N
X	N	N	N	N	N
RS	Y	N	Y	Y	Y
RX	N	N	Y	Y	N
SRX	N	N	Y	N	N

图2 TM锁的小结：

SQL语句	表锁模式	允许的表锁模式
Select …from table…	none	RS，RX，S，SRX，X
Insert into table…	RX	RS，RX
Update table …	RX	RS，RX
Delete from table …	RX	RS，RX
Selete …from table for update of …	RS	RS，RX，S，SRX
Lock table table in row share mode	RS	RS，RX，S，SRX
Lock table table in row exclusive mode	RX	RS，RX
Lock table table in share mode	S	RS，S
Lock table table in share row exclusive mode	SRX	RS
Lock table table in exclusive mode	X	无

注：通常进行的DML操作（insert,update,delete以及使用select ..for update），在表级所获得的锁只是意向锁（RS或者RX锁），真正的封锁粒度还是在行级。

首先将一些用到监控数据库锁的脚本写出如下：

1、showlock.sql:通过连接v$locked_object,all_objects两个视图，显示哪些对象被哪些会话锁住

/* showlock.sql*/

column o_name format a10

column lock_type format a20

column object_name format a15

select rpad(oracle_username,10) o_name,

session_id sid,

decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,

object_name,xidusn,xidslot,xidsqn

from v$locked_object,all_objects

where v$locked_object.OBJECT_ID=all_objects.object_id;

2、showalllock.sql:主要显示当前所有TM，TX锁的信息

/* showalllock.sql*/

select sid,type,id1,id2,

decode(LMODE,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,

request,ctime,block

from v$lock

where Type in ('TM','TX');

实验如下：

--在外键上没有建索引的子表

CREATE TABLE emp

(emp_id number(10),

name varchar2(20) ,

dept_id number(2) CONSTRAINT fk_emp

references dept(dept_id)

ON DELETE CASCADE

);

--在外键上有建索引的子表

CREATE TABLE emp1

(emp_id number(10),

name varchar2(20) ,

dept_id number(2) CONSTRAINT fk_emp1

references dept(dept_id)

ON DELETE CASCADE

);

-- Create/Recreate indexes on the Foreign Key

create index EMP1_DEPT_ID on EMP1 (DEPT_ID);

alter table emp add (constraint pk_emp primary key (emp_id));

alter table emp1 add (constraint pk_emp1 primary key (emp_id));

insert into dept values (10,'ACCOUNTING');

insert into dept values (11,'SALES');

insert into dept values (12,'RESEARCH');

insert into dept values (14,'TT');

insert into emp values (2001,'Jason',10);

insert into emp values (2002,'Mike',11);

insert into emp values (2003,'Green',12);

insert into emp values (2004,'Grey',10);

insert into emp1 values (2001,'Jason',10);

insert into emp1 values (2002,'Mike',11);

insert into emp1 values (2003,'Green',12);

insert into emp1 values (2004,'Grey',10);

实验1：

会话1中：

Daimin用户下的三个会话会话1，会话2，会话3，会话5；sys用户下的会话4；

SQL> insert into dept(dept_id,name) values(15,'daimin');

1 row inserted

会话4中查看锁信息：

SQL> @c:/showlock.sql

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

---------- ---------- -------------------- --------------- ---------- ---------- ----------

DAIMIN 142 Row share EMP1 20 36 498

DAIMIN 142 Row Exclusive DEPT 20 36 498

DAIMIN 142 Row share EMP 20 36 498

SQL> @c:/showalllock.sql;

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------

142 TM 55606 0 Row Exclusive 0 297 0 –----dept表的表锁

142 TX 1310756 498 Exclusive 0 297 0 –----dept表的行锁

142 TM 55614 0 Row share 0 297 0

142 TM 55612 0 Row share 0 297 0

在会话2中：

对子表进行更新操作：

SQL> update emp set name='daimin' where emp_id=2004;

1 row updated

会话4中查看锁信息：

SQL> @c:/showlock.sql

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

---------- ---------- -------------------- --------------- ---------- ---------- ----------

DAIMIN 132 Row Exclusive EMP 12 10 658

DAIMIN 142 Row share EMP 20 36 498

DAIMIN 142 Row Exclusive DEPT 20 36 498

DAIMIN 142 Row share EMP1 20 36 498

SQL> @c:/showalllock.sql;

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------

132 TX 786442 658 Exclusive 0 66 0 --emp表的行级缩

132 TM 55612 0 Row Exclusive 0 66 0

142 TX 1310756 498 Exclusive 0 792 0

142 TM 55612 0 Row share 0 792 0

142 TM 55606 0 Row Exclusive 0 792 0

142 TM 55614 0 Row share 0 792 0

分析：在父表都被插入操作时，子表上被加了表级锁RS锁，当对子表进行更新操作时，对子表需要申请一个表级锁RX锁，由于RS锁和RX锁是相容的，所以这两个事务并不相互阻塞。

会话3中：

对子表加一个排它锁（X锁）：

SQL> lock table emp in exclusive mode;

此时发现事务处于阻塞状态。

在会话4中查看锁信息：

SQL> @c:/showlock.sql

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

---------- ---------- -------------------- --------------- ---------- ---------- ----------

DAIMIN 132 Row Exclusive EMP 12 10 658

DAIMIN 139 None EMP 0 0 0

DAIMIN 142 Row share EMP 20 36 498

DAIMIN 142 Row Exclusive DEPT 20 36 498

DAIMIN 142 Row share EMP1 20 36 498

SQL> @c:/showalllock.sql;

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------

132 TM 55612 0 Row Exclusive 0 642 1

132 TX 786442 658 Exclusive 0 642 0

139 TM 55612 0 None 6 69 0

142 TM 55614 0 Row share 0 1368 0

142 TX 1310756 498 Exclusive 0 1368 0

142 TM 55612 0 Row share 0 1368 1

142 TM 55606 0 Row Exclusive 0 1368 0

分析：从锁信息可以看出，会话3中的事务连TM锁（EMP表级锁）都没有获取得到，所以更不会获取得到行级锁，因为加锁的方向是自上层向下层方向进行的，也就是说是先加TM表级锁，然后再给加行级锁。主要原因是由于申请需要加的X锁与当前EMP表中的RX锁以及RS锁不相容，所以拒绝了X锁的申请，导致该事务受阻塞。

会话5中：

SQL> update emp set name='daimin' where emp_id=2004;

该事务也被阻塞

在会话4中查看锁信息：

SQL> @c:/showlock.sql

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN

---------- ---------- -------------------- --------------- ---------- ---------- ----------

DAIMIN 132 Row Exclusive EMP 12 10 658

DAIMIN 139 Row Exclusive EMP 0 0 0 –获取到RX表级缩

DAIMIN 142 Row share EMP 20 36 498

DAIMIN 142 Row Exclusive DEPT 20 36 498

DAIMIN 142 Row share EMP1 20 36 498

SQL> @c:/showalllock.sql;

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK

---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------

132 TM 55612 0 Row Exclusive 0 2019 0

132 TX 786442 658 Exclusive 0 2019 1

139 TX 786442 658 None 6 18 0 –没有获取到EMP的行级锁

139 TM 55612 0 Row Exclusive 0 18 0 ----获取到EMP表的表级锁

142 TX 1310756 498 Exclusive 0 2745 0

142 TM 55612 0 Row share 0 2745 0

142 TM 55606 0 Row Exclusive 0 2745 0

142 TM 55614 0 Row share 0 2745 0

分析：从上面的锁信息中可以看出，由于会话5对EMP表加RX锁，由于原来EMP表中存在的RX，RS锁是与RX锁相容的，所以对会话5对EMP表加RX表级锁是成功的。但是，

由于会话5中的事务申请获取EMP表的锁的所在行正好与会话2中事务中更新的行一样，由于会话2中的事务还没有释放，所以对emp_id=2004的行记录已经被会话2事务独占并且对该行加有TX锁，所以此时会话5也申请获取emp_id=2004记录的TX行锁必须等待，等会话2的事务结束，释放对emp_id=2004行的TX锁会话5才能加TX锁。