Re: pgsql-function called twice in the same second ->

On Tue, 26 Nov 2002, Peter Alberer wrote:

> The exercise usage process consists of 2 steps: When the student starts
> the exercise the corresponding entry is "locked". (the id of the
> exercise is inserted into a locking table) Every exercise usage
> generates a usage_id that is recorded in the locking table, so the
> exercise can not be deleted while used. When an answer to the exercise
> is received, the exercise usage data is inserted into another table and
> the locking entry is removed.
>
> So the function first tries to retrieve data from the locking table,
> saves (inserts) that data in another table and deletes the entry from
> the locking table. If this function is called twice at the same time,
> both runs get the data from the locking table and both try to do the
> insert. in the first run, the commit succeeds, but in the second run an
> error is generated and the function fails.

You might want that first select to be a SELECT FOR UPDATE so that the
second transaction trying to get the same row waits for the first
transaction to run to completion.