>>> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> I think the fundamental difference is that a deadlock or
serialization
> failure
> can be predicted as a potential problem when writing the code. This
is
> something that can happen for any query any time, even plain old
read-only
> select queries.
I've heard that on Oracle it is (or at least was) possible to get a
serialization failure on a single SELECT statement which was the only
user-requested activity on the system, because it could conflict with
automatic maintenance operations.
In Sybase and Microsoft databases it is definitely possible for a
plain old read-only SELECT statement to be a deadlock victim (reported
as a serialization failure) if some of the data it is referencing is
being updated concurrently. In these (and many other) products, a
lock must be acquired before a row can be read. Imagine, the SELECT
locks a row against updates, another transaction locks some other row
against any access, then the UPDATE tries to change the row locked by
the SELECT while the SELECT tries to read the row locked by the
UPDATE.
PostgreSQL is much less prone to serialization failures, but it is
certainly understandable if hot standby replication introduces new
cases of it.
-Kevin