title: Adding to or retrieving from an SQL table of unique keys and values
body: <p><code>names</code> is a table that maps every unique name to an id:</p>
  
  <pre><code>CREATE TABLE names (
    name       VARCHAR(20)    NOT NULL,
    name_id    INT            NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (name),
    UNIQUE (name_id)
  ) ENGINE=InnoDB
  </code></pre>
  
  <p>If 'foobar' is already stored in <code>names</code>, I want to get its <code>name_id</code>. Otherwise I want to store it and get the <code>name_id</code> that was generated for it. </p>
  
  <p>How does one do this, dealing with the possibility of two threads racing to add 'foobar'? (Note: we never delete from this table.)</p>
  
  <p>With myISAM I wrote:</p>
  
  <pre><code>GET_LOCK('foobar');
  SELECT name_id FROM names WHERE name = 'foobar';
  if name wasn't found
      INSERT INTO names SET name='foobar';
      SELECT LAST_INSERT_ID(); -- that is, name_id
  RELEASE_LOCK('foobar');
  </code></pre>
  
  <p>However, I'm very unclear about how to achieve this using transactions and row-level locking in innoDB.</p>
  
  <p><strong>UPDATE:</strong> mySQL requires that AUTO_INCREMENT only be used for the PK. I'm using MariaDB, which only requires that the AUTO_INCREMENT column be defined as key. Updating example accordingly.</p>