Subject: Re: ?

From: [email-masked] (Paul M. Aoki)
Date: Thu, 21 Apr 94 21:24:50 -0700

[email-masked] (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  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: [email-masked] (Farouk Ho)
Date: Fri, 22 Apr 94 13:17:45 AEST

Newsgroups: vanoco.lists.postgres
Path: vanbig.van.oz.au!farouk
From: [email-masked] (Farouk Ho)
Subject: help with record locking
Organization: Vanoco Pty. Ltd. - Australia
Date: 22 Apr 1994 03:17:39 GMT
Message-ID: <[email-masked]>
Sender: [email-masked] (Farouk Ho)
Lines: 16

Hi y'all,

Is there a way to stop the 2nd task from accessing before the 1st one has
finish ?
Is there another way of doing this ?

Any help is greatly appreciated and the cheque's in the mail.