Home • Explore • Blog • CI
☰



postgresql
15th chunk of `doc/src/sgml/mvcc.sgml`
5714a9dd78ad95f5483cdb7e716270ffe1c3115088f9f4840000000100000fbb
 <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>
         (without <option>CONCURRENTLY</option>).
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
        <literal>SHARE ROW EXCLUSIVE</literal> (<literal>ShareRowExclusiveLock</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.
         This mode protects a table against concurrent data changes, and
         is self-exclusive so that only one session can hold it at a time.
        </para>

        <para>
         Acquired by <command>CREATE TRIGGER</command> and some forms of
         <link linkend="sql-altertable"><command>ALTER TABLE</command></link>.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
        <literal>EXCLUSIVE</literal> (<literal>ExclusiveLock</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>
         Acquired by <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command>.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
        <literal>ACCESS EXCLUSIVE</literal> (<literal>AccessExclusiveLock</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>DROP TABLE</command>,
         <command>TRUNCATE</command>, <command>REINDEX</command>,
         <command>CLUSTER</command>, <command>VACUUM FULL</command>,
         and <command>REFRESH MATERIALIZED VIEW</command> (without
         <option>CONCURRENTLY</option>)
         commands. Many forms of <command>ALTER INDEX</command> and <command>ALTER TABLE</command> also acquire
         a lock at this level. 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>

   <para>
    Once acquired, a lock is normally held until the end of the transaction.  But if a
    lock is acquired after establishing a savepoint, the lock is released
    immediately if the savepoint is rolled back to.  This is consistent with
    the principle that <command>ROLLBACK</command>
Title: Lock Modes in PostgreSQL
Summary
PostgreSQL has several lock modes, including SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE, each with its own set of conflicting lock modes and usage scenarios, such as protecting against concurrent data changes, allowing only reads, or guaranteeing exclusive access, and are acquired by various commands like CREATE INDEX, ALTER TABLE, REFRESH MATERIALIZED VIEW, and LOCK TABLE, with locks normally held until the end of a transaction unless rolled back to a savepoint.