Re: Locking tables

On Mon, 21 Jul 2003, Allan Berger 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?

Yes, probably something like: lock table <tablename> in exclusive mode;

I think that'll allow selects (but not select for update) while blocking
update, delete, insert and select for update. If you don't care about
blocking select for update, I think you could probably lower the lock
mode (see \h LOCK in psql for the mode names and try them out in two
psql sessions ;) ).

although are you sure that you don't really want to be using
a sequence instead to get around the locking?

> B)
> 1) Begin work;
> 2) Select User from table where Id=n;
> 3) If User is null then:
> Update row Id=n to User="me"
> 4) commit;
>
> I want to be absolutely certain no other user can update the tuple to
> User="not me" between steps 2 and 3. This would require me to add a
> "Lock" statement that would prevent reads on this tuple between steps
> 1 and 2, yes (or a "Select with lock" statment)? Again, a suggestion
> for the explicit lock type would be awesome.

If everyone else is going to be doing the same sequence, then I think
making step 2 be

Select user from table where Id=n for update;

would work. That will give you a lock on just the row where Id=n allowing
operations on the rest of the table and if another user goes to do a
select for update, delete or update they'll block waiting for your
transaction to end.