title: MySQL InnoDB locking only the affected rows?
body: <p>I have an InnoDB table containing users, like this:</p>
  
  <pre><code>+--------------+-----------------------+
  | user_id      | name                  |
  +--------------+-----------------------+
  | 1            | Bob                   |
  | 1            | Marry                 |
  | 2            | Bob                   |
  | 1            | John                  |
  | 3            | Bob                   |
  | 2            | Marry                 |
  +--------------+-----------------------+
  </code></pre>
  
  <p>On each insert I increment user_id by 1 so for example, the next user_id for Bob will be 4</p>
  
  <p>I use this query to do that:</p>
  
  <pre><code>INSERT INTO users (user_id, name)
  SELECT 1 + coalesce((SELECT max(user_id) FROM users WHERE name='Bob'), 0), 'Bob';
  </code></pre>
  
  <p>Now I need to make sure that two different users don't add a 'Bob' at the same time. I don't want any two Bobs with user_id 4. Each of Bob's user ids must be different.</p>
  
  <p>Is is possible to write and update lock all of Bob's rows when the above insert query is run? I cannot lock the whole table because many other users still need full access to their rows. Also, all rows should be readable at all times. How would I do this?</p>