On Thu, Dec 17, 2009 at 3:39 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Basically, in a SERIALIZABLE transaction, if the first statement
> which would require a snapshot would currently fail with "ERROR:
> could not serialize access due to concurrent update" we would
> instead get a fresh snapshot and retry -- which is what we do in a
> READ COMMITTED transaction.
So I for multi-statement transactions I don't see what this buys you.
You'll still have to write the code to retry, and postgres retrying in
the cases where it can isn't really going to be a whole lot better.
Moreover I think it would kick in less often than you might expect and
sometimes surprise people by not kicking in when they expect it to.
Any internal queries could count (though i think you get away with
catalog operations in snapshot_now), any volatile functions, etc. So
people might write a single-statement SQL transaction and not bother
writing retry logic and then be surprised by errors.
I'm unclear why serialization failures would be rare. It depends
entirely on the application. If you're querying records which are busy
from concurrent updates you could get a continuous stream of
serialization failures. It seems better to report the situation to the
user all the time since they have to handle it already and might want
to know about the problem and implement some kind of backoff rather
than hide it from them but only sometimes so they still have to write
code to handle it but aren't allows to handle it consistently.
This isn't the first time that we've seen advantages that could be had
from packaging up a whole transaction so the database can see
everything the transaction needs to do. Perhaps we should have an
interface for saying you're going to feed a series of commands which
you want the database to repeat for you verbatim automatically on
serialization failures. Since you can't construct the queries based on
the results of previous queries the database would be free to buffer
them all up and run them together at the end of the transaction which
would allow the other tricky optimizations we've pondered in the past
as well.
--
greg
