Can I lock rows in a cursor if the cursor only returns a single count(*) row?

Question

I would like to restrict users from inserting more than 3 records with color = 'Red' in my FOO table. My intentions are to A) retrieve the current count so that I can determine whether another record is allowed and B) prevent any other processes from inserting any Red records while this one is in process, hence the for update of.

I'd like to do something like:

cursor cur_cnt is
select count(*) cnt from foo
where foo.color = 'Red'
for update of foo.id;

Will this satisfy both my requirements or will it not lock only the rows in the count(*) who had foo.color = 'Red'?

Answer 1

This will only prevent users from updating the selected rows, not from adding new ones. The only way to reliably enforce such a rule is by a combination of a check constraint (on a "master" table) and a trigger on the "foo" table that updates the master table. Something like this (using EMP and DEPT for familiarity):

alter table dept add (manager_count integer default 0 not null,
   constraint manager_count_chk check (manager_count <= 3));

create trigger emp_trg
before insert or update or delete on emp
for each row
begin
    if inserting or updating then
        if :new.job = 'MANAGER' then
            update dept
            set    manager_count = manager_count+1
            where  deptno = :new.deptno;
        end if;
    end if;
    if updating or deleting then
        if :old.job = 'MANAGER' then
            update dept
            set    manager_count = manager_count-1
            where  deptno = :new.deptno;
        end if;
    end if;
end;

This achieves the desired locking by preventing more than one user from inserting, updating or deleting 'MANAGER' employees at a time.

Answer 2

Locking existing rows can't prevent other sessions from inserting new rows.

One possible approach is to have a COLORS tables that lists the possible colors. (Your FOO.COLOR could then have a foreign key reference to COLORS.COLOR.) Then lock the appropriate row in COLORS before doing your inserts and updates. This will serialize all accesses that deal with the same color.