具有初始延迟的主键约束即使阻止了插入。

huangapple go评论55阅读模式
英文:

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中,不能读取另一个会话未提交的更改)。因此,如果允许T2T1保持其事务的同时继续执行插入和提交,它将无法知道存在违规情况。只有当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.

huangapple
  • 本文由 发表于 2023年6月29日 22:08:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76581833.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定