Thread: Which (table) lock mode to use

Hi

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. 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?

Thanks,
Ben

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/

> 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/
> >
>