Thread: Locking tables
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?
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.
I'm especially getting confused by "lock table in row exclusive mode"
without including in this statement which rows to lock...the manual
pages don't offer clear enough examples for this particual newbie.
Thanks!
AB
--
Allan Berger
Bright Eyes & Bushy Tails Veterinary Service
3005 Highway 1 NE
Iowa City, IA 52240
(319) 351-4256 (voice)
(319) 341-8445 (fax) http://www.BEBT.com
On Mon, Jul 21, 2003 at 12:00:45 -0500, Allan Berger <alb2@cornell.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.
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.
At 12:49 PM -0500 7/21/03, Bruno Wolff III wrote:
>If you just need uniqueness (e.g. there can be gaps in the ids), you
>can use sequences to do this more efficiently.
Thanks for the [three] speedy replies!
I know my example was simple--I just wanted to illustrate.
I want to put as much of the code as possible in the database front
end for control and maintenance purposes. I have explicitly
considered the performance degradation and am willing to accept it.
Although having written this, maybe I should just lock everything in
"access exclusive mode" for the brief periods of time these
transactions will be pending, and not worry about identifying the
_least_ restrictive that will block intercurrent Selects (until I
actually do create a performance problem).
[After writing the paragraph immediately above I received Stephan
Szabo's response, which perhaps endorses draconian locks given my
perception of my need.]
AB
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?
The usual way to do this kind of stuff is using sequences.
You don't need to lock anything that way - just:
create sequence myseq;
... and then:
select nextval ('myseq');
and use the output with your insert statement - it is guaranteed, that
no other connection will get the same number from the nextval().
Or, if you do not care about what value you insert, and just need it to
be unique, then you can make it even simpler, using serial type:
create table mytable
(
id serial primary key,
data text
);
then insert statement like
insert into mytable (data) values ('dima');
will automatically generate a unique id and put it into the new row for you.
>
>
> 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;
You don't need to lock the whole table to do this either...
select * from table where id=1 FOR UPDATE;
... is what you are looking for - this will put a lock on the rows that
actually get selected, so that another transaction will not be able to
modify them until you commit or rollback.
I hope, it helps...
Dima
В Пнд, 21.07.2003, в 20:03, Dmitry Tkach пишет: > select * from table where id=1 FOR UPDATE; > > ... is what you are looking for - this will put a lock on the rows that > actually get selected, so that another transaction will not be able to > modify them until you commit or rollback. Can new rows be inserted by another connection with id = 1? -- Markus Bertheau. Berlin, Berlin. Germany.
Markus Bertheau wrote: >В Пнд, 21.07.2003, в 20:03, Dmitry Tkach пишет: > > > >>select * from table where id=1 FOR UPDATE; >> >>... is what you are looking for - this will put a lock on the rows that >>actually get selected, so that another transaction will not be able to >>modify them until you commit or rollback. >> >> > >Can new rows be inserted by another connection with id = 1? > > > Yes, unless you have a unique constraint (or primary key) on that table for the id (if you do, then attempts to insert another row with the same id will fail)... Dima P.S. Your name doesn't look Russian to me... How come you seem to be using a Russian email client? :-)
В Срд, 23.07.2003, в 00:02, Dmitry Tkach пишет: > P.S. Your name doesn't look Russian to me... How come you seem to be > using a Russian email client? :-) Where does it say that only Russians can use Russian email clients? :) -- Markus Bertheau. Berlin, Berlin. Germany.