pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shane Wright <me@shanewright.co.uk>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: table locking
Date: Mon, 18 Feb 2002 15:22:11 +0000
Message-ID: <200202181521.g1IFLwk24097@fullerruss.dsvr.co.uk> (raw)
In-Reply-To: <200202181516.g1IFGF517803@candle.pha.pa.us>
References: <200202181516.g1IFGF517803@candle.pha.pa.us>

Hi Bruce

Thanks for the information (and the book link, straight in the bookmarks and 
remembered for an Amazon search...)

Anyway, the chapter was helpful, but I'm still stuck.  SERIALIZABLE looks 
like it would do the job, but I don't want any queries to fail/rollback - I'd 
rather they stalled on SET TRANSACTION or LOCK TABLE until the first was 
complete.

Is this possible?

I am also assuming that even with SERIALIZABLE, concurrent SELECTs can 
proceed unhindered?

Thanks

Shane


On Monday 18 Feb 2002 3:16 pm, Bruce Momjian wrote:
> 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



view thread (9+ messages)  latest in thread

Message-ID: <200202181521.g1IFLwk24097@fullerruss.dsvr.co.uk>
Permalink:  ../200202181521.g1IFLwk24097@fullerruss.dsvr.co.uk/
Also on:    postgresql.org/message-id/200202181521.g1IFLwk24097@fullerruss.dsvr.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: me@shanewright.co.uk, pgman@candle.pha.pa.us
  Subject: Re: table locking
  In-Reply-To: <200202181521.g1IFLwk24097@fullerruss.dsvr.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox