Because I’m off now for the next 4 days (I’m back on Monday) I decided to leave you a little Oracle quiz. You’ll get the answer then next week. So here we go:
You’ve two sessions: SessionA and SessionB. Both of them perform a insert on the same table (insert into data…) without a commit.
The question now is: Can one insert block the other one?
Just post your thoughts as comment!
Cheers!
Yes it can if there is RI violation.
LikeLike
A lack of available ITL slots could cause a wait/block such that sessionB is waiting for sessionA (or some other session) to commit and release its ITL slot.
LikeLike
if both inserts are triggering an update statement which affects the same row
LikeLike
Even easier. Two INSERTs trying the same primary key or unique key (constraint must be enabled and not deferred). The second waits until the first is committed before reporting the key violation (or it succeeds if the first is rolled back instead).
LikeLike
To answer the question correctly I think we need more information.
Assuming there are no constraints in place, my answer would be:
“No way, both inserts will be successful.”
If there are constraints like PK or UK then check out Gary’s reply (right above mine).
LikeLike