Re: Locking to restrict rowcounts.

Glen Parker wrote:
> Richard Huxton wrote:

>> 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.

If it's in an AFTER INSERT/UPDATE trigger then whatever transaction
takes you beyond 10 rows you will always get a failure. If you get a lot
of insert/delete pairings then you could spend a lot of time counting
rows, but that's all.

 > 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.

Of course, if you're going to have a separate table then you might as
well store the count in there and actually update it on every
insert/update/delete. Assuming you might find the count of some use
somewhere. Set the fill-factor for the lock table and HOT should prevent
the table bloating too.

--
   Richard Huxton
   Archonet Ltd