mvcc.sgml

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

      </varlistentry>

      <varlistentry>
       <term>
	<literal>ACCESS EXCLUSIVE</literal>
       </term>
       <listitem>
	<para>
	 Conflicts with locks of all modes (<literal>ACCESS
	 SHARE</literal>, <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>).
	 This mode guarantees that the
	 holder is the only transaction accessing the table in any way.
	</para>

	<para>
	 Acquired by the <command>ALTER TABLE</command>, <command>DROP
	 TABLE</command>, <command>REINDEX</command>,
	 <command>CLUSTER</command>, and <command>VACUUM FULL</command>
	 commands.  This is also the default lock mode for <command>LOCK
	 TABLE</command> statements that do not specify a mode explicitly.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>

     <tip>
      <para>
       Only an <literal>ACCESS EXCLUSIVE</literal> lock blocks a
       <command>SELECT</command> (without <option>FOR UPDATE/SHARE</option>)
       statement.
      </para>
     </tip>

   </sect2>

   <sect2 id="locking-rows">
    <title>Row-Level Locks</title>

    <para>
     In addition to table-level locks, there are row-level locks, which
     can be exclusive or shared locks.  An exclusive row-level lock on a
     specific row is automatically acquired when the row is updated or
     deleted.  The lock is held until the transaction commits or rolls
     back.  Row-level locks do not affect data querying; they block
     <emphasis>writers to the same row</emphasis> only.
    </para>

    <para>
     To acquire an exclusive row-level lock on a row without actually
     modifying the row, select the row with <command>SELECT FOR
     UPDATE</command>.  Note that once the row-level lock is acquired,
     the transaction may update the row multiple times without
     fear of conflicts.
    </para>

    <para>
     To acquire a shared row-level lock on a row, select the row with
     <command>SELECT FOR SHARE</command>.  A shared lock does not prevent
     other transactions from acquiring the same shared lock.  However,
     no transaction is allowed to update, delete, or exclusively lock a
     row on which any other transaction holds a shared lock.  Any attempt
     to do so will block until the shared locks have been released.
    </para>

    <para>
     <productname>PostgreSQL</productname> doesn't remember any
     information about modified rows in memory, so it has no limit to
     the number of rows locked at one time.  However, locking a row
     may cause a disk write; thus, for example, <command>SELECT FOR
     UPDATE</command> will modify selected rows to mark them locked, and so
     will result in disk writes.
    </para>

    <para>
     In addition to table and row locks, page-level share/exclusive locks are
     used to control read/write access to table pages in the shared buffer
     pool.  These locks are released immediately after a row is fetched or
     updated.  Application developers normally need not be concerned with
     page-level locks, but we mention them for completeness.
    </para>

   </sect2>

   <sect2 id="locking-deadlocks">
    <title>Deadlocks</title>

    <indexterm zone="locking-deadlocks">
     <primary>deadlock</primary>
    </indexterm>

    <para>
     The use of explicit locking can increase the likelihood of
     <firstterm>deadlocks</>, wherein two (or more) transactions each
     hold locks that the other wants.  For example, if transaction 1
     acquires an exclusive lock on table A and then tries to acquire
     an exclusive lock on table B, while transaction 2 has already
     exclusive-locked table B and now wants an exclusive lock on table
     A, then neither one can proceed.
     <productname>PostgreSQL</productname> automatically detects
     deadlock situations and resolves them by aborting one of the
     transactions involved, allowing the other(s) to complete.
     (Exactly which transaction will be aborted is difficult to
     predict and should not be relied on.)
    </para>

    <para>
     Note that deadlocks can also occur as the result of row-level
     locks (and thus, they can occur even if explicit locking is not
     used). Consider the case in which there are two concurrent
     transactions modifying a table. The first transaction executes:

<screen>
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
</screen>

     This acquires a row-level lock on the row with the specified
     account number. Then, the second transaction executes:

<screen>
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
</screen>

     The first <command>UPDATE</command> statement successfully
     acquires a row-level lock on the specified row, so it succeeds in
     updating that row. However, the second <command>UPDATE</command>
     statement finds that the row it is attempting to update has
     already been locked, so it waits for the transaction that
     acquired the lock to complete. Transaction two is now waiting on
     transaction one to complete before it continues execution. Now,
     transaction one executes:

