Обсуждение: 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