Which (table) lock mode to use

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/