Re: Locking to restrict rowcounts.

Richard Huxton wrote:
> Shakil Shaikh wrote:
>> Hi,
>>
>> Consider the following scenario:
>>
>> CREATE FUNCTION test(name)
>> select into cnt count(id) from items where owner = name;
>> --suppose someone inserts during this point? then next check will
>> succeed when it should not.
>> if (cnt < 10) then
>> insert into items values ('new item', name);
>> end;
>> end;
>>
>> What is the best way to stop this function from entering too many
>> items in a concurrent context? I think a lock is needed, although I'm
>> not sure which would be most appropriate since the count requires the
>> whole table
>
> Well, you can't use FOR UPDATE with aggregates, otherwise you could do:
> SELECT into cnt count(id) FROM items WHERE owner = name FOR UPDATE;
> So, you'd need:
> PERFORM * FROM items WHERE owner = name FOR UPDATE;
>
> That won't stop someone blindly inserting into items, but it will act as
> an effective lock on calling test() concurrently.
>
> The other options would be:
> 1. Lock the relevant row in the users table (not so good if you have an
> items2 table with similar requirements)
> 2. Advisory locks at the application level
> 3. Check after an insert on the items table and raise an exception if
> there are 11+ items.
>
> I'd be tempted by #3 - assuming most of the time you won't breach this
> limit.

#3 won't work unless the other transactions have all committed by the
time you do the check. It is guaranteed to fail at some point.

In cases like this, in the unusual case that I don't already have some
suitable row locked, I just pick a row in some table that makes sense to
lock. There would be nothing wrong with creating a table with rows that
exist solely for the purpose of locking. This is a (usually) better
version of option #2 above.

-Glen