insert操作引起的row lock测验（Row Locking on Insert）

记录insert 引发的row lock等待的测验

一、测验

1.创建主从表

create table t1 (
  id1 int primary key
);
create table t2 (
  id1 int references t1 (id1)
);

insert into t1 values (1);
insert into t2 values (1);
commit

2.从session 1删除子表
session 1:

SQL> delete t2;

3.session 2 插入1笔至父表
session 2:

SQL> insert into t1 values (2);

4.可以看出没有阻塞

select l1.sid, ' IS BLOCKING ', l2.sid
from   v$lock l1, v$lock l2
where  l1.block =1 and l2.request > 0
and    l1.id1=l2.id1
and    l1.id2=l2.id2
no rows selected

5.返回session 1,同样插入1笔至主表t1

SQL> rollback;
Rollback complete.
SQL> insert into t1 values (2);

6.此时会话 1 卡住，等待会话 2 提交/回滚：

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from   v$lock l1, v$lock l2
  3  where  l1.block =1 and l2.request > 0
  4  and    l1.id1=l2.id1
  5  and    l1.id2=l2.id2;
         SID 'ISBLOCKING'         SID
---------- ------------- ----------
       615  IS BLOCKING         593

二、结论

当不同的session在拥有主键约束的表上insert相同的键值，也会引发row lock竞争