title: When to use SELECT ... FOR UPDATE?
body: <p>Please help me understand the use-case behind <code>SELECT ... FOR UPDATE</code>.</p>
  <p><strong>Question 1</strong>: Is the following a good example of when <code>SELECT ... FOR UPDATE</code> should be used?</p>
  <p>Given:</p>
  <ul>
  <li>rooms[id]</li>
  <li>tags[id, name]</li>
  <li>room_tags[room_id, tag_id]
  <ul>
  <li>room_id and tag_id are foreign keys</li>
  </ul>
  </li>
  </ul>
  <p>The application wants to list all rooms and their tags, but needs to differentiate between rooms with no tags versus rooms that have been removed. If SELECT ... FOR UPDATE is not used, what could happen is:</p>
  <ul>
  <li>Initially:
  <ul>
  <li>rooms contains <code>[id = 1]</code></li>
  <li>tags contains <code>[id = 1, name = 'cats']</code></li>
  <li>room_tags contains <code>[room_id = 1, tag_id = 1]</code></li>
  </ul>
  </li>
  <li>Thread 1: <code>SELECT id FROM rooms;</code>
  <ul>
  <li><code>returns [id = 1]</code></li>
  </ul>
  </li>
  <li>Thread 2: <code>DELETE FROM room_tags WHERE room_id = 1;</code></li>
  <li>Thread 2: <code>DELETE FROM rooms WHERE id = 1;</code></li>
  <li>Thread 2: [commits the transaction]</li>
  <li>Thread 1: <code>SELECT tags.name FROM room_tags, tags WHERE room_tags.room_id = 1 AND tags.id = room_tags.tag_id;</code>
  <ul>
  <li>returns an empty list</li>
  </ul>
  </li>
  </ul>
  <p>Now Thread 1 thinks that room 1 has no tags, but in reality the room has been removed. To solve this problem, Thread 1 should <code>SELECT id FROM rooms FOR UPDATE</code>, thereby preventing Thread 2 from deleting from <code>rooms</code> until Thread 1 is done. Is that correct?</p>
  <p><strong>Question 2</strong>: When should one use <code>SERIALIZABLE</code> transaction isolation versus <code>READ_COMMITTED</code> with <code>SELECT ... FOR UPDATE</code>?</p>
  <p>Answers are expected to be portable (not database-specific). If that's not possible, please explain why.</p>