主外键的锁等待问题

[minitoy]

create table t_base--创建主表
(id number primary key,
 name varchar2(10));

create table t_ref--创建子表
(did  number primary key,
 dname varchar2(10),
 pid  number);
 
alter table T_REF--为子表添加外键
  add constraint FK_T_REF foreign key (PID)
  references T_BASE (ID);
 --窗口1
insert into t_base--执行后不commit,去窗口2执行insert
values(1,'aa');

--窗口2
insert into t_ref--锁等待
values(1,'ba',1);

问题来了,既然oracle是 read committed,窗口2的执行语句看不到窗口一得执行语句,
为什么不返回ORA-02291: 违反完整约束条件 (SCOTT.FK_T_REF) - 未找到父项关键字,
而是被阻塞,这跟 read committed 不矛盾么?

[gelyon]

这个应该是在缓冲中，检查约束的时候窗口1 的那条夫记录未提交，但是在缓冲中，因此窗口2在insert的时候就找到了父项
我是这么理解的

[minitoy]

从产生锁等待来看,窗口2的语句执行时是知道窗口1的数据的,但是这时窗口1的数据还没提交,那不成了read uncommitted了?

[真的不知道取什么名]

--窗口1进行的操作
scott@YPCOST> create table t_base--创建主表
  2  (id number primary key,
  3   name varchar2(10));

表已创建。

scott@YPCOST> create table t_ref--创建子表
  2  (did  number primary key,
  3   dname varchar2(10),
  4   pid  number);

表已创建。

scott@YPCOST>  
scott@YPCOST> alter table T_REF--为子表添加外键
  2    add constraint FK_T_REF foreign key (PID)
  3    references T_BASE (ID);

表已更改。

scott@YPCOST> select * from t_base for update;--行级所 这样其他用户可以进行查询操作

未选定行

--窗口2
scott@YPCOST> insert into t_ref--锁等待
  2  values(1,'ba',1);
insert into t_ref--锁等待
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_T_REF) - 未找到父项关键字

/*
个人观点：想t_base里insert 是属于排他锁，这个未提交。
你向t_ref里insert时无法去读取t_base里的数据 所以就一直等到
*/

[真的不知道取什么名]

--当我在窗口1中用排它锁
scott@YPCOST> lock table t_base in exclusive mode;

表已锁定。

--窗口2就会出现等待，而不是报ORA-02291 错误

[minitoy]

  ADDR	    KADDR	    SID	TYPE	ID1   	ID2	  LMODE	REQUEST	CTIME	BLOCK
	5AF4172C	5AF41744	132	TM	  70088	  0	    3	    0	      59	  0
	5AF417F0	5AF41808	132	TM	  70091	  0	    2	    0	      59	  0
	5AFAE748	5AFAE864	132	TX	  524289	7056	6	    0	      59	  1
	5AFA6F84	5AFA70A0	140	TX	  196608	7050	6	    0	      12	  0
	5B434398	5B4343AC	140	TX	  524289	7056	0	    4	      12	  0
	5AF41668	5AF41680	140	TM	  70091	  0	    3	    0	      12	  0
	5AF415A4	5AF415BC	140	TM	  70088	  0	    2	    0	      12	  0

上面是v$lock的内容,132对应窗口1的sid,140对应窗口2的sid.
看不懂,zhuomingwang和paddy给我讲讲吧

[真的不知道取什么名]

[Quote=引用 5 楼 minitoy 的回复:]
SQL code
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
5AF4172C 5AF41744 132 TM 70088 0 3 0 59 0
……
[/Quote]
我也看不懂，不过我觉得 应该是我想的那个思路
可能我的说法不正确，

[minitoy]

恩,我也觉得你说的有道理.如果能结合这个表说明下就好了.一直不会看这张表[Quote=引用 6 楼 zhuomingwang 的回复:]
引用 5 楼 minitoy 的回复:
SQL code
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
5AF4172C 5AF41744 132 TM 70088 0 3 0 59 0
……

我也看不懂，不过我觉得 应该是我想的那个思路
可能我的说法不正确，
[/Quote]

[真的不知道取什么名]

[Quote=引用 7 楼 minitoy 的回复:]
恩,我也觉得你说的有道理.如果能结合这个表说明下就好了.一直不会看这张表

引用 6 楼 zhuomingwang 的回复:
引用 5 楼 minitoy 的回复:
SQL code
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
5AF4172C 5AF41744 132 TM 70088 0 3 0 59 0
……

……
[/Quote]
能力有限啊，爱莫能助

[lxyzxq2008]

create table t_base--创建主表
(id number primary key,
 name varchar2(10));

create table t_ref--创建子表
(did  number primary key,
 dname varchar2(10),
 pid  number);
 
alter table T_REF--为子表添加外键
  add constraint FK_T_REF foreign key (PID)
  references T_BASE (ID);
 --窗口1
insert into t_base--作成成功，未commit
values(1,'aa');

--窗口2
insert into t_base--作成成功，未commit，未等待
values(2,'bb');

--窗口1
insert into t_ref--作成成功，未commit，未等待
values(1,'ba',1);

--窗口2
insert into t_ref--作成成功，未commit，未等待
values(2,'ba',2);

--窗口1
insert into t_base--锁等待
values(2,'bb');

----------------------------
--自己理解：提交读（read committed）事务隔离级别：虽然不会出现脏读，但是会出现幻象和非重复读。
--事务1先于事务2开始，并保持未提交状态，事务2想要修正正被事务1修改的行，那么事务2等待！
--只有事务1提交或回滚，事务2才能进行修改；
--如果事务2修正非事务1修正行，则可以正常修正！

[us_yunleiwangdb]

我期待大家继续探讨，也想知道ORACLE 的存储模式

[UPC子夜]

楼主可以这么想
如果窗口1把表锁了 那么就说明窗口1正在更新数据
窗口2就不确定数据中是否会已经存在外键约束的数据
那么 按照常理 就不应该报违反唯一约束的错误 而应该等待窗口1提交完成再做决定
我认为oracle这么做才是明智的 而不是急着返回错误

[minitoy]

顶起来

[cyousor]

[Quote=引用 11 楼 upc05070000 的回复:]

楼主可以这么想
如果窗口1把表锁了 那么就说明窗口1正在更新数据
窗口2就不确定数据中是否会已经存在外键约束的数据
那么 按照常理 就不应该报违反唯一约束的错误 而应该等待窗口1提交完成再做决定
我认为oracle这么做才是明智的 而不是急着返回错误
[/Quote]

+1

窗口1 执行后，执行
Select T.Session_Id, T.Object_Id, S.Object_Name, T.Locked_Mode
From V$locked_Object t, All_Objects s
Where T.Object_Id = S.Object_Id
And T.Session_Id = '146';--我的sessionid
可以看到这两个表上都有锁，
SESSION_ID OBJECT_ID OBJECT_NAME LOCKED_MODE
---------- ---------- ------------------------------ -----------
146 58546 T_BASE 3
146 58548 T_REF 2
在T_REF上的锁为Row-S 行共享(RS)，只能查询这些数据行，不能进行update、delete或select for update。
所以此时再执行窗口2，窗口2中的语句就会等待这个锁释放，才能继续执行插入操作。