英文:
Blocked insert even with Pk constraint with Initially Deferred
问题
我已创建一个测试表:TABLE1(ID -> 主键, INFO -> varchar2(100))。
我打开了SQL开发者,并将主键的延迟状态从“Not Deferrable”更改为“Initially Deferred”。这个主键的索引自动从“Unique”更改为“Non-Unique”。
我测试了一个重复的插入(相同的ID)使用2个事务,T1和T2。
T1:插入TABLE1中的ID=1(未提交)
T2:插入TABLE1中的ID=1(未提交) => 事务T2被阻塞。
为什么T2被阻塞?我的主键是“Initially Deferred”。
(如果我在T1中执行提交,那么T2不再被阻塞并且插入被执行。)
英文:
I have created a test table : TABLE1(ID -> pk, INFO -> varchar2(100)).
I opened the SQL Developer and i changed Pk Deferrable State from "Not Deferrable" to "Initially Deferred". The index of this PK automatically changed from "Unique" to "Non-Unique".
I tested a duplicate insert(same ID) with 2 transactions, T1 and T2 .
T1: insert into TABLE1 with ID=1 (no commit)
T2: insert into TABLE1 with ID=1 (no commit) => transaction T2 is blocked.
Why T2 is blocked? my PK is"Initially Deferred".
(If i execute a commit in T1, then T2 is no longer blocked and the insert is executed.)
答案1
得分: 2
在延迟约束中,验证发生在提交时。当T1
的事务处于打开状态时,T2
无法看到T1
已经执行的操作(在Oracle中,不能读取另一个会话未提交的更改)。因此,如果允许T2
在T1
保持其事务的同时继续执行插入和提交,它将无法知道存在违规情况。只有当T2
提交时,T1
才能看到该行,因此准备在自己的提交时验证唯一性。因此,必须阻止它才能正确执行其工作。延迟约束并不意味着非阻塞。它意味着非错误直到提交,这就是你所得到的。在提交之前它不会报错(T2
应该在提交时报错)。
延迟约束的主要好处是当它们在会话内使用时。如果我需要插入一个新的替代行,更新子外键,然后删除旧的父行,只要这一切都在一个事务中完成,就可以执行此操作。在这种情况下,不会有阻塞,因为所有的工作都由我的会话完成,所以一切都可以看到。
英文:
In a deferred constraint, validation happens at commit. While T1
's transaction was open, T2
cannot see what T1
has done (you cannot read another session's uncommitted changes in Oracle). Therefore if T2
were allowed to proceed with the insert and commit while T1
was holding its transaction, it would have no way of knowing there was a violation. It's only when T2
commits that T1
can see that row and therefore is prepared to validate uniqueness at its own commit time. So it has to be blocked in order to do it's job correctly. A deferred constraint doesn't mean non-blocking. It means non-erroring-until-commit, which is what you got. It didn't error before you committed (T2
should have errored at commit time).
The main benefit to deferred constraints is when they are used intra-session. If I need to insert a new replacement row, update a child FK, then delete the old parent row, I can do this as long as it's all in one transaction. There would be no blocking in this situation because all the work is done by my session, so everything can be seen.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论