title: Postgresql Share Row Exclusive Lock VS pg_advisory_lock
body: <p>I'm running Postgres 9.3 and have a table tags, accessed through Python's psycopg2 module. I have a table called 'tags' that gets updated/inserted by two different methods, called 'update' and 'insert.' I also have several workers running concurrently, each of which call either 'update' or 'insert.' Due to a uniqueness constraint, I'd like to lock the 'tags' table directly before I perform the inserts or updates, and I commit the transaction directly after.</p>
  
  <p>So my code roughly looks like (in psycopg2 parlance)</p>
  
  <p>UPDATE:</p>
  
  <pre><code>cur.execute(LOCK TABLE tags IN SHARE ROW EXCLUSIVE MODE)
  cur.execute(UPDATE tags SET ...)
  cur.execute(DELETE FROM tags ....)
  cur.execute(INSERT INTO tags ...)
  connection.commit()
  </code></pre>
  
  <p>INSERT:</p>
  
  <pre><code>cur.execute(LOCK TABLE tags IN SHARE ROW EXCLUSIVE MODE)
  cur.execute(DELETE FROM tags ....)
  cur.execute(INSERT INTO tags ...)
  connection.commit()
  </code></pre>
  
  <p>And my schema looks like </p>
  
  <pre><code>user_id varchar NOT NULL, 
  tag varchar NOT NULL, 
  time timestamptz, 
  CONSTRAINT unique_tag_key PRIMARY KEY (user_id, tag)
  CONSTRAINT seen_before_user FOREIGN_KEY (user_id)
      REFERENCES user_id_table (user_id) MATCH SIMPLE 
      ON UPDATE NO ACTION ON DELETE NO ACTION 
  </code></pre>
  
  <p>The problem I'm getting is that when I run concurrent workers I get deadlocks upon executing the share lock. 
  Weirdly though, if I replace the LOCK TABLE calls with a call like </p>
  
  <pre><code>cur.execute("SELECT pg_advisory_lock(tag_hash)")
  </code></pre>
  
  <p>where tag_hash is a hash on the tags table name 'tags', I get no such errors. </p>
  
  <p>Why is it that I get errors with SHARE ROW EXCLUSIVE, but not pg_advisory lock? Are there any downsides to using pg_advisory locks here if I can guarantee that the tags table never gets modified outside of these two methods?</p>