基于v$lock.lmode分析父子表外键列是否索引持锁模式区别之系列六

最新推荐文章于 2024-10-16 11:14:53 发布

clg10051

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量234

点赞数

文章标签：数据库后端

结论

1,oracle lock锁机制非常复杂，v$lock.lmode之间的区别以及适用场景还要研究

2,本文基于3个并发会话进行测试，第1个会话操作基于子表DML操作，第2个会话操作基于父表的DML操作，第3个会话基于子表的DML操作（包含INSERT，UPDATE，DELETE）

3,需要3个并发会话，基于子表外键列是否构建索引的行为区别
对于子表的DML操作影响有所不同
A,如构建索引，则第3个会话操作仅与操作父表DML对应记录的子表记录DML才会阻塞，其它子表的DML正常继续
B,如不构建索引，则第3个会话的任何DML操作皆会受到阻塞

4，再细化分解下，因为是父子表，通过外键把父子表记录关联起来，以保障数据一致性，所以不管你是先更新父表或子表记录不提交，而更新对应的子表或父表记录对应的事务则会等待

5，未构建子表外键索引时，V$LOCK会同时对父子表持行级排它锁，并且会请求共享行级排它锁ssx
相反如果构建子表外键索引时，v$lock也会同时对父子表持行级排它锁，不会请求任何锁

6, lmode值为0,即none锁，此模式锁将在另一文章进行测试与学习

7, 再直白一点讲，如子表外键列构建索引，到时对于子表的并发操作会更好，否则会更差，所以一定要为子表外键列构建索引

8，而且如不为子表外键列构建索引，删除父表DML,会导致子表全表扫描，否则仅会对子表进行索引扫描即可

9，还有一个问题，某个会话因为DML持的锁会不会因为并行会话的增加或变化，会导致某个会话DML持锁模式发生变化呢，这个还没有完全想明白，将在另一文章进行测试

10,v$lock.block的列含义及适用场景，同上，也要专门测试，加深理解

测试

1，创建父表
SQL> create table t_parent(a int,b int,c int);

Table created.

2，创建子表
SQL> create table t_child(a int,b int);

Table created.

3，创建主键约束
SQL> alter table t_parent add constraint pk_t_parent primary key(a);

Table altered.

4，创建子键外键约束
SQL> alter table t_child add constraint fk_t_child foreign key(a) references t_parent(a);

Table altered.

5,父子表记录
SQL> select * from t_parent order by 1;

A B C
---------- ---------- ----------
1 1 1
2 2 2
3 3 3

SQL> select * from t_child order by 1;

A B
---------- ----------
1 1
2 2

6，测试子表外键列是否构建索引，对于OLTP DML并行的影响
未建子表外键列索引

会话1

SQL> select sid from v$mystat where rownum=1;

SID
----------
234

删除子表1条记录
SQL> delete from t_child where a=1;

1 row deleted.

会话2

SQL> select sid from v$mystat where rownum=1;

SID
----------
55

插入主表1条记录
SQL> insert into t_parent values(4,4,4);

1 row created.

删除主表1条记录
SQL> delete from t_parent where a=4;

HANG住

监控会话
SQL> select sid,type,id1,id2,
2 decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,
ctime,block
decode(request,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') request,
4 ctime,block
5 from v$lock where type in ('TM','TX') and sid in (234,55);

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
234 TM 74751 0 row-x none 238 0
234 TM 74752 0 row-x none 240 1
55 TM 74751 0 row-x none 226 0
55 TM 74752 0 row-x ssx 226 1
234 TX 15007761 3602 x none 239 0
55 TX 13434883 6598 x none 227 0

6 rows selected.

SQL> select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where type in ('TM','TX');

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 transact
ions to wait for it

SQL> select object_id,object_name from dba_objects where object_id in (74751,74752);

OBJECT_ID OBJECT_NAME
---------- ------------------------------
74751 T_PARENT
74752 T_CHILD

会话3
SQL> insert into t_child values(1,1);

HANG住

SQL> insert into t_child values(3,3);

HANG住

SQL> delete from t_child where a=1;

HANG住

SQL> delete from t_child where a=2;

HANG住

SQL> update t_child set a=2 where a=1;

HANG住

SQL> update t_child set a=1 where a=2;

hang住

总结：
1，子表外键列未建索引时，如果1个会话删除子表1条记录时，且另一会话删除主表1条记录时，而随后的会话任何对于子表的DML皆会HANG掉
可见子表t_child不仅持行级排它锁，且会请求共享行级排它锁，而且t_child还是blocker就是它还是阻塞者，也就是说它占有了别人
需要的锁资源；这样主表操作1条记录时，对子表任何的DML全会造成阻塞

建子表外键列索引

SQL> create index idx_t_child on t_child(a);

Index created.

会话1
SQL> delete from t_child where a=1;

1 row deleted.

会话2
SQL> insert into t_parent values(4,4,4);

1 row created.

SQL> delete from t_parent where a=4;

1 row deleted.

未HANG住

监控会话
SQL> select sid,type,id1,id2,
2 decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,
3 decode(request,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') request,
4 ctime,block
5 from v$lock where type in ('TM','TX') and sid in (234,55);

SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
234 TM 74751 0 row-x none 233 0
234 TM 74752 0 row-x none 234 0
55 TM 74751 0 row-x none 224 0
55 TM 74752 0 row-x none 224 0
234 TX 13697029 5850 x none 234 0
55 TX 18939931 3110 x none 224 0

6 rows selected.

会话3
SQL> insert into t_child values(1,1);

1 row created.
未HANG住

SQL> insert into t_child values(3,3);

1 row created.

未hang住

SQL> delete from t_child where a=1;
HANG住

SQL> delete from t_child where a=2;

1 row deleted.
未HANG住

SQL> update t_child set a=2 where a=1;
hang掉

SQL> update t_child set a=1 where a=2;

1 row updated.
未hang住

总结：
1，子表外键列构建索引时，如果1个会话删除子表1条记录时，且另一会话删除主表1条记录时，而随后的会话任何对于子表的DML仅会HANG住与会话1更新相同行的DML操作
可见对于主表操作，只会对于主键列对应的子表记录对应的DML造成阴塞