1.建表并验证对和表DML操作时的锁定状态
a是主表，定义了id字段为主键
b是从表，id字段是引用主表的id字段
结论：
BYS@dg2>select distinct sid from v$mystat;
SID
----------
17
BYS@dg2>create table a(id int primary key);
Table created.
BYS@dg2>create table b(id references a(id));
Table created.
BYS@dg2>insert into a values(1);
1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 insert由于有从属关系因此会在2个表上都加3号共享锁
17 TM 75056 0 3 0 0
17 TX 458758 888 6 0 0
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message to client
49 SQL*Net message from client
BYS@dg2>col object_name for a20
BYS@dg2>select object_name from dba_objects where object_id in(75054,75056);
OBJECT_NAME
--------------------
A
B
BYS@dg2>commit; 锁会随着事务的结束而释放
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
BYS@dg2>select * from a;
ID
----------
1
BYS@dg2>select * from b;
no rows selected
BYS@dg2>update a set id=111 where id=1;
1 row updated.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 update现在只对主表有锁定，从表没有锁定
17 TX 393219 1031 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
BYS@dg2>select * from a;
ID
----------
111
BYS@dg2>delete a;
1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 delete也是只对主表有锁定，从表没有锁定
17 TX 524310 1244 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
#########################################################################
BYS@dg2>insert into b values(2); --直接给从表插入记录，如果主表没有的话，会报错违反引用完整性约束
insert into b values(2)
*
ERROR at line 1:
ORA-02291: integrity constraint (BYS.SYS_C0011302) violated - parent key not found
BYS@dg2>insert into a values(2);
1 row created.
BYS@dg2>commit;
Commit complete.
BYS@dg2>select * from a;
ID
----------
2
BYS@dg2>select * from b;
no rows selected
BYS@dg2>insert into b values(2);
1 row created.
BYS@dg2>select * from b;
ID
----------
2
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0
17 TM 75056 0 3 0 0 从表的insert也会对主从表同时加锁
17 TX 196634 1043 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>delete b;
1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0
17 TM 75056 0 3 0 0 从表delete也会对主从表同时加锁
17 TX 393245 1031 6 0 0
#############################################
BYS@dg2>select distinct sid from v$mystat;
SID
----------
46
BYS@dg2>select * from a;
ID
----------
2
BYS@dg2>select * from b;
no rows selected
BYS@dg2>insert into a values(33);
1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
46 TM 75054 0 3 0 0 insert由于有从属关系因此会在2个表上都加3号共享锁
46 TM 75056 0 3 0 0
46 TX 458758 888 6 0 0
此时在会话2上也执行向从表插入的动作会hang住
BYS@dg2>insert into b values(33);
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 589856 1025 0 4 0 主从表插入后产生了2个TX锁，这说明这是2条不同的独立的记录，不是争用同一条记录
49 TM 75054 0 3 0 0
49 TM 75056 0 3 0 0
46 TM 75054 0 3 0 0
46 TM 75056 0 3 0 0
49 TX 524298 1245 6 0 0 插入成功，修改值未决
46 TX 589856 1025 6 0 1 46阻塞49，6代表插入成功，修改值未提交是未决状态
有一个TX锁正在申请4号锁，是因为2条记录的修改值都是未决状态违反了引用完整性约束从而产生阻塞。导致156会话hang住不能前进。