agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: Paul M. Aoki <aoki@postgres.Berkeley.EDU>
To: Farouk Ho <farouk@van.oz.au>
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: ?
Date: Thu, 21 Apr 94 21:24:50 -0700
Message-ID: <199404220424.VAA04731@faerie.CS.Berkeley.EDU> (raw)
In-Reply-To: <9404221317.aa14397@vanbig.van.oz.au>

farouk@van.oz.au (Farouk Ho) writes:
> The situation is this, there are several tasks are accessing a single class
> with one instance, containing a serial number. Each task can at any time
> access the instance and update the value by 1 (retrieve and update cmd).
> What I need is that when a task access the instance, other task should not
> be able to access it and must wait until the previous task has finish (thus
> each task should access a different value).

postgres does table-level locking on the base tables.  the following
transaction should do about what you want:
	begin
	replace counter_tbl (c = c + 1)
	retrieve (counter_tbl.c)
	end

under postgres 4.x (x < 2), replace (read-modify-write) would
read-lock then write-lock.  this meant that several competing xacts
could acquire read locks and then reach for a write lock -- blam,
deadlock (you can't get a write lock while other xacts have read
locks).

in 4.2, replace now gets the write lock first.  this reduces
concurrency but you also don't get scads of bogus deadlocks.  4.2 also
fixes a number of bugs relating to multi-user access (various race
conditions, etc. -- see the 4.2 release notes).

something i've thought about but never looked into is this: postgres
keeps its dead tuples (until you vacuum).  hence a high-update table
like this quickly winds up wading through tons of dead tuples until it
comes to the one that's current.  at a certain update rate, it may pay
to define an index over the one-record table -- it means more work
(you have to update the index) but it may actually speed up updates
(you can find the valid record more quickly). 
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

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: postgres@postgres.berkeley.edu
  Cc: aoki@postgres.Berkeley.EDU, farouk@van.oz.au
  Subject: Re: ?
  In-Reply-To: <199404220424.VAA04731@faerie.CS.Berkeley.EDU>

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

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