Home Explore Blog CI



postgresql

25th chunk of `doc/src/sgml/mvcc.sgml`
eaffebe983adefa60fc9dc6b0df7f7183fe28be943606fff0000000100000fa3
 was held.
    </para>

    <para>
     Global validity checks require extra thought under
     non-serializable <acronym>MVCC</acronym>.
     For example, a banking application might wish to check that the sum of
     all credits in one table equals the sum of debits in another table,
     when both tables are being actively updated.  Comparing the results of two
     successive <literal>SELECT sum(...)</literal> commands will not work reliably in
     Read Committed mode, since the second query will likely include the results
     of transactions not counted by the first.  Doing the two sums in a
     single repeatable read transaction will give an accurate picture of only the
     effects of transactions that committed before the repeatable read transaction
     started &mdash; but one might legitimately wonder whether the answer is still
     relevant by the time it is delivered.  If the repeatable read transaction
     itself applied some changes before trying to make the consistency check,
     the usefulness of the check becomes even more debatable, since now it
     includes some but not all post-transaction-start changes.  In such cases
     a careful person might wish to lock all tables needed for the check,
     in order to get an indisputable picture of current reality.  A
     <literal>SHARE</literal> mode (or higher) lock guarantees that there are no
     uncommitted changes in the locked table, other than those of the current
     transaction.
    </para>

    <para>
     Note also that if one is relying on explicit locking to prevent concurrent
     changes, one should either use Read Committed mode, or in Repeatable Read
     mode be careful to obtain
     locks before performing queries.  A lock obtained by a
     repeatable read transaction guarantees that no other transactions modifying
     the table are still running, but if the snapshot seen by the
     transaction predates obtaining the lock, it might predate some now-committed
     changes in the table.  A repeatable read transaction's snapshot is actually
     frozen at the start of its first query or data-modification command
     (<literal>SELECT</literal>, <literal>INSERT</literal>,
     <literal>UPDATE</literal>, <literal>DELETE</literal>, or
     <literal>MERGE</literal>), so it is possible to obtain locks explicitly
     before the snapshot is frozen.
    </para>
   </sect2>
  </sect1>

  <sect1 id="mvcc-serialization-failure-handling">
   <title>Serialization Failure Handling</title>

   <indexterm>
    <primary>serialization failure</primary>
   </indexterm>
   <indexterm>
    <primary>retryable error</primary>
   </indexterm>

   <para>
    Both Repeatable Read and Serializable isolation levels can produce
    errors that are designed to prevent serialization anomalies.  As
    previously stated, applications using these levels must be prepared to
    retry transactions that fail due to serialization errors.  Such an
    error's message text will vary according to the precise circumstances,
    but it will always have the SQLSTATE code <literal>40001</literal>
    (<literal>serialization_failure</literal>).
   </para>

   <para>
    It may also be advisable to retry deadlock failures.
    These have the SQLSTATE code <literal>40P01</literal>
    (<literal>deadlock_detected</literal>).
   </para>

   <para>
    In some cases it is also appropriate to retry unique-key failures,
    which have SQLSTATE code <literal>23505</literal>
    (<literal>unique_violation</literal>), and exclusion constraint
    failures, which have SQLSTATE code <literal>23P01</literal>
    (<literal>exclusion_violation</literal>).  For example, if the
    application selects a new value for a primary key column after
    inspecting the currently stored keys, it could get a unique-key
    failure because another application instance selected the same new key
    concurrently.  This is effectively a serialization failure, but the
    server will not detect it as such because

Title: Serialization Failure Handling and Locking Considerations
Summary
This section discusses the handling of serialization failures in PostgreSQL, including retrying transactions that fail due to serialization errors, deadlock failures, unique-key failures, and exclusion constraint failures. It also highlights the importance of careful locking and transaction management to prevent concurrency issues and ensure data consistency, particularly in non-serializable MVCC environments.