Re: Proposal: String key space for advisory locks

On Tue, Oct 27, 2009 at 12:43 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Merlin,
>
>> Why even bother with a hash function when you can just have multiple
>> table pull from a shared sequence?  AFAICT, this solves the OP's
>> problem with no downsides (I used the approach with excellent results
>> in a ported cobol app which had pessimistic locking requirement).
>
> Well, if you have enough tables, the sequence itself becomes a
> bottleneck

I wonder if that's a legacy problem...I tested on our development
server w/pgbench -f and measured that nextval('s') scaled almost
linearly (I tested up to 900 clients) at about 70% of 'select 0'. (28k
tps on 4 core dell server vs 40k peak). pgbench does have it's own
scaling problems though. Since I happen to be working on a project
that relies heavily on high traffic sequences, do you have any
specific insights on known scaling problems with sequences?

> It also offends my sense of good database design, but that's another
> issue entirely.

I basically agree.

> More importantly, I think the issues raised here cause developers not to
> use advisory locks and instead use solutions more subject to race
> conditions, like a locking table.  Advisory locks could be a really cool
> feature for developers if it was just a bit more usable.

'as is', advisory locks is a fantastic feature that can be used for
signaling, mutexing, etc that are relatively difficult things to do in
the transactional world of sql. My main gripe is that the 'shared id'
method for doing record pessimistic locks is basically a nuclear
missile pointed at your shared buffers if you don't have lot of
discipline in the queries that lock IDs. Maybe this argues for more
of a 'sql exposed' pessimistic lock feature that operates on similar
level as 'for update'...I'm not sure...curious what thoughts you have
about improving them.

merlin