title: Update WHERE (SELECT COUNT(*)) atomicity and race conditions. Suggestions?
body: <p>I have a table for booking system and I want to apply a certain constraint that should be atomic. Simply, I just want to conditionally insert a row into that table. I don't want to read-prepare-write because it will cause race conditions. I decided to insert an initial row then update it with a sub-query condition and check affected rows count.
  <code>affectedRowsCount</code> will always be 1 on concurrent requests which indicates a race condition. I know that isolation level of Serializable and lock mechanisms will help but I want to discuss other less strict ways</p>
  <p>Pseudo Code</p>
  <pre><code>Start transaction
  Insert single row at table Reservations (Lets call Row)
  affectedRowsCount = Update Reservations where ID = &quot;Row&quot;.id AND (SELECT COUNT(*) FROM &quot;Reservation&quot; WHERE ...) &lt; some integer
  if (affectedRowsCount === 0) throw Already Reserved Error
  Commit transaction
  </code></pre>