mvcc.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

    of the current query within the transaction.  Thus, successive
    <command>SELECT</command> commands within a single transaction always see the same
    data.
   </para>

   <para>
    <command>UPDATE</command>, <command>DELETE</command>, <command>SELECT
    FOR UPDATE</command>, and <command>SELECT FOR SHARE</command> commands
    behave the same as <command>SELECT</command>
    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

<screen>
ERROR:  could not serialize access due to concurrent update
</screen>

    because a serializable transaction cannot modify or lock rows changed by
    other transactions after the serializable transaction began.
   </para>

   <para>
    When the application receives this error message, it should abort
    the current transaction and then retry the whole transaction from
    the beginning.  The second time through, the transaction sees the
    previously-committed change as part of its initial view of the database,
    so there is no logical conflict in using the new version of the row
    as the starting point for the new transaction's update.
   </para>

   <para>
    Note that only updating transactions may need to be retried; read-only
    transactions will never have serialization conflicts.
   </para>

   <para>
    The Serializable mode provides a rigorous guarantee that each
    transaction sees a wholly consistent view of the database.  However,
    the application has to be prepared to retry transactions when concurrent
    updates make it impossible to sustain the illusion of serial execution.
    Since the cost of redoing complex transactions may be significant,
    this mode is recommended only when updating transactions contain logic
    sufficiently complex that they may give wrong answers in Read
    Committed mode.  Most commonly, Serializable mode is necessary when
    a transaction executes several successive commands that must see
    identical views of the database.
   </para>

   <sect3 id="mvcc-serializability">
    <title>Serializable Isolation versus True Serializability</title>

   <indexterm>
    <primary>serializability</primary>
   </indexterm>

   <indexterm>
    <primary>predicate locking</primary>
   </indexterm>

   <para>
    The intuitive meaning (and mathematical definition) of
    <quote>serializable</> execution is that any two successfully committed
    concurrent transactions will appear to have executed strictly serially,
    one after the other &mdash; although which one appeared to occur first may
    not be predictable in advance.  It is important to realize that forbidding
    the undesirable behaviors listed in <xref linkend="mvcc-isolevel-table">
    is not sufficient to guarantee true serializability, and in fact
    <productname>PostgreSQL</productname>'s Serializable mode <emphasis>does
    not guarantee serializable execution in this sense</>.  As an example,
    consider a table <structname>mytab</>, initially containing
<screen>
 class | value 
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200
</screen>
    Suppose that serializable transaction A computes
<screen>
SELECT SUM(value) FROM mytab WHERE class = 1;
</screen>
    and then inserts the result (30) as the <structfield>value</> in a
    new row with <structfield>class</> = 2.  Concurrently, serializable
    transaction B computes
