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'?