Re: Parallel transactions failing oddly

On 31 Jul 2003, Mauri Sahlberg wrote:

> Either we have found a bug in Postgres (which I seriously doubt) or we
> are being stupid clever enough way to not notice it.
>
> We have five complex "transactions" that are executed thru pq++/c++
> interface. If we run them one by one everything goes fine. But if I
> run them in parallel - in separate processes - all but the first one
> claiming the lock for "ryhmalaiset"-table will fail. And they will
> fail as soon as the first one is finished by trying to insert
> duplicate row in the shared table. Incidentally this row would also be
> the very first row they are trying to insert. They all run the same code
> but with different data.
>
> Each transaction fails in the following insert:
>  LOOP
>   SELECT * INTO r FROM kelaaryhma(aToimiala, aKielikoodi, pKoodi);
>
>   SELECT INTO pRyhmalaiset * FROM ryhmalaiset
>   WHERE
>           toimiala      = aToimiala             AND
>           ryhma         = pKoodi                AND
>           kuukausi      = aKuukausi             AND
>           vuosi         = aVuosi;
>
>   IF NOT FOUND THEN
>           aMessage:= pKoodi::VARCHAR(16)||''/''||aKoodi::VARCHAR(16);
>           RAISE NOTICE ''ins-ryhmalaiset %'', aMessage;
>           INSERT INTO ryhmalaiset
>               (toimiala, ryhma, jasen, kuukausi, vuosi, ajettu)
>             VALUES
>               (aToimiala, pKoodi, aKoodi, aKuukausi, aVuosi,
>                ''now''::timestamp);
>   END IF;
> ...
>
> Where (toimiala, ryhma, kuukausi, jasen, vuosi) are primary index
> fields. The "IF NOT FOUND" should ensure that no duplicate inserts are
> attempted but somehow it just fails when two or more processess are
> run.

The second transaction won't see the row inserted by the first transaction
until it commits (at best).  Both transactions can think there are no
matching rows.