Hi,
I have a table and different instances are running to access the records of that table.I have to update the records of this table. Can it be possible that whenever i lock some rows say 10 row for update then other instance can not get that rows by firing select query?
My query is like:

select col1,col2 from table1 where cond1..and rownum<10 for update of col1 no wait;

Suppose I have got ten ros and locked these rows. Now I want that if any other instance fires the same query then it should not get these row to see as well as to update.

>>I have a table and different instances are running to access the records of that table.

Here you are using the word instance. Is it an instance or a session?


Users from other databases(i.e. from different instancnes) can access your table data only through database links.

Users from other sessions can access your table data if they have privileges to do so.

>>select col1,col2 from table1 where cond1..and rownum<10 for update of col1 no wait;


Here you are using for update. Others can't make modifications upon this data until you issue rollback or commit.


>>Now I want that if any other instance fires the same query then it should not get these row to see

Then why are you giving privileges on your table to other users.

Hi,
I am explaining you the whole problem.
I have a table and we have to fetch records from this table, update some columns of that record(to make unavailable to any other thread for the same query), do some proccessing on java side, if proceess is successful then give confirmation call to delete that rows from the table if not successful or till 5 minutes it does not get the confirmation call, then again we will have to update and make it null the previously updated columns to make available again for that query.
This query will be fired by java developers through multiple threads or instances whatever.
suppose my query is :

select * from table abc where col1 is null and col2=2 and rownum<10 for update of col1 nowait;



after this we update these records by givivng some value to col1 just to make unavailable for any other thread or instance.

update abc set col1=1234 where col2=2;

But in between these two statement if any thread fires the previous select query then it gets the same records again as they still contain null in their col1. Now thread one updates col1 and give some value and during its proccessing and before giving confirmation call thread2 will execute the update command to give some value to col1 then on confirmation call of first thread will be fail due to differrent value in col1 as it gives confirmation call on the basis of value of col1 like:

delete from abc where col1=1234;

It will not get that 9 rows because thread2 will give some other number to col1;

Thats why I wanted to lock these rows for select also so that not any thread can get these 9 rows fetched by thread one.

java developers are firing these queries through their multiple threads which are running parallelly.

you need to commit immediately after executing your DML statements.