<screen>
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
</screen>

     Transaction one attempts to acquire a row-level lock on the
     specified row, but it cannot: transaction two already holds such
     a lock. So it waits for transaction two to complete. Thus,
     transaction one is blocked on transaction two, and transaction
     two is blocked on transaction one: a deadlock
     condition. <productname>PostgreSQL</productname> will detect this
     situation and abort one of the transactions.
    </para>

    <para>
     The best defense against deadlocks is generally to avoid them by
     being certain that all applications using a database acquire
     locks on multiple objects in a consistent order. In the example
     above, if both transactions
     had updated the rows in the same order, no deadlock would have
     occurred. One should also ensure that the first lock acquired on
     an object in a transaction is the highest mode that will be
     needed for that object.  If it is not feasible to verify this in
     advance, then deadlocks may be handled on-the-fly by retrying
     transactions that are aborted due to deadlock.
    </para>

    <para>
     So long as no deadlock situation is detected, a transaction seeking
     either a table-level or row-level lock will wait indefinitely for
     conflicting locks to be released.  This means it is a bad idea for
     applications to hold transactions open for long periods of time
     (e.g., while waiting for user input).
    </para>
   </sect2>
  </sect1>

  <sect1 id="applevel-consistency">
   <title>Data Consistency Checks at the Application Level</title>

   <para>
    Because readers in <productname>PostgreSQL</productname>
    do not lock data, regardless of
    transaction isolation level, data read by one transaction can be
    overwritten by another concurrent transaction. In other words,
    if a row is returned by <command>SELECT</command> it doesn't mean that
    the row is still current at the instant it is returned (i.e., sometime
    after the current query began).  The row might have been modified or
    deleted by an already-committed transaction that committed after this one
    started.
    Even if the row is still valid <quote>now</quote>, it could be changed or
    deleted
    before the current transaction does a commit or rollback.
   </para>

   <para>
    Another way to think about it is that each
    transaction sees a snapshot of the database contents, and concurrently
    executing transactions may very well see different snapshots.  So the
    whole concept of <quote>now</quote> is somewhat ill-defined anyway.
    This is not normally
    a big problem if the client applications are isolated from each other,
    but if the clients can communicate via channels outside the database
    then serious confusion may ensue.
   </para>

   <para>
    To ensure the current validity of a row and protect it against
    concurrent updates one must use <command>SELECT FOR UPDATE</command>,
    <command>SELECT FOR SHARE</command>, or an appropriate <command>LOCK
    TABLE</command> statement.  (<command>SELECT FOR UPDATE</command>
    or <command>SELECT FOR SHARE</command> locks just the
    returned rows against concurrent updates, while <command>LOCK
    TABLE</command> locks the whole table.)  This should be taken into
    account when porting applications to
    <productname>PostgreSQL</productname> from other environments.
    (Before version 6.5 <productname>PostgreSQL</productname> used
    read locks, and so this above consideration is also relevant when
    upgrading from <productname>PostgreSQL</productname> versions
    prior to 6.5.)
   </para>

   <para>
    Global validity checks require extra thought under <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 under
    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 serializable transaction will give an accurate picture of the
    effects of transactions that committed before the serializable transaction
    started &mdash; but one might legitimately wonder whether the answer is still
    relevant by the time it is delivered.  If the serializable 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</> 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 use
    Read Committed mode, or in Serializable mode be careful to obtain the
    lock(s) before performing queries.  A lock obtained by a
    serializable 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 may predate some now-committed
    changes in the table.  A serializable transaction's snapshot is actually
    frozen at the start of its first query or data-modification command
    (<literal>SELECT</>, <literal>INSERT</>,
    <literal>UPDATE</>, or <literal>DELETE</>), so
    it's possible to obtain locks explicitly before the snapshot is
    frozen.
   </para>
  </sect1>

  <sect1 id="locking-indexes">
   <title>Locking and Indexes</title>

   <indexterm zone="locking-indexes">
    <primary>index</primary>
    <secondary>locks</secondary>
   </indexterm>

   <para>
    Though <productname>PostgreSQL</productname>
    provides nonblocking read/write access to table
    data, nonblocking read/write access is not currently offered for every
    index access method implemented
    in <productname>PostgreSQL</productname>.
    The various index types are handled as follows:

    <variablelist>
     <varlistentry>
      <term>
       B-tree and <acronym>GiST</acronym> indexes
      </term>
      <listitem>
       <para>
        Short-term share/exclusive page-level locks are used for
        read/write access. Locks are released immediately after each
        index row is fetched or inserted.  These index types provide
        the highest concurrency without deadlock conditions.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       Hash indexes
      </term>
      <listitem>
       <para>
        Share/exclusive hash-bucket-level locks are used for read/write
        access.  Locks are released after the whole bucket is processed.
        Bucket-level locks provide better concurrency than index-level
        ones, but deadlock is possible since the locks are held longer
        than one index operation.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       R-tree indexes
      </term>
      <listitem>
       <para>
        Share/exclusive index-level locks are used for read/write access.
        Locks are released after the entire command is done.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    Currently, B-tree indexes offer the best performance for concurrent
    applications; since they also have more features than hash
    indexes, they are the recommended index type for concurrent
    applications that need to index scalar data. When dealing with
    non-scalar data, B-trees are not useful, and GiST indexes should
    be used instead.  R-tree indexes are deprecated and are likely
    to disappear entirely in a future release.
   </para>
  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->