[TLM] Retrying transactions in serializable isolation level

From: Laurent Birtz <laurent(dot)birtz(at)kryptiva(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: [TLM] Retrying transactions in serializable isolation level
Date: 2007-12-24 18:03:15
Message-ID: 20071224180632.D9AA7414281@qatlm3.calidad2.pandasoftware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I am trying to execute a long series of statements within a transaction
in "serializable" isolation level.

I've read Tom Lane's excellent document describing concurrency issues in
Postgres and learned of the general method of doing this:

loop
BEGIN;
SELECT hits FROM webpages WHERE url = ...;
-- client internally computes $newval = $hits + 1
UPDATE webpages SET hits = $newval WHERE url = ..;
if (no error)
break out of loop;
else
ROLLBACK;
end loop
COMMIT;

However, I am having problem implementing this scheme in C with libpq.
Transactions can be aborted because a deadlock occurred or another
transaction already made some changes to the database.

My question is how exactly do I detect that this occurred? Will Postgres
tell me that the transaction failed when I receive a result for a
particular statement? Can Postgres returns an error when I try to commit,
as well? And which exactly are the error codes returned by Postgres when
I should retry the transaction? I guess that SERIALIZATION FAILURE is one
of these errors, but are there others too? Clearly I don't want to retry
a transaction that will always fail for reasons unrelated to concurrency.

I spent 4 hours trying to find a code snippet that does this. So far I've
been unsuccessful. Any precisions would be greatly appreciated.

Thanks,
Laurent Birtz

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2007-12-24 18:03:53 Re: Postgres from PHP in Leopard
Previous Message Richard Broersma Jr 2007-12-24 17:54:48 [TLM] Re: pg_ctl start fails on Windows