March 14, 2019 - 10:24 am UTC
Reviewer: stephen moore
I assume this type of feature will never be released in future?
So, is this an oracle technical reason for not allowing a NOWAIT on insert (but allowing it for an Update) Or this an ISO SQL standard requirement?
March 14, 2019 - 11:31 am UTC
I'm not aware of any plans to implement this in Oracle Database.
Possible workaournd using dbms_lock
March 14, 2019 - 6:54 pm UTC
You might be able to do a workaround using the dbms_lock package to serialize access on the insert. You would need to create a procedure and in the procedure acquire an application lock and then run the insert statement. When session 2 tries to acquire the application lock it will wait and I think there's an option on the lock acquisition to specify a timeout. I seem to recall doing something like this once for serializing access to write to a file.
March 15, 2019 - 8:46 am UTC
You could. Though it's likely to add a lot more blocking operations to the app than the PK collision problem at the moment.
I'm more inclined to look into why
two processes are trying to insert the same PK value. And address that.
March 14, 2019 - 7:22 pm UTC
Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil
use of NOWAIT for UPDATE
October 15, 2020 - 11:10 am UTC
While I understand your response to OP about not having WAIT option for INSERT, I believe there is a use for WAIT/NOWAIT with UPDATE. If 2 or more users are trying to update same row(s) concurrently, only one of them will be able to acquire lock and do the UPDATE. Others will have to wait until first user COMMITs.
If there was an option to WAIT/NOWAIT with UPDATE, user/client will have more control over what it wants to do if rows are locked.
Like other poster said, one can use DBMS_LOCK (which appears to be a lot of code) or one can use SELECT...FOR UPDATE NOWAIT, followed by UPDATE to solve this issue, which again involves additional SELECT.
Am I missing something obvious?