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: 20030731170748.L29530-100000@megazone.bigpanda.com (view raw or flat)
Thread:
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:
>  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.



In response to

Responses

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-2014 The PostgreSQL Global Development Group