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
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