Thanks for the suggestion Connor.To answer you questions

1) My block will update the inventory status for single record on demand based on the input of order_id and inventory id. Sometime the user will upload the orders in bulk from the front end application and it has to proceed in multiple rows.

2) Tried with SELCT FOR UPDATE NO WAIT and it throws me an error stating

Error:ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

It seems the for update wont work for view isnt it?


3) let me try with skip loaded

Hello, Anish : Connor is 101% right, is Natural and Expected that multiple database sessions acessing the same row WILL block - in Oracle RDBMS data consistency is PARAMOUNT, so no two sessions can change the same data at the same time , AND (obeying the RDBMS rules) each database session CAN and WILL 'see' only THEIR changes in the data....
IF what you want with threads is to 'break' a large work into multiple tasks/processes, PARALLEL SQL is the answer for it : with PARALLEL SQL the RDBMS will Automagically divide the work into multiple 'slave works'.... Ok ? And, if due to any reason you don´t have access to native Parallel SQL, use the poorman´s/DIY options, such as DBMS_PARALLEL....

It´s NOT RECCOMENDED, but Another option, if you REALLY WANT to 'break to work' in Java threads, Externally (OUT of the database) would be make the multiple java threads share one database session : read the Database JDBC Developer's Guide (right now online in https://docs.oracle.com/database/121/JJDBC/apxtips.htm#JJDBC28948 ) and see that you CAN have multiple threads sharing one database session...
OF COURSE, the manual ALSO warns you againt doing it (I repeat, PARALLEL SQL inside the database is the prefered way) but afaik it is possible...

Finally, if you want/need to have multiple threads in Java (urgh!), AND due to any issue you MUST/WANT create a dedicated database session to each thread, is up to you CONTROL the multiple sessions to avoid two sessions acessing/changing teh same data at the same time...

And yes : no SELECT FOR UPDATE inside views, think in other options if you really want/need to use views...

Regards,

J. Laurindo Chiappa

Thanks again, Connor for the suggestion. But a quick question.

How to code a simple update statement in pl SQL block without commit statements and to get accessed from different user without locks/wait time?


In my case, the pl/SQL block will update the status id and I will be returning the id to the application, which is again used for further processing.

Sometimes, the end user may update in bulk, but mostly there is a single user from the portal using my code at a time.So the option of a bulk update, FORALL is out of scope!


1) My PLSQL block is open to all our clients and there is a high possibility of updating 10k users in 1 hour.From my java application, my PLSQL block is getting accessed using 5 different threads and each will be returning the corresponding updated id (unique) respectively.

2) I have tried with no wait option and im getting the error
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.This is happening when I execute the orders in bulk, for a single execution at a time, it is not throwing an error.

3) If I try with skip locked, I can see only one record getting executed in DB, all were getting errored out.

SQL> create table t as select * from dba_Objects;

Table created.

SQL>
SQL> create index ix on t ( object_id );

Index created.

SQL>
SQL> create or replace
  2  procedure update_entry(r sys.odcinumberlist) is
  3    s timestamp := systimestamp;
  4  begin
  5    if r.count = 1 then
  6      update t set owner = lower(owner)
  7      where object_id = r(1);
  8    else
  9      forall i in 1 .. r.count
 10        update t set owner = lower(owner)
 11        where object_id = r(i);
 12    end if;
 13
 14    dbms_output.put_line(sql%rowcount||' records updated in '||(systimestamp-s));
 15  end;
 16  /

Procedure created.

SQL> set serverout on
SQL> exec update_entry(sys.odcinumberlist(123));
1 records updated in +000000000 00:00:00.004000000

PL/SQL procedure successfully completed.

SQL> declare
  2    lots_of_rows sys.odcinumberlist := sys.odcinumberlist();
  3  begin
  4    for i in 1 .. 1000 loop
  5      lots_of_rows.extend;
  6      lots_of_rows(i) := i;
  7    end loop;
  8    update_entry(lots_of_rows);
  9  end;
 10  /
997 records updated in +000000000 00:00:00.020000000

PL/SQL procedure successfully completed.

Wow, that's a very simple example, explained perfectly for a rookie like me. I understood well. Thanks, Connor.

But my objective is a little bit different. My proc is getting accessed by 100 different users at a time(we tested using 5 sessions at a time, in production, it can be more) and it is not getting executed 100 times in a single session using bulk upload.

As you may see my code, in the update statement, the inventory_table is getting updated based on a complex view. Again the view contains an inventory_table one among the other table. Is that the reason for the lock?