title: Postgres 9.3: Sharelock issue with simple INSERT
body: <p><strong><em>Update: Potential solution below</em></strong></p>
  
  <p>I have a large corpus of configuration files consisting of key/value pairs that I'm trying to push into a database.  A lot of the keys and values are repeated across configuration files so I'm storing the data using 3 tables. One for all unique key values, one for all unique pair values, and one listing all the key/value pairs for each file.</p>
  
  <p><strong>Problem:</strong>
  I'm using multiple concurrent processes (and therefore connections) to add the raw data into the database. Unfortunately I get a lot of detected deadlocks when trying to add values to the key and value tables.  I have a tried a few different methods of inserting the data (shown below), but always end up with a "deadlock detected" error</p>
  
  <blockquote>
    <p>TransactionRollbackError: deadlock detected <br>DETAIL:  Process 26755
    waits for ShareLock on transaction 689456; blocked by process 26754.
    Process 26754 waits for ShareLock on transaction 689467; blocked by
    process 26755.</p>
  </blockquote>
  
  <p>I was wondering if someone could shed some light on exactly what could be causing these deadlocks, and possibly point me towards some way of fixing the issue.  Looking at the SQL statements I'm using (listed below), I don't really see why there is any co-dependency at all.  <br><br>Thanks for reading!</p>
  
  <p><strong>Example config file:</strong></p>
  
  <pre><code>example_key this_is_the_value
  other_example other_value
  third example yet_another_value
  </code></pre>
  
  <p><strong>Table definitions:</strong></p>
  
  <pre><code>    CREATE TABLE keys (
          id SERIAL PRIMARY KEY,
          hash UUID UNIQUE NOT NULL,
          key TEXT);
  
      CREATE TABLE values (
          id SERIAL PRIMARY KEY,
          hash UUID UNIQUE NOT NULL,
          key TEXT);
  
      CREATE TABLE keyvalue_pairs (
          id SERIAL PRIMARY KEY,
          file_id INTEGER REFERENCES filenames,
          key_id INTEGER REFERENCES keys,
          value_id INTEGER REFERENCES values);
  </code></pre>
  
  <p><strong>SQL Statements:</strong></p>
  
  <p>Initially I was trying to use this statement to avoid any exceptions:</p>
  
  <pre><code>    WITH s AS (
          SELECT id, hash, key FROM keys
              WHERE hash = 'hash_value';
      ), i AS (
          INSERT INTO keys (hash, key)
          SELECT 'hash_value', 'key_value'
          WHERE NOT EXISTS (SELECT 1 FROM s)
          returning id, hash, key
      )
      SELECT id, hash, key FROM i
      UNION ALL
      SELECT id, hash, key FROM s;
  </code></pre>
  
  <p>But even something as simple as this causes the deadlocks:</p>
  
  <pre><code>    INSERT INTO keys (hash, key)
          VALUES ('hash_value', 'key_value')
          RETURNING id;
  </code></pre>
  
  <ul>
  <li>In both cases, if I get an exception thrown because the inserted hash
  value is not unique, I use savepoints to rollback the change and
  another statement to just select the id I'm after.</li>
  <li>I'm using hashes for the unique field, as some of the keys and values
  are too long to be indexed</li>
  </ul>
  
  <p><strong>Full example of the python code (using psycopg2) with savepoints:</strong></p>
  
  <pre><code>key_value = 'this_key'
  hash_val = generate_uuid(value)
  try:
      cursor.execute(
          '''
          SAVEPOINT duplicate_hash_savepoint;
          INSERT INTO keys (hash, key)
              VALUES (%s, %s)
              RETURNING id;
          '''
          (hash_val, key_value)
      )
  
      result = cursor.fetchone()[0]
      cursor.execute('''RELEASE SAVEPOINT duplicate_hash_savepoint''')
      return result
  except psycopg2.IntegrityError as e:
      cursor.execute(
          '''
          ROLLBACK TO SAVEPOINT duplicate_hash_savepoint;
          '''
      )
  
      #TODO: Should ensure that values match and this isn't just
      #a hash collision
  
      cursor.execute(
          '''
          SELECT id FROM keys WHERE hash=%s LIMIT 1;
          '''
          (hash_val,)
      )
      return cursor.fetchone()[0]
  </code></pre>
  
  <p><strong>Update:</strong>
  So I believe I a hint on <a href="https://dba.stackexchange.com/questions/27688/locking-issue-with-concurrent-delete-insert-in-postgresql">another stackexchange site:</a></p>
  
  <p>Specifically:<br></p>
  
  <blockquote>
    <p>UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands
    behave the same as SELECT in terms of searching for target rows: they
    will only find target rows that were committed as of the command start
    time1. However, such a target row might have already been updated (or
    deleted or locked) by another concurrent transaction by the time it is
    found. In this case, the would-be updater will wait for the first
    updating transaction to commit or roll back (if it is still in
    progress). If the first updater rolls back, then its effects are
    negated and the second updater can proceed with updating the
    originally found row. If the first updater commits, the second updater
    will ignore the row if the first updater deleted it2, otherwise it
    will attempt to apply its operation to the updated version of the row.</p>
  </blockquote>
  
  <p>While I'm still not exactly sure where the co-dependency is, it seems that processing a large number of key/value pairs without commiting would likely result in something like this. Sure enough, if I commit after each individual configuration file is added, the deadlocks don't occur.</p>