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
