Re: Which (table) lock mode to use

> 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(at)gmail(dot)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(at)fuhr(dot)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/
> >
>