<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-locks-set">
      </a>
      17.7.3 Locks Set by Different SQL Statements in InnoDB
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045164036128">
  </a>
  <a class="indexterm" name="idm46045164035088">
  </a>
  <p>
   A
   <a class="link" href="glossary.html#glos_locking_read" title="locking read">
    locking read
   </a>
   , an
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   , or a
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   generally set record locks
      on every index record that is scanned in the processing of an SQL
      statement. It does not matter whether there are
   <code class="literal">
    WHERE
   </code>
   conditions in the statement that would
      exclude the row.
   <code class="literal">
    InnoDB
   </code>
   does not remember the
      exact
   <code class="literal">
    WHERE
   </code>
   condition, but only knows which
      index ranges were scanned. The locks are normally
   <a class="link" href="glossary.html#glos_next_key_lock" title="next-key lock">
    next-key locks
   </a>
   that also
      block inserts into the
   <span class="quote">
    “
    <span class="quote">
     gap
    </span>
    ”
   </span>
   immediately before the
      record. However,
   <a class="link" href="glossary.html#glos_gap_lock" title="gap lock">
    gap locking
   </a>
   can be disabled explicitly, which causes next-key locking not to
      be used. For more information, see
   <a class="xref" href="innodb-locking.html" title="17.7.1 InnoDB Locking">
    Section 17.7.1, “InnoDB Locking”
   </a>
   . The transaction isolation level
      can also affect which locks are set; see
   <a class="xref" href="innodb-transaction-isolation-levels.html" title="17.7.2.1 Transaction Isolation Levels">
    Section 17.7.2.1, “Transaction Isolation Levels”
   </a>
   .
  </p>
  <p>
   If a secondary index is used in a search and the index record
      locks to be set are exclusive,
   <code class="literal">
    InnoDB
   </code>
   also
      retrieves the corresponding clustered index records and sets locks
      on them.
  </p>
  <p>
   If you have no indexes suitable for your statement and MySQL must
      scan the entire table to process the statement, every row of the
      table becomes locked, which in turn blocks all inserts by other
      users to the table. It is important to create good indexes so that
      your queries do not scan more rows than necessary.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   sets specific types of locks as follows.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ...
          FROM
       </code>
      </a>
      is a consistent read, reading a snapshot of the
          database and setting no locks unless the transaction isolation
          level is set to
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_serializable">
       <code class="literal">
        SERIALIZABLE
       </code>
      </a>
      . For
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_serializable">
       <code class="literal">
        SERIALIZABLE
       </code>
      </a>
      level, the
          search sets shared next-key locks on the index records it
          encounters. However, only an index record lock is required for
          statements that lock rows using a unique index to search for a
          unique row.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
          UPDATE
       </code>
      </a>
      and
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
          SHARE
       </code>
      </a>
      statements that use a unique index acquire
          locks for scanned rows, and release the locks for rows that do
          not qualify for inclusion in the result set (for example, if
          they do not meet the criteria given in the
      <code class="literal">
       WHERE
      </code>
      clause). However, in some cases, rows
          might not be unlocked immediately because the relationship
          between a result row and its original source is lost during
          query execution. For example, in a
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      , scanned (and locked)
          rows from a table might be inserted into a temporary table
          before evaluating whether they qualify for the result set. In
          this circumstance, the relationship of the rows in the
          temporary table to the rows in the original table is lost and
          the latter rows are not unlocked until the end of query
          execution.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="glossary.html#glos_locking_read" title="locking read">
       locking reads
      </a>
      (
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      with
      <code class="literal">
       FOR
          UPDATE
      </code>
      or
      <code class="literal">
       FOR SHARE
      </code>
      ),
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements, the locks
          that are taken depend on whether the statement uses a unique
          index with a unique search condition or a range-type search
          condition.
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         For a unique index with a unique search condition,
         <code class="literal">
          InnoDB
         </code>
         locks only the index record
              found, not the
         <a class="link" href="glossary.html#glos_gap" title="gap">
          gap
         </a>
         before
              it.
        </p>
       </li>
       <li class="listitem">
        <p>
         For other search conditions, and for non-unique indexes,
         <code class="literal">
          InnoDB
         </code>
         locks the index range scanned,
              using
         <a class="link" href="glossary.html#glos_gap_lock" title="gap lock">
          gap locks
         </a>
         or
         <a class="link" href="glossary.html#glos_next_key_lock" title="next-key lock">
          next-key locks
         </a>
         to block insertions by other sessions into the gaps
              covered by the range. For information about gap locks and
              next-key locks, see
         <a class="xref" href="innodb-locking.html" title="17.7.1 InnoDB Locking">
          Section 17.7.1, “InnoDB Locking”
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      For index records the search encounters,
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
          UPDATE
       </code>
      </a>
      blocks other sessions from doing
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... FOR
          SHARE
       </code>
      </a>
      or from reading in certain transaction
          isolation levels. Consistent reads ignore any locks set on the
          records that exist in the read view.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE ... WHERE
          ...
       </code>
      </a>
      sets an exclusive next-key lock on every record
          the search encounters. However, only an index record lock is
          required for statements that lock rows using a unique index to
          search for a unique row.
     </p>
    </li>
    <li class="listitem">
     <p>
      When
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      modifies a
          clustered index record, implicit locks are taken on affected
          secondary index records. The
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      operation also takes
          shared locks on affected secondary index records when
          performing duplicate check scans prior to inserting new
          secondary index records, and when inserting new secondary
          index records.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE FROM ... WHERE
          ...
       </code>
      </a>
      sets an exclusive next-key lock on every record
          the search encounters. However, only an index record lock is
          required for statements that lock rows using a unique index to
          search for a unique row.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      sets an exclusive lock
          on the inserted row. This lock is an index-record lock, not a
          next-key lock (that is, there is no gap lock) and does not
          prevent other sessions from inserting into the gap before the
          inserted row.
     </p>
     <p>
      Prior to inserting the row, a type of gap lock called an
          insert intention gap lock is set. This lock signals the intent
          to insert in such a way that multiple transactions inserting
          into the same index gap need not wait for each other if they
          are not inserting at the same position within the gap. Suppose
          that there are index records with values of 4 and 7. Separate
          transactions that attempt to insert values of 5 and 6 each
          lock the gap between 4 and 7 with insert intention locks prior
          to obtaining the exclusive lock on the inserted row, but do
          not block each other because the rows are nonconflicting.
     </p>
     <p>
      If a duplicate-key error occurs, a shared lock on the
          duplicate index record is set. This use of a shared lock can
          result in deadlock should there be multiple sessions trying to
          insert the same row if another session already has an
          exclusive lock. This can occur if another session deletes the
          row. Suppose that an
      <code class="literal">
       InnoDB
      </code>
      table
      <code class="literal">
       t1
      </code>
      has the following structure:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7694736"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>i<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Now suppose that three sessions perform the following
          operations in order:
     </p>
     <p>
      Session 1:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45771180"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Session 2:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52191686"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Session 3:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10270023"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Session 1:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa4594321"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ROLLBACK</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The first operation by session 1 acquires an exclusive lock
          for the row. The operations by sessions 2 and 3 both result in
          a duplicate-key error and they both request a shared lock for
          the row. When session 1 rolls back, it releases its exclusive
          lock on the row and the queued shared lock requests for
          sessions 2 and 3 are granted. At this point, sessions 2 and 3
          deadlock: Neither can acquire an exclusive lock for the row
          because of the shared lock held by the other.
     </p>
     <p>
      A similar situation occurs if the table already contains a row
          with key value 1 and three sessions perform the following
          operations in order:
     </p>
     <p>
      Session 1:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6447185"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> i <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Session 2:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46032661"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Session 3:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18725816"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Session 1:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75379725"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">COMMIT</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The first operation by session 1 acquires an exclusive lock
          for the row. The operations by sessions 2 and 3 both result in
          a duplicate-key error and they both request a shared lock for
          the row. When session 1 commits, it releases its exclusive
          lock on the row and the queued shared lock requests for
          sessions 2 and 3 are granted. At this point, sessions 2 and 3
          deadlock: Neither can acquire an exclusive lock for the row
          because of the shared lock held by the other.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
       <code class="literal">
        INSERT
          ... ON DUPLICATE KEY UPDATE
       </code>
      </a>
      differs from a simple
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      in that an exclusive
          lock rather than a shared lock is placed on the row to be
          updated when a duplicate-key error occurs. An exclusive
          index-record lock is taken for a duplicate primary key value.
          An exclusive next-key lock is taken for a duplicate unique key
          value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      is done like an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      if there is no collision
          on a unique key. Otherwise, an exclusive next-key lock is
          placed on the row to be replaced.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INSERT INTO T SELECT ... FROM S WHERE ...
      </code>
      sets an exclusive index record lock (without a gap lock) on
          each row inserted into
      <code class="literal">
       T
      </code>
      . If the
          transaction isolation level is
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ
          COMMITTED
       </code>
      </a>
      ,
      <code class="literal">
       InnoDB
      </code>
      does the search
          on
      <code class="literal">
       S
      </code>
      as a consistent read (no locks).
          Otherwise,
      <code class="literal">
       InnoDB
      </code>
      sets shared next-key
          locks on rows from
      <code class="literal">
       S
      </code>
      .
      <code class="literal">
       InnoDB
      </code>
      has to set locks in the latter case:
          During roll-forward recovery using a statement-based binary
          log, every SQL statement must be executed in exactly the same
          way it was done originally.
     </p>
     <p>
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE ...
          SELECT ...
       </code>
      </a>
      performs the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      with shared next-key
          locks or as a consistent read, as for
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT ...
          SELECT
       </code>
      </a>
      .
     </p>
     <p>
      When a
      <code class="literal">
       SELECT
      </code>
      is used in the constructs
      <code class="literal">
       REPLACE INTO t SELECT ... FROM s WHERE ...
      </code>
      or
      <code class="literal">
       UPDATE t ... WHERE col IN (SELECT ... FROM s
          ...)
      </code>
      ,
      <code class="literal">
       InnoDB
      </code>
      sets shared next-key
          locks on rows from table
      <code class="literal">
       s
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       InnoDB
      </code>
      sets an exclusive lock on the end of
          the index associated with the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column while initializing a
          previously specified
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column
          on a table.
     </p>
     <p>
      With
      <a class="link" href="innodb-parameters.html#sysvar_innodb_autoinc_lock_mode">
       <code class="literal">
        innodb_autoinc_lock_mode=0
       </code>
      </a>
      ,
      <code class="literal">
       InnoDB
      </code>
      uses a special
      <code class="literal">
       AUTO-INC
      </code>
      table lock mode where the lock is
          obtained and held to the end of the current SQL statement (not
          to the end of the entire transaction) while accessing the
          auto-increment counter. Other clients cannot insert into the
          table while the
      <code class="literal">
       AUTO-INC
      </code>
      table lock is
          held. The same behavior occurs for
      <span class="quote">
       “
       <span class="quote">
        bulk inserts
       </span>
       ”
      </span>
      with
      <a class="link" href="innodb-parameters.html#sysvar_innodb_autoinc_lock_mode">
       <code class="literal">
        innodb_autoinc_lock_mode=1
       </code>
      </a>
      .
          Table-level
      <code class="literal">
       AUTO-INC
      </code>
      locks are not used
          with
      <a class="link" href="innodb-parameters.html#sysvar_innodb_autoinc_lock_mode">
       <code class="literal">
        innodb_autoinc_lock_mode=2
       </code>
      </a>
      .
          For more information, See
      <a class="xref" href="innodb-auto-increment-handling.html" title="17.6.1.6 AUTO_INCREMENT Handling in InnoDB">
       Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”
      </a>
      .
     </p>
     <p>
      <code class="literal">
       InnoDB
      </code>
      fetches the value of a previously
          initialized
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column without
          setting any locks.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a
      <code class="literal">
       FOREIGN KEY
      </code>
      constraint is defined on a
          table, any insert, update, or delete that requires the
          constraint condition to be checked sets shared record-level
          locks on the records that it looks at to check the constraint.
      <code class="literal">
       InnoDB
      </code>
      also sets these locks in the case
          where the constraint fails.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      sets table locks,
          but it is the higher MySQL layer above the
      <code class="literal">
       InnoDB
      </code>
      layer that sets these locks.
      <code class="literal">
       InnoDB
      </code>
      is aware of table locks if
      <code class="literal">
       innodb_table_locks = 1
      </code>
      (the default) and
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit = 0
       </code>
      </a>
      , and the MySQL
          layer above
      <code class="literal">
       InnoDB
      </code>
      knows about row-level
          locks.
     </p>
     <p>
      Otherwise,
      <code class="literal">
       InnoDB
      </code>
      's automatic deadlock
          detection cannot detect deadlocks where such table locks are
          involved. Also, because in this case the higher MySQL layer
          does not know about row-level locks, it is possible to get a
          table lock on a table where another session currently has
          row-level locks. However, this does not endanger transaction
          integrity, as discussed in
      <a class="xref" href="innodb-deadlock-detection.html" title="17.7.5.2 Deadlock Detection">
       Section 17.7.5.2, “Deadlock Detection”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      acquires two locks
          on each table if
      <code class="literal">
       innodb_table_locks=1
      </code>
      (the
          default). In addition to a table lock on the MySQL layer, it
          also acquires an
      <code class="literal">
       InnoDB
      </code>
      table lock. To
          avoid acquiring
      <code class="literal">
       InnoDB
      </code>
      table locks, set
      <code class="literal">
       innodb_table_locks=0
      </code>
      . If no
      <code class="literal">
       InnoDB
      </code>
      table lock is acquired,
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      completes even if
          some records of the tables are being locked by other
          transactions.
     </p>
     <p>
      In MySQL 8.4,
      <a class="link" href="innodb-parameters.html#sysvar_innodb_table_locks">
       <code class="literal">
        innodb_table_locks=0
       </code>
      </a>
      has no
          effect for tables locked explicitly with
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES ...
          WRITE
       </code>
      </a>
      . It does have an effect for tables locked for
          read or write by
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES ...
          WRITE
       </code>
      </a>
      implicitly (for example, through triggers) or
          by
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
          ... READ
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      All
      <code class="literal">
       InnoDB
      </code>
      locks held by a transaction are
          released when the transaction is committed or aborted. Thus,
          it does not make much sense to invoke
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      on
      <code class="literal">
       InnoDB
      </code>
      tables in
      <a class="link" href="server-system-variables.html#sysvar_autocommit">
       <code class="literal">
        autocommit=1
       </code>
      </a>
      mode because the
          acquired
      <code class="literal">
       InnoDB
      </code>
      table locks would be
          released immediately.
     </p>
    </li>
    <li class="listitem">
     <p>
      You cannot lock additional tables in the middle of a
          transaction because
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        LOCK TABLES
       </code>
      </a>
      performs an implicit
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      and
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        UNLOCK
          TABLES
       </code>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
