pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table locking.
9+ messages / 4 participants
[nested] [flat]

* Table locking.
@ 2001-01-11 03:18  Michael Richards <michael@fastmail.ca>
  0 siblings, 0 replies; 9+ messages in thread

From: Michael Richards @ 2001-01-11 03:18 UTC (permalink / raw)
  To: pgsql-sql

Hi.

I've always had trouble understanding how to properly deal with the 
following case.

I need to run an insert on a table but I don't want any other 
transactions to commit the insert before me. Within the logic of the 
program, I run a select to see if there is a value there, then the 
insert. Between these 2 statements someone could commit one that 
causes the insert to fail.

Is there a better way to do this than locking the table in SHARE ROW 
EXCLUSIVE MODE and trying to be certain that anything else that may 
call an insert there asks for the same type of lock?

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians
From pgsql-sql-owner@postgresql.org  Wed Jan 10 23:59:39 2001
Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28])
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0B4bBB90828
	for <pgsql-sql@postgresql.org>; Wed, 10 Jan 2001 23:37:11 -0500 (EST)
	(envelope-from pgsql-sql-owner@postgresql.org)
Received: from paprika.michvhf.com (adsl-dynamic35-204.detroit.mi.ameritech.net [64.108.244.204] (may be forged))
	by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f09FLEB22185
	for <pgsql-sql@postgresql.org>; Tue, 9 Jan 2001 10:21:14 -0500 (EST)
	(envelope-from vev@michvhf.com)
Received: (qmail 17781 invoked by uid 1001); 9 Jan 2001 15:21:10 -0000
Date: Tue, 9 Jan 2001 10:21:10 -0500 (EST)
From: Vince Vielhaber <vev@michvhf.com>
To: The Hermit Hacker <scrappy@hub.org>
cc: Kaare Rasmussen <kar@webline.dk>, <pgsql-hackers@postgresql.org>,
   <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] Re: PostgreSQL web page
In-Reply-To: <Pine.BSF.4.31.0101090857120.21326-100000@thelab.hub.org>
Message-ID: <Pine.BSF.4.30.0101091007350.17695-100000@paprika.michvhf.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200101/102
X-Sequence-Number: 763

On Tue, 9 Jan 2001, The Hermit Hacker wrote:

> On Tue, 9 Jan 2001, Kaare Rasmussen wrote:
>
> > I wonder why http://www.postgresql.org is not updated with news. The
> > latest "Latest News" is from Nov 16. I have to go to
> > http://www.pgsql.com to find some interesting news later than that:
> > 2000-12-22 PostgreSQL, Inc. Releases Open Source Replication & Database
> > and                            2000-12-12 2000 Linux Journal Editor's
> > Choice Award for Best Database
> >
> > In fact I was looking for the Replication server. No mention seems to be
> > available on postgresql.org.
> >
> > Also the mailing list archive for pgsql-sql is not listed here:
> > http://www.postgresql.org/devel-corner/index.html  - but you can see it
> > if you write the correct URL yourself.
>
> I have no graphical browser right now, and lynx isn't installed, so I'm a
> bit at a disadvantage here ... but, pgsql-sql *should* be listed under
> users-gallery, not devel ... devel is -hackers, -bugs, -ports, etc ...

Unless the clock on hub was wrong, I have the award happening on
11/16/Y2K.   The replication server info was a press release by
PostgreSQL, Inc.  Not the PostgreSQL Global Development Group.   Between
GB and PG,Inc with the constant barrage of press releases I made the
decision months ago that NO press releases from ANY group would be posted
on the PostgreSQL website - these groups have their own websites for that
and PostgreSQL.org doesn't need to be caught up in the middle.

pgsql-sql is in the users section - both info and archives.

> Beyond that, the web site is maintained by Vince ... but I thought the
> Award was definitely up there already ...

Both on the main page and in latest news.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* table locking
@ 2002-02-18 14:39  Shane Wright <me@shanewright.co.uk>
  0 siblings, 1 reply; 9+ messages in thread

From: Shane Wright @ 2002-02-18 14:39 UTC (permalink / raw)
  To: pgsql-sql

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.

Thanks

--
Shane







^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 15:16  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Shane Wright <me@shanewright.co.uk>
  0 siblings, 1 reply; 9+ messages in thread

From: Bruce Momjian @ 2002-02-18 15:16 UTC (permalink / raw)
  To: me@shanewright.co.uk; +Cc: pgsql-sql

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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 15:22  Shane Wright <me@shanewright.co.uk>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 9+ messages in thread

From: Shane Wright @ 2002-02-18 15:22 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 15:34  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Shane Wright <me@shanewright.co.uk>
  1 sibling, 1 reply; 9+ messages in thread

