Skip site navigation (1) Skip section navigation (2)

Re: Locking tables

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Allan Berger <alb2(at)cornell(dot)edu>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Locking tables
Date: 2003-07-21 17:52:39
Message-ID: 20030721103805.W4254-100000@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.


In response to

pgsql-novice by date

Next:From: Allan BergerDate: 2003-07-21 17:58:32
Subject: Re: Locking tables
Previous:From: Bruno Wolff IIIDate: 2003-07-21 17:49:12
Subject: Re: Locking tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group