1

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.)

Blocked
  • 340
  • 2
  • 5
  • 20

1 Answers1

2

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.

Paul W
  • 5,507
  • 2
  • 2
  • 13
  • I think I lack some knowledge of Oracle database. This is my current thinking: T1 insert; T2 insert (no blocking); T1 commit (now T2 can see T1 changes); T2 try to commit => err PK Violation. – Blocked Jun 30 '23 at 14:17
  • Or: T1 insert; T2 insert (no blocking); T2 commit (now T1 can see T2 changes); T1 try to commit => err PK Violation. Why must the insert be blocked? – Blocked Jun 30 '23 at 14:19
  • Unfortunately it doesn't work that way. In a race condition in Oracle it isn't the first to commit, but the earliest SCN, and the SCN is generated at transaction *start*, not end. So Oracle must give T1 the view of the table as it existed at it's start transaction (SCN) time, which is before T2 did anything. If one of these were selects, it could simply do consistent read (CR) by applying rollback without any blocking, but as they are both DMLs it must use a current (CUR) block which can only be consistent by serializing transactions attempting to obtain the same lock bit. – Paul W Jun 30 '23 at 15:43
  • So, T1 could not change its results because of anything T2 does, or that violates the SCN priority of Oracle. T1 must succeed, which means T2 cannot be allowed to succeed while T1 is not committed. – Paul W Jun 30 '23 at 15:48