Skip site navigation (1) Skip section navigation (2)

Re: Retrying transactions in serializable isolation level

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 (view raw or flat)
Thread:
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.

In response to

pgsql-general by date

Next:From: Gurjeet SinghDate: 2007-12-26 07:08:12
Subject: Re: how to alter an enum type
Previous:From: Usama DarDate: 2007-12-25 16:38:18
Subject: Re: Restoring 8.0 db to 8.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group