title: Handling race conditions in PostgreSQL
body: <p>I have several workers, each holding its own connection to PostgreSQL. The workers manipulate with different tables.</p>
  
  <p>The workers handle parallel requests from outside the system. One of the tables being accessed is the table of users. When some information comes, I first need to ensure there is a record for the user in the table. If there is no record, I wish to create one at first.</p>
  
  <p>I'm using the following idiom:</p>
  
  <pre><code>if [user does not exist] then [create user]
  </code></pre>
  
  <p>The code of <code>[user does not exist]</code> is:</p>
  
  <pre><code>SELECT id FROM myschema.users WHERE userId='xyz'
  </code></pre>
  
  <p>and I test whether any row is returned.</p>
  
  <p>The (simplified) code of <code>[create user]</code> is:</p>
  
  <pre><code>INSERT INTO myschema.users VALUES ('xyz')
  </code></pre>
  
  <p>When my system handles parallel streams of different information concerning <strong>the same</strong> user, I often get PostgreSQL error:</p>
  
  <pre><code>Key (id)=(xyz) already exists
  </code></pre>
  
  <p>It happens because the <code>SELECT</code> command returns no rows, then another worker creates the user, any my worker attempts to do the same, resulting in exemplary concurrency error.</p>
  
  <p>According to PostgreSQL documentation, by default, whenever I implicitly start a transaction, the table becomes locked for as long as I don't commit it. I'm not using autocommit and I only commit the transaction in blocks, e.g. after the whole <code>if-else</code> block.</p>
  
  <p>Indeed, I could put the <code>if-else</code> stuff into SQL directly, but it does not solve my problem of locking in general. I was supposing that "the winner takes it all" paradigm will work, and that the first worker which manages to execute the <code>SELECT</code> command will own the locks until it calls <code>COMMIT</code>.</p>
  
  <p>I've read many different topics here at SO, but I'm still not sure what the right solution is. Should I use explicit locking of tables, because the implicit locking does not work? How can I ensure that only single worker owns a table at time?</p>