Обсуждение: Retrying transactions in serializable isolation level

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

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.