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

could not serialize access due to concurrent update

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: could not serialize access due to concurrent update
Date: 2007-08-10 20:23:13
Message-ID: 46BCC931.5060304@janestcapital.com (view raw or flat)
Thread:
Lists: pgsql-novice
Actually, I have something of a weird problem.  I'm trying to test some 
code of mine to handle cases where a retry is needed when working with 
serializable transactions.  My problem is that I can *not* figure out 
how to trigger this error:


> UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands 
> behave the same as SELECT in terms of searching for target rows: they 
> will only find target rows that were committed as of the transaction 
> start time. However, such a target row may have already been updated 
> (or deleted or locked) by another concurrent transaction by the time 
> it is found. In this case, the serializable transaction will wait for 
> the first updating transaction to commit or roll back (if it is still 
> in progress). If the first updater rolls back, then its effects are 
> negated and the serializable transaction can proceed with updating the 
> originally found row. But if the first updater commits (and actually 
> updated or deleted the row, not just locked it) then the serializable 
> transaction will be rolled back with the message
>
>ERROR:  could not serialize access due to concurrent update
>
> because a serializable transaction cannot modify or lock rows changed 
> by other transactions after the serializable transaction began.
>

from:
http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-SERIALIZABLE

I know I need at least two different transactions on the same database 
stepping on each other somehow, but the worst case I can come up with is 
a deadlock case (which I want to retry, but that I know works).  But I 
want to trigger the above error to make sure it works.  Most of the 
examples I've come up with don't even generate an error, one transaction 
simply blocks until the other completes.

Could somebody please send me a simple example of how I can trigger this 
error?  I'm on postgres 8.1.4.

For the record, Postgresql is amazingly, sometimes even annoyingly, robust.

Brian


Responses

pgsql-novice by date

Next:From: Andrej Ricnik-BayDate: 2007-08-10 20:44:30
Subject: Re: Free designable front end for PostgreSQL
Previous:From: Andrew C. UseltonDate: 2007-08-10 20:21:09
Subject: Re: pg_dump/pg_dumpall

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