Jorge Godoy wrote:
> Chris <dmagick@gmail.com> writes:
>
> > I'm not sure what type of lock you'd need to make sure no other transactions
> > updated the table (see
> > http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in theory"
> > something like this should work:
> >
> > begin;
> > select id from table order by id desc limit 1;
> > insert into table (id, blah) values (id+1, 'blah');
> > commit;
>
> This is part of the solution, yes. But I would still need locking this table
> so that no other concurrent transaction gets another "id". I don't want to
> lock the main table -- as I believe you're suggesting -- because I want it to
> be searchable and updatable while I'm inserting new data.
So you have to hold a lock that conflicts with itself, but not with
ACCESS SHARE which is the lock acquired by SELECT. I think the first
one on the list with these two properties is SHARE UPDATE EXCLUSIVE.
Have a look at the list yourself:
http://www.postgresql.org/docs/8.1/static/explicit-locking.html
Note the tip at the end of the table:
Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR
UPDATE/SHARE) statement.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support