Home • Explore • Blog • CI
☰



postgresql
14th chunk of `doc/src/sgml/mvcc.sgml`
56a6f0e38b9b3aac1d5b43f9ee49aa277b56a3f9789dfb120000000100000fa9
 SHARE</literal> (<literal>RowShareLock</literal>)
       </term>
       <listitem>
        <para>
         Conflicts with the <literal>EXCLUSIVE</literal> and
         <literal>ACCESS EXCLUSIVE</literal> lock modes.
        </para>

        <para>
         The <command>SELECT</command> command acquires a lock of this mode
         on all tables on which one of the <option>FOR UPDATE</option>,
         <option>FOR NO KEY UPDATE</option>,
         <option>FOR SHARE</option>, or
         <option>FOR KEY SHARE</option> options is specified
         (in addition to <literal>ACCESS SHARE</literal> locks on any other
         tables that are referenced without any explicit
         <option>FOR ...</option> locking option).
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
        <literal>ROW EXCLUSIVE</literal> (<literal>RowExclusiveLock</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>, <command>INSERT</command>, and
         <command>MERGE</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 <emphasis>modifies data</emphasis> in a table.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
        <literal>SHARE UPDATE EXCLUSIVE</literal> (<literal>ShareUpdateExclusiveLock</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</command> runs.
        </para>

        <para>
         Acquired by <command>VACUUM</command> (without <option>FULL</option>),
         <command>ANALYZE</command>, <command>CREATE INDEX CONCURRENTLY</command>,
         <command>CREATE STATISTICS</command>, <command>COMMENT ON</command>,
         <command>REINDEX CONCURRENTLY</command>,
         and certain <link linkend="sql-alterindex"><command>ALTER INDEX</command></link>
         and <link linkend="sql-altertable"><command>ALTER TABLE</command></link> variants
         (for full details see the documentation of these commands).
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>
        <literal>SHARE</literal> (<literal>ShareLock</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>
         (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
Title: Table-Level Lock Modes in PostgreSQL
Summary
PostgreSQL provides several table-level lock modes, including SHARE, SHARE ROW EXCLUSIVE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, and others, each with its own set of conflicting lock modes and usage scenarios, such as protecting against concurrent data changes, schema changes, or VACUUM runs, and are acquired by various commands like SELECT, UPDATE, CREATE INDEX, and VACUUM, to manage concurrent access to data and ensure data integrity.