Re: table locking

Shane Wright wrote:
> Hi
> 
> I'm having trouble getting my head round postgres's locking.  I've read 
> through all the docs but cant seem to find the right thingthat does what I 
> want...
> 
> 
> Basically, there are a few tables in my system where a SELECT is done prior 
> to an INSERT to avoid duplicating certain data and so forth.
> 
> So, I want to lock the table for the transaction that does the stuff above so 
> no more than one can run at a time (essentially making it SERIALIZABLE).  
> But, I dont want to block other readers from SELECTing from the table at the 
> same time...
> 
> This is the classic multiple readers, single writer problem yes?  I'm sure 
> its doable, but I cant work out which locking / transaction isolation levels 
> to use.

See my chapter on transactions.  I think you want SERIALIZABLE
transaction mode.
http://www.postgresql.org/docs/awbook.html

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026