Re: New to SQL; hopefully simple question

On Fri, 2003-04-18 at 18:58, foo2(at)on-spammers2(dot)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(at)rbt(dot)ca>

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