<screen>
SELECT SUM(value) FROM mytab WHERE class = 2;
</screen>
    and obtains the result 300, which it inserts in a new row with
    <structfield>class</> = 1.  Then both transactions commit.  None of
    the listed undesirable behaviors have occurred, yet we have a result
    that could not have occurred in either order serially.  If A had
    executed before B, B would have computed the sum 330, not 300, and
    similarly the other order would have resulted in a different sum
    computed by A.
   </para>

   <para>
    To guarantee true mathematical serializability, it is necessary for
    a database system to enforce <firstterm>predicate locking</>, which
    means that a transaction cannot insert or modify a row that would
    have matched the <literal>WHERE</> condition of a query in another concurrent
    transaction.  For example, once transaction A has executed the query
    <literal>SELECT ... WHERE class = 1</>, a predicate-locking system
    would forbid transaction B from inserting any new row with class 1
    until A has committed.
     <footnote>
      <para>
       Essentially, a predicate-locking system prevents phantom reads
       by restricting what is written, whereas MVCC prevents them by
       restricting what is read.
      </para>
     </footnote>
    Such a locking system is complex to
    implement and extremely expensive in execution, since every session must
    be aware of the details of every query executed by every concurrent
    transaction.  And this large expense is mostly wasted, since in
    practice most applications do not do the sorts of things that could
    result in problems.  (Certainly the example above is rather contrived
    and unlikely to represent real software.)  Accordingly,
    <productname>PostgreSQL</productname> does not implement predicate
    locking, and so far as we are aware no other production DBMS does either.
   </para>

   <para>
    In those cases where the possibility of nonserializable execution
    is a real hazard, problems can be prevented by appropriate use of
    explicit locking.  Further discussion appears in the following
    sections.
   </para>
  </sect3>
  </sect2>
 </sect1>

  <sect1 id="explicit-locking">
   <title>Explicit Locking</title>

   <indexterm>
    <primary>lock</primary>
   </indexterm>

   <para>
    <productname>PostgreSQL</productname> provides various lock modes
    to control concurrent access to data in tables.  These modes can
    be used for application-controlled locking in situations where
    <acronym>MVCC</acronym> does not give the desired behavior.  Also,
    most <productname>PostgreSQL</productname> commands automatically
    acquire locks of appropriate modes to ensure that referenced
    tables are not dropped or modified in incompatible ways while the
    command executes.  (For example, <command>ALTER TABLE</> cannot be
    executed concurrently with other operations on the same table.)
   </para>

   <para>
    To examine a list of the currently outstanding locks in a database
    server, use the <structname>pg_locks</structname> system view
    (<xref linkend="view-pg-locks">). For more
    information on monitoring the status of the lock manager
    subsystem, refer to <xref linkend="monitoring">.
   </para>

  <sect2 id="locking-tables">
   <title>Table-Level Locks</title>

   <indexterm zone="locking-tables">
    <primary>LOCK</primary>
   </indexterm>

   <para>
    The list below shows the available lock modes and the contexts in
    which they are used automatically by
    <productname>PostgreSQL</productname>.  You can also acquire any
    of these locks explicitly with the command <xref
    linkend="sql-lock" endterm="sql-lock-title">.
    Remember that all of these lock modes are table-level locks,
    even if the name contains the word
    <quote>row</quote>; the names of the lock modes are historical.
    To some extent the names reflect the typical usage of each lock
    mode &mdash; but the semantics are all the same.  The only real difference
    between one lock mode and another is the set of lock modes with
    which each conflicts.  Two transactions cannot hold locks of conflicting
    modes on the same table at the same time.  (However, a transaction
    never conflicts with itself.  For example, it may acquire
    <literal>ACCESS EXCLUSIVE</literal> lock and later acquire
    <literal>ACCESS SHARE</literal> lock on the same table.)  Non-conflicting
    lock modes may be held concurrently by many transactions.  Notice in
    particular that some lock modes are self-conflicting (for example,
    an <literal>ACCESS EXCLUSIVE</literal> lock cannot be held by more than one
    transaction at a time) while others are not self-conflicting (for example,
    an <literal>ACCESS SHARE</literal> lock can be held by multiple transactions).
    Once acquired, a lock is held till end of transaction.
   </para>

     <variablelist>
      <title>Table-level lock modes</title>
      <varlistentry>
       <term>
	<literal>ACCESS SHARE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>ACCESS EXCLUSIVE</literal> lock
	 mode only.
	</para>

	<para>
	 The commands <command>SELECT</command> and
	 <command>ANALYZE</command> acquire a lock of this mode on
	 referenced tables.  In general, any query that only reads a table
	 and does not modify it will acquire this lock mode.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
	<literal>ROW SHARE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>EXCLUSIVE</literal> and
	 <literal>ACCESS EXCLUSIVE</literal> lock modes.
	</para>

	<para>
	 The <command>SELECT FOR UPDATE</command> and
	 <command>SELECT FOR SHARE</command> commands acquire a
	 lock of this mode on the target table(s) (in addition to
	 <literal>ACCESS SHARE</literal> locks on any other tables
	 that are referenced but not selected
	 <option>FOR UPDATE/FOR SHARE</option>).
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
	<literal>ROW EXCLUSIVE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>SHARE</literal>, <literal>SHARE ROW
	 EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
	 <literal>ACCESS EXCLUSIVE</literal> lock modes.
	</para>

	<para>
	 The commands <command>UPDATE</command>,
	 <command>DELETE</command>, and <command>INSERT</command>
	 acquire this lock mode on the target table (in addition to
	 <literal>ACCESS SHARE</literal> locks on any other referenced
	 tables).  In general, this lock mode will be acquired by any
	 command that modifies the data in a table.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
	<literal>SHARE UPDATE EXCLUSIVE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>SHARE UPDATE EXCLUSIVE</literal>,
	 <literal>SHARE</literal>, <literal>SHARE ROW
	 EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
	 <literal>ACCESS EXCLUSIVE</literal> lock modes.
	 This mode protects a table against
	 concurrent schema changes and <command>VACUUM</> runs.
	</para>

	<para>
	 Acquired by <command>VACUUM</command> (without <option>FULL</option>).
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
	<literal>SHARE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>ROW EXCLUSIVE</literal>,
	 <literal>SHARE UPDATE EXCLUSIVE</literal>, <literal>SHARE ROW
	 EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
	 <literal>ACCESS EXCLUSIVE</literal> lock modes.
	 This mode protects a table against concurrent data changes.
	</para>

	<para>
	 Acquired by <command>CREATE INDEX</command>.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
	<literal>SHARE ROW EXCLUSIVE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>ROW EXCLUSIVE</literal>,
	 <literal>SHARE UPDATE EXCLUSIVE</literal>,
	 <literal>SHARE</literal>, <literal>SHARE ROW
	 EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
	 <literal>ACCESS EXCLUSIVE</literal> lock modes.
	</para>

	<para>
         This lock mode is not automatically acquired by any
         <productname>PostgreSQL</productname> command.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
	<literal>EXCLUSIVE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with the <literal>ROW SHARE</literal>, <literal>ROW
	 EXCLUSIVE</literal>, <literal>SHARE UPDATE
	 EXCLUSIVE</literal>, <literal>SHARE</literal>, <literal>SHARE
	 ROW EXCLUSIVE</literal>, <literal>EXCLUSIVE</literal>, and
	 <literal>ACCESS EXCLUSIVE</literal> lock modes.
	 This mode allows only concurrent <literal>ACCESS SHARE</literal> locks,
	 i.e., only reads from the table can proceed in parallel with a
	 transaction holding this lock mode.
	</para>

	<para>
         This lock mode is not automatically acquired on user tables by any
         <productname>PostgreSQL</productname> command.  However it is
         acquired on certain system catalogs in some operations.
	</para>
       </listitem>