From: Bruce Momjian @ 2002-02-18 15:34 UTC (permalink / raw)
  To: me@shanewright.co.uk; +Cc: pgsql-sql

Shane Wright wrote:
> 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?

Actually, I think the default READ COMMITTED will work fine for you. 
Backends will block waiting for your write, then continue when you
commit, and reads will not be affected.  I am not totally sure what you
are testing, so I suggest starting two psql sessions and trying it.

-- 
  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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 15:42  Shane Wright <me@shanewright.co.uk>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Shane Wright @ 2002-02-18 15:42 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-sql

Hi Bruce,

I've had a go with that, but its proving quite complicated because of the 
number of combinations.

The main problem is that the initial SELECT doesn't match any rows (it isnt 
supposed to), so I can use SELECT .. FOR UPDATE or anything.

The exact thing is this...

1 - generate an ID randomly
2 - SELECT in the table to see if a row already has that ID
3 - if it does, loop back and generate another
4- if not, insert the new row with that ID

So, I need to block other processes doing that same thing or they can (and 
do) pick the same ID and it gets duplicated (or one fails because of a UNIQUE 
constraint).

I know it's not the best method for generating IDs, but it works relatively 
well given that I cant use sequences (gives away too much info about whats in
the system), and I dont want to use oids (not portable, and issues on the 
oids surviving across databases).

--
Shane

On Monday 18 Feb 2002 3:34 pm, Bruce Momjian wrote:
> Shane Wright wrote:
> > 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?
>
> Actually, I think the default READ COMMITTED will work fine for you.
> Backends will block waiting for your write, then continue when you
> commit, and reads will not be affected.  I am not totally sure what you
> are testing, so I suggest starting two psql sessions and trying it.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 15:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Shane Wright <me@shanewright.co.uk>
  1 sibling, 2 replies; 9+ messages in thread

From: Tom Lane @ 2002-02-18 15:42 UTC (permalink / raw)
  To: me@shanewright.co.uk; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-sql

I think you want just LOCK TABLE tab IN SHARE ROW EXCLUSIVE MODE
or LOCK TABLE tab IN EXCLUSIVE MODE.  The latter would lock out
SELECT FOR UPDATE, the former wouldn't.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 15:46  Shane Wright <me@shanewright.co.uk>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 9+ messages in thread

From: Shane Wright @ 2002-02-18 15:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Hi Tom,

Thanks for the input, but I specifically can't use SELECT.. FOR UPDATE as the 
SELECT won't match any rows (it isnt supposed to, there's an explanation as 
to in a post I've just sent).  

I had thought about ACCESS EXCLUSIVE MODE; but it looks like it'll block all 
other readers as well, which isn't necessary.

Thanks

--
Shane

On Monday 18 Feb 2002 3:42 pm, Tom Lane wrote:
> I think you want just LOCK TABLE tab IN SHARE ROW EXCLUSIVE MODE
> or LOCK TABLE tab IN EXCLUSIVE MODE.  The latter would lock out
> SELECT FOR UPDATE, the former wouldn't.
>
> 			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: table locking
@ 2002-02-18 16:37  Shane Wright <me@shanewright.co.uk>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 9+ messages in thread

From: Shane Wright @ 2002-02-18 16:37 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Hi Tom,

Sorry I tihnk I misunderstood your post the first time round :(

SHARE ROW EXCLUSIVE MODE works just fine :))  Thanks very much :)

Regards

Shane


On Monday 18 Feb 2002 3:42 pm, Tom Lane wrote:
> I think you want just LOCK TABLE tab IN SHARE ROW EXCLUSIVE MODE
> or LOCK TABLE tab IN EXCLUSIVE MODE.  The latter would lock out
> SELECT FOR UPDATE, the former wouldn't.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2002-02-18 16:37 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-11 03:18 Table locking. Michael Richards <michael@fastmail.ca>
2002-02-18 14:39 table locking Shane Wright <me@shanewright.co.uk>
2002-02-18 15:16 ` Re: table locking Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-18 15:22   ` Re: table locking Shane Wright <me@shanewright.co.uk>
2002-02-18 15:34     ` Re: table locking Bruce Momjian <pgman@candle.pha.pa.us>
2002-02-18 15:42       ` Re: table locking Shane Wright <me@shanewright.co.uk>
2002-02-18 15:42     ` Re: table locking Tom Lane <tgl@sss.pgh.pa.us>
2002-02-18 15:46       ` Re: table locking Shane Wright <me@shanewright.co.uk>
2002-02-18 16:37       ` Re: table locking Shane Wright <me@shanewright.co.uk>

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