Skip to Main Content
  • Questions
  • Is there a nowait clause for an insert statement

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, stephen.

Asked: March 13, 2019 - 1:26 pm UTC

Answered by: Chris Saxon - Last updated: October 15, 2020 - 11:21 am UTC

Category: SQL - Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

I have inserted the same primary key data but the Second Session is locking forever!
Is there an option to alter user session to raise timeout after 60seconds or NOWAIT for the second sessions INSERT command.

In our environment session 1 is a long running batch process taking hours and the Session 2 is single-row-insert processing a file, so I would like to log the problem row and complete the other lines in the file.

-- test case :
CREATE TABLE x_temp_log( pk NUMBER PRIMARY KEY , Action_type varchar2(1)  , created_date date) 
; 
-- Connect session1
EXEC dbms_application_info.set_module('session1','BATCH process takes hours'  ); 
INSERT INTO x_temp_log  VALUES( 12345 ,'I',SYSDATE ) 
; 
EXEC dbms_lock.sleep(3600);
--simulate a long running that finally COMMITs or rollback;

 
-- Connect session2 
EXEC dbms_application_info.set_module('session2 ', 'row-by-row process, I would like a NOWAIT feature'); 
select *FROM x_temp_log WHERE pk=12345
;
--no rows
INSERT INTO x_temp_log  VALUES( 12345 ,'I',SYSDATE )
; 
-- this seems to hang forever!


-- Weirdly over a database link it does timeouts at 5minutes:
INSERT INTO myschema.x_temp_log@dblink_12c VALUES( 12345 ,'I',SYSDATE )

and we said...

No, there isn't. You have to wait for the batch process to commit/rollback.

When it comes to operations over database links there is the DISTRIBUTED_LOCK_TIMEOUT parameter. This states how long a session will wait for locked remote resources. After this time you'll get an "ORA-02049: timeout: distributed transaction waiting for lock" error.

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

why ?

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?



Chris Saxon

Followup  

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

Reviewer: Evan

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.
Chris Saxon

Followup  

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.

To Evan

March 14, 2019 - 7:22 pm UTC

Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil

Hi : you are thinking in something like https://groups.google.com/forum/#!topic/comp.databases.oracle.server/efCqBFRl5xE , right ? yep, this could work....

Regards,

J. Chiappa

use of NOWAIT for UPDATE

October 15, 2020 - 11:10 am UTC

Reviewer: Narendra

Hello Chris,

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?
Chris Saxon

Followup  

October 15, 2020 - 11:21 am UTC

I see the use case, I'm just not aware of any plans to add (NO)WAIT to UPDATE/INSERT.

If this is something you want to see, you could post it as a suggestion in the Database Ideas forum:

https://community.oracle.com/tech/apps-infra/categories/database-ideas-ideas

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.