Re: Locking tables

On Mon, Jul 21, 2003 at 12:00:45 -0500,
Allan Berger <alb2(at)cornell(dot)edu> wrote:
> Hi all,
>
> I have a genuine novice question. What's the best "postgres way" to
> lock tables in the following work flow circumstances:
>
> A)
> 1) Begin work;
> 2) select max(Id) from table;
> 3) insert into table record with Id=(max+1);
> 4) commit;
>
> I want to be absolutely certain no other user can run this identical
> query concurrently (read the same max(Id)) causing two identical
> records to be built with the same Id=(max+1) between steps 2 and 4.
> This would require locking the entire table with a "Lock table"
> statement between steps 1 and 2, yes? Best syntax?

If you just need uniqueness (e.g. there can be gaps in the ids), you
can use sequences to do this more efficiently.