title: Deadlock on inserting a new row
body: <p>I have a table that stores the keys of other tables for a many-to-many relationship. Example:</p>
  <pre><code>create table for_example(a uuid, b uuid, primary key(a, b));
  </code></pre>
  <p>Multiple transactions can insert data into a table at the same time. In this case, it may turn out that two transactions are inserting the same data. In this case, one of these transactions will see that the record already exists and will wait for the parallel transaction to complete its work in order to continue its work. But it may turn out that before the lock occurred, the second transaction had already inserted any data into this table, and in this case, if the first transaction tries to insert new data that the second transaction has already inserted, a deadlock will occur. Example:</p>
  <pre><code>begin;
  
  --check exists row in table before insert in condition &quot;IF&quot;
  select 1 from for_example where a = '002e202d-c564-45cd-a0a7-d8fe8b930d99' and b = '4ab9d0ed-80c7-42ee-a641-59834a61f06d';
  
  --if not exists then insert
  insert into for_example(a, b)
  values('002e202d-c564-45cd-a0a7-d8fe8b930d99', '4ab9d0ed-80c7-42ee-a641-59834a61f06d')
  ON CONFLICT DO NOTHING;
  </code></pre>
  <p>we pass at this time to the second transaction</p>
  <pre><code>begin;
  
  --check exists row in table before insert in condition &quot;IF&quot;
  select 1 from for_example where a = '4ab9d0ed-80c7-42ee-a641-59834a61f06d' and b = '002e202d-c564-45cd-a0a7-d8fe8b930d99';
  
  --if not exists then insert
  insert into for_example(a, b)
  values('4ab9d0ed-80c7-42ee-a641-59834a61f06d', '002e202d-c564-45cd-a0a7-d8fe8b930d99')
  ON CONFLICT DO NOTHING;
  </code></pre>
  <p>we return to the first transaction and try to insert the data that the second transaction has already inserted</p>
  <pre><code>--check exists row in table before insert in condition &quot;IF&quot;
  select 1 from for_example where a = '4ab9d0ed-80c7-42ee-a641-59834a61f06d' and b = '002e202d-c564-45cd-a0a7-d8fe8b930d99';
  
  --if not exists then insert
  insert into for_example(a, b)
  values('4ab9d0ed-80c7-42ee-a641-59834a61f06d', '002e202d-c564-45cd-a0a7-d8fe8b930d99')
  ON CONFLICT DO NOTHING;
  </code></pre>
  <p>blocking occurs, we are waiting for the second transaction to finish its work, but at this time the second transaction is trying to insert data that the first transaction has already inserted</p>
  <pre><code>--check exists row in table before insert in condition &quot;IF&quot;
  select 1 from for_example where a = '002e202d-c564-45cd-a0a7-d8fe8b930d99' and b = '4ab9d0ed-80c7-42ee-a641-59834a61f06d';
  
  --if not exists then insert
  insert into for_example(a, b)
  values('002e202d-c564-45cd-a0a7-d8fe8b930d99', '4ab9d0ed-80c7-42ee-a641-59834a61f06d')
  ON CONFLICT DO NOTHING;
  </code></pre>
  <p>error occurs: [40P01] ERROR: deadlock detected.
  Is it possible to somehow check for the existence of a row in a table with the condition that it may already have been inserted by a parallel transaction?
  This is a real issue we're having on a production server right now.</p>