1

I have a control table and want to be able to select for update a single row in this control table.

However, using an ORDER BY and WHERE rownum = 1 along with a FOR UPDATE results in ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

This is possible in Postgresql. Is there a way to accomplish this in Oracle?

Set up

drop table foo;

create table foo (
    bar int, 
    last_update_date date
);

insert into foo (
    bar, 
    last_update_date
) select level, 
    null
from dual 
connect by level <= 10;

commit;

Issue

The following FOR UPDATE with the ORDER BY fails with ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.:

select *
from (
    select *
    from foo
    where last_update_date is null
    order by bar
)
where rownum = 1
for update skip locked;

Removing the ORDER BY works, but is not the behavior that I want.

Workaround

The workaround that I have is to select all rows into memory:

select *
from foo
where last_update_date is null
order by bar
for update;

Followed by an update to the first row in the result set and a commit.

This is not that great. Not only does it not scale but it also blocks concurrent threads for much longer than it would if I could just select for update a single row.

1 Answer 1

1
select *
from foo
where last_update_date is null
order by bar
for update;

Will only lock the rows that get fetched. Have your calling code just fetch a single result and you will only be locking a single row at a time. If you are using PL/SQL for your processing, this means just using the

Open cursor
Fetch cursor into resultVariables
Do processing
Commit
Close cursor

You could be better off looking into Advanced Queueing (dbms_aq) which handles a lot of this for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.