Return-Path: owner-postman 
Delivery-Date: Fri, 22 Apr 94 01:19:00 -0700
Return-Path: owner-postman
Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id VAA21754 for postgres-redist; Thu, 21 Apr 1994 21:25:02 -0700
Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199404220425.VAA21754@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: owner-postman
Received: from faerie.CS.Berkeley.EDU (faerie.CS.Berkeley.EDU [128.32.149.14]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with ESMTP id VAA21745 for <postgres@postgres.Berkeley.EDU>; Thu, 21 Apr 1994 21:25:01 -0700
Received: from localhost (localhost [127.0.0.1]) by faerie.CS.Berkeley.EDU (8.6.4/8.1B) with SMTP id VAA04731; Thu, 21 Apr 1994 21:24:51 -0700
Message-Id: <199404220424.VAA04731@faerie.CS.Berkeley.EDU>
X-Authentication-Warning: faerie.CS.Berkeley.EDU: Host localhost didn't use HELO protocol
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: farouk@van.oz.au (Farouk Ho)
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: ?
Reply-To: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
In-reply-to: Your message of Fri, 22 Apr 94 13:17:45 AEST 
	     <9404221317.aa14397@vanbig.van.oz.au> 
Date: Thu, 21 Apr 94 21:24:50 -0700
X-Sender: aoki@postgres.Berkeley.EDU
Resent-To: postgres-redist@postgres.Berkeley.EDU
X-Mts: smtp
Resent-Date: Thu, 21 Apr 94 21:25:01 -0700
Resent-XMts: smtp

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