Subject: Re: New to SQL; hopefully simple question
From: Rod Taylor <rbt@rbt.ca>
To: the2@newsgroup2.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1e83d3494141f5a5be1ad65b3ebd1756@news.teranews.com>
References: <1e83d3494141f5a5be1ad65b3ebd1756@news.teranews.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-/8lC22aaN8KuMXCAeNbv"
Organization: 
Message-Id: <1051119608.38778.28.camel@jester>
Mime-Version: 1.0
Date: 23 Apr 2003 13:40:08 -0400

--=-/8lC22aaN8KuMXCAeNbv
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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:
>=20
> "select valueone from ordtable"
> VALUE =3D PQgetvalue(res,0,0)
> ...compute VALUE =3D VALUE + 1
> "update ordtable SET valueone =3D VALUE"
>=20
> ...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:
>=20
> lock the table so anyone else waits for the unlock (not fails, waits)
> do the above
> unlock the table
>=20
> 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.

--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-/8lC22aaN8KuMXCAeNbv
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+ps/36DETLow6vwwRApBSAJ0aTc9KONBPUA1jyiMj2HWJh3pSWACdGDdD
7W5r/kY+SQj0SRO68YygtHI=
=uhSU
-----END PGP SIGNATURE-----

--=-/8lC22aaN8KuMXCAeNbv--