Lists: | pgsql-general |
---|
From: | Laurent Birtz <laurent(dot)birtz(at)kryptiva(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Retrying transactions in serializable isolation level |
Date: | 2007-12-24 18:03:15 |
Message-ID: | 476FF463.9090002@kryptiva.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Laurent Birtz <laurent(dot)birtz(at)kryptiva(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Retrying transactions in serializable isolation level |
Date: | 2007-12-25 19:17:49 |
Message-ID: | 20071225191749.GA30758@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Laurent Birtz wrote:
> 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;
I think you should be able to shave some milliseconds off that loop by
using SAVEPOINTs instead of a full-blown transaction for each tuple.
Something like
BEGIN
outer loop
inner loop
SAVEPOINT foo;
SELECT hits ...
UPDATE webpages SET hits = ...
if (no error)
break inner loop
else
ROLLBACK TO foo
end inner loop
end outer 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?
IIRC you can apply PQresultStatus to the PGresult returned by the UPDATE.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.