Re: Which (table) lock mode to use

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/