From: Rod Taylor <rbt@rbt.ca>
To: the2@newsgroup2.com
Cc: pgsql-sql@postgresql.org
Subject: Re: New to SQL; hopefully simple question
Date: 23 Apr 2003 13:40:08 -0400
Message-ID: <1051119608.38778.28.camel@jester> (raw)
In-Reply-To: <1e83d3494141f5a5be1ad65b3ebd1756@news.teranews.com>
References: <1e83d3494141f5a5be1ad65b3ebd1756@news.teranews.com>
On Fri, 2003-04-18 at 18:58, foo2@on-spammers2.com wrote:
> I have a situation where I want to keep a bunch of ordinals in a row
> of a table maintained by the database. From this table, I want to
> obtain a number, and increment it in the table. This is the general
> idea, abstracted to the key parts (and it works, as far as it goes:
>
> "select valueone from ordtable"
> VALUE = PQgetvalue(res,0,0)
> ...compute VALUE = VALUE + 1
> "update ordtable SET valueone = VALUE"
>
> ...as I said, this works. however, as far as I understand it, it's
> divisible and thus subject to multi-user fugue. What I need,
> conceptually, is:
>
> lock the table so anyone else waits for the unlock (not fails, waits)
> do the above
> unlock the table
>
> Can anyone point me in the right direction for this? I looked at lock,
> but it seems to only apply across one DB action. Yes? No?
No.. table locks will persist for the length of the transaction.
BEGIN;
LOCK TABLE ...;
SELECT ...
<computations>
UPDATE ...
COMMIT;
However, does your update really change the entire table? If you are
updating the same row as you selected (same WHERE clause in SELECT and
UPDATE statements) you can:
BEGIN;
SELECT ... WHERE ... FOR UPDATE OF ordtable;
<computations>
UPDATE ... WHERE ...;
COMMIT;
The above locks only the rows you intend to work with rather than the
entire table. This can allow work to happen on the unlocked
(non-selected) rows on the table.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.ascAttachments:
signature.asc (application/pgp-signature, 186B) download
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: pgsql-sql@postgresql.org
Cc: rbt@rbt.ca, the2@newsgroup2.com
Subject: Re: New to SQL; hopefully simple question
In-Reply-To: <1051119608.38778.28.camel@jester>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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