If I understood well, you'll have to use the SELECT FOR UPDATE statement.
When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is a useful feature because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records which have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data).
There are times, however, when you will want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking.
When you issue a SELECT...FOR UPDATE statement, the RDBMS automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records “for your changes only” as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.
An example would be this:
select empno, ename, sal
where deptno = 10
for update of sal;
The OF list of the FOR UPDATE clause does not restrict you to changing only those columns listed. Locks are still placed on all rows; the OF list just gives you a way to document more clearly what you intend to change. If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, then the database will then lock all identified rows across all tables listed in the FROM clause.
but you dont catch me.
for example detailed:
in table tab_name, there are a lot of records. and many people(not one) process these records one by one.
person Tom get a connection to db, and person Mike also get one.
Tom issue a sql: select * from tab_name where rownum=1 for update; he just need one record to process for this time.
then, Mike alse issue the same sql: select * from tab_name where rownum=1 for update; he also want get a record from table to process.
problem rise: because Tom process his job with a long time, and he locked a record, then Mike(issue sql a little later) will not get select return until Tom finish his work.
so i think if there is a sql like this:
select * from tab_name where rownum=1 and (record not locked by other peron);
thus Mike can get another record(it isnt processed by other person on the same time);