Jorge Godoy wrote: > Chris <dmagick@xxxxxxxxx> 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
