Postgres Pro
Downloads
Home   >   mailing lists

Thread: Concurrency and locks

Concurrency and locks

From
"Mike Mascari"
Date:
Is it possible, or could it be made possible in the future, for
a ROW locking mechanism whereby:

Transaction #1

BEGIN;
SELECT key FROM foo WHERE key = 1 FOR ????;

 returns immediately with '1'

Transaction #2

BEGIN;
SELECT key FROM foo WHERE key = 1 FOR ????;

 returns immediately with '1' <-- Not blocked by #1

Transaction #3

BEGIN;
UPDATE foo SET key = 2 WHERE key = 1; <-- Blocks

If one wants to guarantee consistency in user-defined BEFORE
INSERT/UPDATE triggers and trigger procedures, at the moment, is
the only recourse SELECT FOR UPDATE? Is this the cause of
performance problems with the current RI implementation?

Mike Mascari
mascarm@mascari.com

Re: Concurrency and locks

From
Tom Lane
Date:
"Mike Mascari" <mascarm@mascari.com> writes:
> If one wants to guarantee consistency in user-defined BEFORE
> INSERT/UPDATE triggers and trigger procedures, at the moment, is
> the only recourse SELECT FOR UPDATE? Is this the cause of
> performance problems with the current RI implementation?

Yup, and yup (or at least one cause).  But it's not easy to see how
to build a multiple-locker mechanism that scales to handle very large
numbers of locked tuples.  You can't really expect to keep the state
data in shared memory --- but if there's >1 locker then there's no
room for it in the on-disk tuple header, either.

            regards, tom lane

Re: Concurrency and locks

From
Richard Huxton
Date:
On Wednesday 19 Feb 2003 5:00 pm, Tom Lane wrote:
> "Mike Mascari" <mascarm@mascari.com> writes:
> > If one wants to guarantee consistency in user-defined BEFORE
> > INSERT/UPDATE triggers and trigger procedures, at the moment, is
> > the only recourse SELECT FOR UPDATE? Is this the cause of
> > performance problems with the current RI implementation?
>
> Yup, and yup (or at least one cause).  But it's not easy to see how
> to build a multiple-locker mechanism that scales to handle very large
> numbers of locked tuples.  You can't really expect to keep the state
> data in shared memory --- but if there's >1 locker then there's no
> room for it in the on-disk tuple header, either.

Does the shared info need to be _which_ backends have locks, or could you just
get by keeping track of _how many_ backends have a lock on a particular tuple
- that'd be no bigger than an int.

Of course, if a backend crashes it'd cause problems.

--
  Richard Huxton