title: Why does MySQL 8 hold locks for another table, even though transaction is completed and the query is about a different table?
body: <p>I need to get an exclusive lock to the user_info table, however, I can't acquire the lock (it takes forever); I guess because there are locks holding on to the table.</p>
  <p>I run this query to see the locks:</p>
  <pre><code>select lock_type, lock_duration, lock_status, owner_thread_id, owner_event_id from performance_schema.metadata_locks
  where object_name = &quot;user_info&quot;
  </code></pre>
  <p>I see there are three locks from different threads with same event id:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>lock_type</th>
  <th>lock_duration</th>
  <th>lock_status</th>
  <th>owner_thread_id</th>
  <th>owner_event_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>shared_read</td>
  <td>transaction</td>
  <td>granted</td>
  <td>2484557</td>
  <td>60</td>
  </tr>
  <tr>
  <td>shared_read</td>
  <td>transaction</td>
  <td>granted</td>
  <td>2488532</td>
  <td>60</td>
  </tr>
  <tr>
  <td>shared_read</td>
  <td>transaction</td>
  <td>granted</td>
  <td>2485700</td>
  <td>60</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>When I try to acquire a lock, it shows in this table as &quot;EXCLUSIVE&quot; and &quot;PENDING&quot;.</p>
  <p>I check why these locks are not releasing, and try to see which queries cause these locks with this query:</p>
  <pre><code>SELECT current_statement, trx_state, lock_type, lock_status FROM performance_schema.metadata_locks m
    INNER JOIN sys.session r
    ON r.thd_id = m.owner_thread_id
    where m.object_name = &quot;user_info&quot;
    and m.owner_event_id = 60;
  </code></pre>
  <p>I see four rows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>current_statement</th>
  <th>trx_state</th>
  <th>lock_type</th>
  <th>lock_status</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>SELECT created FROM carlin.NOT ...  order by created desc limit 1</td>
  <td>COMMITTED</td>
  <td>SHARED_READ</td>
  <td>GRANTED</td>
  </tr>
  <tr>
  <td>SELECT created FROM carlin.NOT ...  order by created desc limit 1</td>
  <td>COMMITTED</td>
  <td>SHARED_READ</td>
  <td>GRANTED</td>
  </tr>
  <tr>
  <td>SELECT created FROM carlin.NOT ...  order by created desc limit 1</td>
  <td>COMMITTED</td>
  <td>SHARED_READ</td>
  <td>GRANTED</td>
  </tr>
  <tr>
  <td>SELECT created FROM carlin.NOT ...  order by created desc limit 1</td>
  <td>COMMITTED</td>
  <td>SHARED_READ</td>
  <td>GRANTED</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What am I doing wrong here? Why don't these locks release eventually? And their queries seem to be about a completely different table.</p>