Concurrency and locks
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
"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
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