Skip site navigation (1) Skip section navigation (2)

Re: Parallel transactions failing oddly

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Mauri Sahlberg <Mauri(dot)Sahlberg(at)pretax(dot)net>
Cc: <pgsql-admin(at)postgresql(dot)org>, <petri(dot)hilska(at)pretax(dot)net>
Subject: Re: Parallel transactions failing oddly
Date: 2003-08-01 00:12:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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:
>   SELECT * INTO r FROM kelaaryhma(aToimiala, aKielikoodi, pKoodi);
>   SELECT INTO pRyhmalaiset * FROM ryhmalaiset
>           toimiala      = aToimiala             AND
>           ryhma         = pKoodi                AND
>           kuukausi      = aKuukausi             AND
>           vuosi         = aVuosi;
>           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.

In response to


pgsql-admin by date

Next:From: Romildo WildgrubeDate: 2003-08-01 00:18:47
Subject: Re: a question
Previous:From: MarvinDate: 2003-07-31 23:08:52
Subject: Re: Starting PostgreSQL server as root

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group