Home • Explore • Blog • CI
☰



postgresql
16th chunk of `doc/src/sgml/mvcc.sgml`
92e40b52d2838306dee9cf574e35e0f24f8ed7dbb6b333140000000100000fae
 <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> cancels all effects of the
    commands since the savepoint.  The same holds for locks acquired within a
    <application>PL/pgSQL</application> exception block: an error escape from the block
    releases locks acquired within it.
   </para>



    <table tocentry="1" id="table-lock-compatibility">
     <title>Conflicting Lock Modes</title>
     <tgroup cols="9">
      <colspec colnum="1" colwidth="1.25*"/>
      <colspec colnum="2" colwidth="1*" colname="lockst"/>
      <colspec colnum="3" colwidth="1*"/>
      <colspec colnum="4" colwidth="1*"/>
      <colspec colnum="5" colwidth="1*"/>
      <colspec colnum="6" colwidth="1*"/>
      <colspec colnum="7" colwidth="1*"/>
      <colspec colnum="8" colwidth="1*"/>
      <colspec colnum="9" colwidth="1*" colname="lockend"/>
      <spanspec spanname="lockreq" namest="lockst" nameend="lockend" align="center"/>
      <thead>
       <row>
        <entry morerows="1">Requested Lock Mode</entry>
        <entry spanname="lockreq">Existing Lock Mode</entry>
       </row>
       <row>
        <entry><literal>ACCESS SHARE</literal></entry>
        <entry><literal>ROW SHARE</literal></entry>
        <entry><literal>ROW EXCL.</literal></entry>
        <entry><literal>SHARE UPDATE EXCL.</literal></entry>
        <entry><literal>SHARE</literal></entry>
        <entry><literal>SHARE ROW EXCL.</literal></entry>
        <entry><literal>EXCL.</literal></entry>
        <entry><literal>ACCESS EXCL.</literal></entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><literal>ACCESS SHARE</literal></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center">X</entry>
       </row>
       <row>
        <entry><literal>ROW SHARE</literal></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center">X</entry>
        <entry align="center">X</entry>
       </row>
       <row>
        <entry><literal>ROW EXCL.</literal></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center">X</entry>
        <entry align="center">X</entry>
        <entry align="center">X</entry>
        <entry align="center">X</entry>
       </row>
       <row>
        <entry><literal>SHARE UPDATE EXCL.</literal></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center"></entry>
        <entry align="center">X</entry>
        <entry align="center">X</entry>
        <entry align="center">X</entry>
Title: Lock Mode Compatibility in PostgreSQL
Summary
This section describes the compatibility of different lock modes in PostgreSQL, including ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE, with a table summarizing the conflicting lock modes, and explains how locks are held until the end of a transaction unless rolled back to a savepoint or released due to an error in a PL/pgSQL exception block.