oracle锁适应场景,模拟RI锁定导致阻塞的场景，并分析v$lock

Oracle LOCK内部机制及最佳实践系列

模拟RI锁定导致阻塞的场景，并分析v$lock

[日期：2012-12-03]

来源：Linux社区

作者：Leonarding

[字体：大 中 小]

模拟RI锁定导致阻塞的场景，并分析v$lock相应的锁定信息，给出SQL演示。

LEO1@LEO1> create table a (id int primary key);                    a是主表，定义了id字段为主键

Table created.

LEO1@LEO1> create table b (id references a(id));                    b是从表，id字段是引用主表的id字段

Table created.

LEO1@LEO1> insert into a values(1);                                      往主表a中插入一条数据但没有提交，事务没有结束会产生锁定

1 row created.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0    insert由于有从属关系因此会在2个表上都加3号共享锁

138 TM              73467          0          3          0          0

138 TX            196640      940          6          0          0

LEO1@LEO1> select object_name from dba_objects where object_id in (73465,73467);  ID1就是138会话操作的对象id，我们会在主表和从表上都加上表级锁

OBJECT_NAME

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

A          73465

B          73467

LEO1@LEO1> commit;                                                  提交之后释放锁

Commit complete.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;    锁会随着事务的结束而释放

no rows selected

LEO1@LEO1> select * from a;                                        a表中有一条记录

ID

----------

1

LEO1@LEO1> select * from b;

no rows selected

LEO1@LEO1> update a set id=100 where id=1;                          主表a上更新了一条记录

1 row updated.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0          update现在只对主表有锁定，从表没有锁定

138 TX            196634      941          6          0          0

LEO1@LEO1> commit;                                                  提交之后释放锁

Commit complete.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;      现在没有锁了

no rows selected

LEO1@LEO1> select * from a;          主表里的值已经更新了

ID

----------

100

LEO1@LEO1> delete from a;

1 row deleted.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0        delete也是只对主表有锁定，从表没有锁定

138 TX            655375      705          6          0          0

LEO1@LEO1> commit;                                                              提交释放锁

Commit complete.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

no rows selected

LEO1@LEO1> insert into b values(2);

insert into b values(2)

*

ERROR at line 1:

ORA-02291: integrity constraint (LEO1.SYS_C0010831) violated - parent key not found  直接给从表插入记录，如果主表没有的话，会报错违反引用完整性约束，没有主表依据

LEO1@LEO1> insert into a values(2);      我们只能先给主表插入

1 row created.

LEO1@LEO1> insert into b values(2);      再给从表插入才可以，因为从表的数据必须在主表里先存在，才能正常引用

1 row created.

LEO1@LEO1> select * from a;              主表有了

ID

----------

2

LEO1@LEO1> select * from b;              从表有了

ID

----------

2

LEO1@LEO1> select * from a;              主表有3

ID

----------

2

3

LEO1@LEO1> insert into b values(3);  才能给从表插入

1 row created.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0    从表的insert也会对主从表同时加锁

138 TM              73467          0          3          0          0

138 TX            393246      939          6          0          0

LEO1@LEO1> select * from b;

ID

----------

2

3

LEO1@LEO1> commit;                释放锁

Commit complete.

LEO1@LEO1> delete from b;      删除从表

2 rows deleted.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0    从表delete也会对主从表同时加锁

138 TM              73467          0          3          0          0

138 TX            196620      944          6          0          0

LEO1@LEO1> commit;                                  释放锁

Commit complete.

LEO1@LEO1> select * from a;                      主表有2条记录

ID

----------

2

3

LEO1@LEO1> select * from b;                      从表没有记录

no rows selected

LEO1@LEO1> insert into a values(4);            向主表插入1条记录，因为没有提交所以是未决状态

1 row created.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0      此时对主从表都加了锁定

138 TM              73467          0          3          0          0

138 TX            589834      937          6          0          0

LEO2@LEO1> insert into leo1.b values(4);          此时向从表也插入1条记录，由于从表的数据必须引用自主表，而主表数据现在是一种未决状态，所以hang住不能前进

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

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

138 TM              73465          0          3          0          0

138 TM              73467          0          3          0          0

138 TX            589834      937          6          0          1      138会话阻塞156会话，这个6代表已经插入成功，但修改值还是未决状态

156 TM              73467          0          3          0          0

156 TM              73465          0          3          0          0

156 TX            393221      942          6          0          0      这个6代表也已经插入成功，但修改值还是未决状态

156 TX            589834      937          0          4          0      主从表插入后产生了2个TX锁，这说明这是2条不同的记录，2个独立的记录，不是争用同一条记录

小结：之所以还有一个TX锁正在申请4号锁，是因为2条记录的修改值都是未决状态违反了引用完整性约束从而产生阻塞。导致156会话hang住不能前进。