Re: PostgreSQL transaction locking problem

Jeff Martin wrote:
>
> first the case of my example is just that, an example. i want to learn to
> use transactions and locking so that i can do the following....
>
> 1. run multiple processes in different transactions,
> 2. executing the same pg/sql functions which,
> 3. need to read data at the beginning of the function that is committed,
> 4. perform calculations and write a result.
> 5. thus competing processes will need to wait for each to commit the result
> in turn.

I kind of missed the beginning of this thread, so pardon me if I'm way
off base. But the behavior you describe just requires the use of
SELECT...FOR UPDATE. The second transaction will block awaiting the
COMMIT/ABORT of the first.

Session #1:

BEGIN;
SELECT balance FROM checking FOR UPDATE;

Session #2:

BEGIN;
SELECT balance FROM checking FOR UPDATE;

^== Blocks until Session #1 COMMITS/ABORTS

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com