title: Lock multiple rows using CTE and FOR UPDATE
body: <p>In one session, I trying to lock multiple rows in "users" table, and get "status" column for user <code>WHERE id = 2</code>.</p>
  
  <pre><code>do $$
  declare user_status int;
  begin
      WITH t(id, status) AS(
          SELECT id, status FROM users WHERE id in( 2,4,7,6) order by id FOR UPDATE
      )
      SELECT status FROM t WHERE id = 2 INTO user_status;
  
      -- just run transaction for a while
      FOR i in 1..2000000000 loop
      end loop;
  
  end;
  $$ language plpgsql
  </code></pre>
  
  <p>while this transaction is running, I run in another session this query:</p>
  
  <pre><code>UPDATE users SET some_col = some_col WHERE id = 6;
  </code></pre>
  
  <p>I expected that row <code>WHERE id = 6</code> should locked by first transaction, but this is not true, because <code>UPDATE</code> in  second session runs immediately (not waiting for ending transaction in first session).</p>
  
  <p>What I'm misunderstood ?</p>
  
  <p>P.S.</p>
  
  <p>If in first transaction, instead of CTE use: </p>
  
  <pre><code>perform id, status FROM users WHERE id in( 2,4,7,6) order by id FOR UPDATE;
  </code></pre>
  
  <p>Then lock works as expected.</p>