mvcc.sgml

PostgreSQL7.4.6 for Linux

   <para>
    The level <firstterm>Serializable</firstterm> provides the strictest transaction
    isolation.  This level emulates serial transaction execution,
    as if transactions had been executed one after another, serially,
    rather than concurrently.  However, applications using this level must
    be prepared to retry transactions due to serialization failures.
   </para>

   <para>
    When a transaction is on the serializable level,
    a <command>SELECT</command> query sees only data committed before the
    transaction began; it never sees either uncommitted data or changes
    committed
    during transaction execution by concurrent transactions.  (However, the
    <command>SELECT</command> does see the effects of previous updates
    executed within its own transaction, even though they are not yet
    committed.)  This is different from Read Committed in that the
    <command>SELECT</command>
    sees a snapshot as of the start of the transaction, not as of the start
    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>, and <command>SELECT
    FOR UPDATE</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 marked for update) 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 selected it for update)
    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 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>
  </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>.
    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 --- 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> command acquires 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</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 by any
         <productname>PostgreSQL</productname> command.
	</para>
       </listitem>
      </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>