Skip site navigation (1) Skip section navigation (2)

Re: Table locks

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andreas Seltenreich <seltenreich(at)gmx(dot)de>
Cc: nsuk(at)users(dot)sourceforge(dot)net (Jake Stride),pgsql-novice(at)postgresql(dot)org
Subject: Re: Table locks
Date: 2004-09-09 16:52:26
Message-ID: 6832.1094748746@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Andreas Seltenreich <seltenreich(at)gmx(dot)de> writes:
> Jake Stride writes:
>> I thought of doing:
>> SELECT max(jobno) from jobs where companyid=1;

> I think SELECT FOR UPDATE should work fine here.

Nope; he'll get something like

regression=# select max(unique1) from tenk1 for update;
ERROR:  SELECT FOR UPDATE is not allowed with aggregate functions

His best bet is probably

		BEGIN;
		LOCK TABLE jobs;
		SELECT max(jobno) from jobs where companyid=1;
		INSERT INTO jobs ...
		COMMIT;

This is pretty horrid from a concurrency point of view but I don't think
there's any other way to meet the "no gaps" requirement.

You could reduce the strength of the lock a bit, for instance
		LOCK TABLE jobs IN EXCLUSIVE MODE;
which would allow readers of the jobs table to proceed concurrently,
but not writers.  If you were willing to assume that all inserters into
jobs are cooperating by explicitly obtaining the correct lock, you
could reduce it to
		LOCK TABLE jobs IN SHARE UPDATE EXCLUSIVE MODE;
which is the lowest self-conflicting table lock type.  This would allow
unrelated updates to the jobs table to proceed concurrently too (though
not VACUUMs).  See

http://www.postgresql.org/docs/7.4/static/explicit-locking.html

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: the inquirerDate: 2004-09-09 17:49:26
Subject: PL/pgSQL Function Problem
Previous:From: Andreas SeltenreichDate: 2004-09-09 16:02:59
Subject: Re: Table locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group