Home Explore Blog CI



postgresql

10th chunk of `doc/src/sgml/mvcc.sgml`
1ad62e1aa037b6ea885aee42309b5582341566c168ac23730000000100000fa2
 is read, because such a
    transaction waits until it can acquire a snapshot guaranteed to be free
    from such problems before starting to read any data.  In all other cases
    applications must not depend on results read during a transaction that
    later aborted; instead, they should retry the transaction until it
    succeeds.
   </para>

   <para>
    To guarantee true serializability <productname>PostgreSQL</productname>
    uses <firstterm>predicate locking</firstterm>, which means that it keeps locks
    which allow it to determine when a write would have had an impact on
    the result of a previous read from a concurrent transaction, had it run
    first.  In <productname>PostgreSQL</productname> these locks do not
    cause any blocking and therefore can <emphasis>not</emphasis> play any part in
    causing a deadlock.  They are used to identify and flag dependencies
    among concurrent Serializable transactions which in certain combinations
    can lead to serialization anomalies.  In contrast, a Read Committed or
    Repeatable Read transaction which wants to ensure data consistency may
    need to take out a lock on an entire table, which could block other
    users attempting to use that table, or it may use <literal>SELECT FOR
    UPDATE</literal> or <literal>SELECT FOR SHARE</literal> which not only
    can block other transactions but cause disk access.
   </para>

   <para>
    Predicate locks in <productname>PostgreSQL</productname>, like in most
    other database systems, are based on data actually accessed by a
    transaction.  These will show up in the
    <link linkend="view-pg-locks"><structname>pg_locks</structname></link>
    system view with a <literal>mode</literal> of <literal>SIReadLock</literal>.  The
    particular locks
    acquired during execution of a query will depend on the plan used by
    the query, and multiple finer-grained locks (e.g., tuple locks) may be
    combined into fewer coarser-grained locks (e.g., page locks) during the
    course of the transaction to prevent exhaustion of the memory used to
    track the locks.  A <literal>READ ONLY</literal> transaction may be able to
    release its SIRead locks before completion, if it detects that no
    conflicts can still occur which could lead to a serialization anomaly.
    In fact, <literal>READ ONLY</literal> transactions will often be able to
    establish that fact at startup and avoid taking any predicate locks.
    If you explicitly request a <literal>SERIALIZABLE READ ONLY DEFERRABLE</literal>
    transaction, it will block until it can establish this fact.  (This is
    the <emphasis>only</emphasis> case where Serializable transactions block but
    Repeatable Read transactions don't.)  On the other hand, SIRead locks
    often need to be kept past transaction commit, until overlapping read
    write transactions complete.
   </para>

   <para>
    Consistent use of Serializable transactions can simplify development.
    The guarantee that any set of successfully committed concurrent
    Serializable transactions will have the same effect as if they were run
    one at a time means that if you can demonstrate that a single transaction,
    as written, will do the right thing when run by itself, you can have
    confidence that it will do the right thing in any mix of Serializable
    transactions, even without any information about what those other
    transactions might do, or it will not successfully commit.  It is
    important that an environment which uses this technique have a
    generalized way of handling serialization failures (which always return
    with an SQLSTATE value of '40001'), because it will be very hard to
    predict exactly which transactions might contribute to the read/write
    dependencies and need to be rolled back to prevent serialization
    anomalies.  The monitoring of read/write dependencies has a cost, as does
    the restart of transactions which are terminated with a serialization

Title: Predicate Locking in Serializable Transactions
Summary
To guarantee true serializability, PostgreSQL uses predicate locking, which involves acquiring locks that allow the database to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, and these locks do not cause blocking or deadlocks, but are used to identify and flag dependencies among concurrent Serializable transactions that can lead to serialization anomalies, and the use of Serializable transactions can simplify development by providing a guarantee that successfully committed transactions will have the same effect as if they were run one at a time.