title: MySQL strange behavior
body: <p><strong>DB Config</strong></p>
  <ul>
  <li><p>DB version: 8.0.23</p>
  </li>
  <li><p>Isolation Level: REPEATABLE READ</p>
  </li>
  </ul>
  <p>I found a strange behavior in the locking mechanism and I'm trying to understand the difference between 2 flows.</p>
  <p><em><strong>Flow 1:</strong></em></p>
  <p>Imagine you have 2 connections that operates in parallel:</p>
  <p>Connection1:</p>
  <pre><code>START TRANSACTION;
  INSERT INTO my_table (id,name) VALUES (101,'test');
  </code></pre>
  <p>Connection2:</p>
  <pre><code>START TRANSACTION;
  SET @id = (SELECT id FROM my_table where id = 101);
  </code></pre>
  <p><strong>NOTE:</strong></p>
  <ul>
  <li>In this flow (Flow 1) connection1 locks the row that have id=101 and connection2 will wait until connection1 will close the transaction.</li>
  </ul>
  <p><em><strong>Flow 2:</strong></em></p>
  <p>Imagine you have 2 connections that operates in parallel:</p>
  <p>Connection1:</p>
  <pre><code>START TRANSACTION;
  INSERT INTO my_table (id,name) VALUES (101,'test');
  </code></pre>
  <p>Connection2:</p>
  <pre><code>START TRANSACTION;
  SET @id = NULL;
  SELECT id INTO @id from my_table where id = 101;
  </code></pre>
  <p><strong>NOTE:</strong></p>
  <ul>
  <li>In this flow (Flow 2) connection1 locks the row that have id=101 and connection2 will <strong>NOT</strong> wait for connection1 to close the transaction (We will just get NULL because of the isolation level).</li>
  </ul>
  <p>So I'm trying to understand what is the difference between these 2 flows
  Why connection2 wait when using &quot;SET&quot; but doesn't wait when using &quot;SELECT ... INTO ...&quot;</p>
  <p>FYI - For some reason it seems that using &quot;SET&quot; this way also locks the table (Flow1)</p>
  <p>Thanks in advance!!</p>