Re: Which (table) lock mode to use - Mailing list pgsql-general

> What's the purpose of the updates?  If we knew more about what
> you're trying to do then maybe we could suggest alternate solutions.

There are two fields (of all the records) I need to update when I
insert/update/delete a record. These two fields contain numbers and
they act as a link between the records within the table. If I don't
update these numbers the link will be broken.

Thanks,
Ben


On Wed, 15 Dec 2004 16:47:53 +1100, Ben <newreaders@gmail.com> wrote:
> > What's the purpose of the updates?  If we knew more about what
> > you're trying to do then maybe we could suggest alternate solutions.
>
> There are two fields (of all the records) I need to update when I
> insert/update/delete a record. These two fields contain numbers and
> they act as a link between the records within the table. If I don't
> update these numbers the link will be broken.
>
> > The "Explicit Locking" section of the "Concurrency Control" chapter
> > in the documentation describes the available lock modes and their
> > conflicts.  SELECT acquires ACCESS SHARE; UPDATE, DELETE, and INSERT
> > acquire ROW EXCLUSIVE, so it sounds like you want a lock that
> > conflicts with ROW EXCLUSIVE but not with ACCESS SHARE.  That gives
> > the following possibilities:
> >
> > SHARE
> > SHARE ROW EXCLUSIVE
> > EXCLUSIVE
>
> Thank you, I will have another look at the documentation.
>
> Cheers,
> Ben
>
> On Tue, 14 Dec 2004 20:36:08 -0700, Michael Fuhr <mike@fuhr.org> wrote:
> > On Wed, Dec 15, 2004 at 11:55:34AM +1100, Ben wrote:
> >
> > > I have this scenario and would like to use lock table for this but I
> > > don't know which mode I should use.
> > >
> > > When I delete/update/insert a record from/to a table, I need to update
> > > some fields of all the records in this table.
> >
> > What's the purpose of the updates?  If we knew more about what
> > you're trying to do then maybe we could suggest alternate solutions.
> >
> > > During this process I don't want anyone to insert, update or delete
> > > the data but allow them to select the data at the original state.
> > >
> > > I am currently using:
> > >
> > > LOCK TABLE tbl_name IN SHARE ROW EXCLUSIVE MODE
> > >
> > > Is this the correct mode to use?
> >
> > The "Explicit Locking" section of the "Concurrency Control" chapter
> > in the documentation describes the available lock modes and their
> > conflicts.  SELECT acquires ACCESS SHARE; UPDATE, DELETE, and INSERT
> > acquire ROW EXCLUSIVE, so it sounds like you want a lock that
> > conflicts with ROW EXCLUSIVE but not with ACCESS SHARE.  That gives
> > the following possibilities:
> >
> > SHARE
> > SHARE ROW EXCLUSIVE
> > EXCLUSIVE
> >
> > See the documentation for these modes' conflicts and decide which
> > is most appropriate.  But again, what problem are you trying to
> > solve?  Maybe there's a better way than locking an entire table.
> >
> > --
> > Michael Fuhr
> > http://www.fuhr.org/~mfuhr/
> >
>