Обсуждение: Parallel transactions failing oddly
Hi,
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.
Without lock on "ryhmalaiset" only some of the transactions will fail
and some will not.
Any ideas what we are doing wrong?
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.
On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > 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. > > > 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. Umh, but as the "ryhmalaiset" table is locked until the transaction is commited? And what do you mean with "at best"? Is there any way ensuring that the other transactions won't access the table until the first one has finished updating it if the lock is not enough?
On 1 Aug 2003, Mauri Sahlberg wrote: > On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > > 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. > > > > > 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. > > Umh, but as the "ryhmalaiset" table is locked until the transaction is > commited? And what do you mean with "at best"? Is there any way ensuring > that the other transactions won't access the table until the first one > has finished updating it if the lock is not enough? I said at best because I dont think serializable mode transactions won't see the row even after commit as long as its snapshot's been taken already. You are locking the table in access exclusive mode right?
On pe, 2003-08-01 at 08:51, Stephan Szabo wrote: > On 1 Aug 2003, Mauri Sahlberg wrote: > > > On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > > > 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. > > > > > > > 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. > > > > Umh, but as the "ryhmalaiset" table is locked until the transaction is > > commited? And what do you mean with "at best"? Is there any way ensuring > > that the other transactions won't access the table until the first one > > has finished updating it if the lock is not enough? > > I said at best because I dont think serializable mode transactions won't > see the row even after commit as long as its snapshot's been taken > already. You are locking the table in access exclusive mode right? Yes.
On 1 Aug 2003, Mauri Sahlberg wrote: > On pe, 2003-08-01 at 08:51, Stephan Szabo wrote: > > On 1 Aug 2003, Mauri Sahlberg wrote: > > > > > On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > > > > 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. > > > > > > > > > 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. > > > > > > Umh, but as the "ryhmalaiset" table is locked until the transaction is > > > commited? And what do you mean with "at best"? Is there any way ensuring > > > that the other transactions won't access the table until the first one > > > has finished updating it if the lock is not enough? > > > > I said at best because I dont think serializable mode transactions won't > > see the row even after commit as long as its snapshot's been taken > > already. You are locking the table in access exclusive mode right? > > Yes. Can you make a complete test case that shows the problem(hopefully simpler than your entire system)?
On 1 Aug 2003, Mauri Sahlberg wrote: > On pe, 2003-08-01 at 08:51, Stephan Szabo wrote: > > On 1 Aug 2003, Mauri Sahlberg wrote: > > > > > On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > > > > 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. > > > > > > > > > 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. > > > > > > Umh, but as the "ryhmalaiset" table is locked until the transaction is > > > commited? And what do you mean with "at best"? Is there any way ensuring > > > that the other transactions won't access the table until the first one > > > has finished updating it if the lock is not enough? > > > > I said at best because I dont think serializable mode transactions won't > > see the row even after commit as long as its snapshot's been taken > > already. You are locking the table in access exclusive mode right? > > Yes. Another possibility is if you locking the table inside the function? If so, I think you may not see the state of the other transaction's commits even in read committed isolation.
On pe, 2003-08-01 at 09:21, Stephan Szabo wrote: > On 1 Aug 2003, Mauri Sahlberg wrote: > > > On pe, 2003-08-01 at 08:51, Stephan Szabo wrote: > > > On 1 Aug 2003, Mauri Sahlberg wrote: > > > > > > > On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > > > > > 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. > > > > > > > > > > > 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. > > > > > > > > Umh, but as the "ryhmalaiset" table is locked until the transaction is > > > > commited? And what do you mean with "at best"? Is there any way ensuring > > > > that the other transactions won't access the table until the first one > > > > has finished updating it if the lock is not enough? > > > > > > I said at best because I dont think serializable mode transactions won't > > > see the row even after commit as long as its snapshot's been taken > > > already. You are locking the table in access exclusive mode right? > > > > Yes. > > Another possibility is if you locking the table inside the function? If > so, I think you may not see the state of the other transaction's commits > even in read committed isolation. > c++-code calling function that is calling several functions... Tried locking the table at the very first function and locking the table just before the culprit select and inserts. I haven't touched the transaction isolation settings so the isolation level is read committed. I talked again with the database coder and reread the documentation about the concurrency control and mvcc-snapshots. And thanks to your responses, I have now somekind of clue what is happening. I think it is time for the databse coder to rethink the application logic and for me to do some more tests. If this won't help, I'll bug you again with simplified case. Thank you very much for the prompt assistance.
On pe, 2003-08-01 at 03:12, Stephan Szabo wrote: > > 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. > > > 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. Umh, but as the "ryhmalaiset" table is locked until the transaction is commited? And what do you mean with "at best"? Is there any way ensuring that the other transactions won't access the table until the first one has finished updating it if the lock is not enough?