title: MySQL - why one insert locks second insert?
body: <p>I want to insert row with unique value in non-primary key field. I cannot use unique index (because of NULL values in multiple field index, but it is not important here).</p>
  
  <p>I use INSERT/SELECT WHERE NOT EXISTS as an atomic (I hope) operation - see below.</p>
  
  <p>I will describe problem on simplified example:</p>
  
  <p>I have an empty table:</p>
  
  <p><code>CREATE TABLE Test (
    Id int(11) NOT NULL AUTO_INCREMENT,
    A int(11) DEFAULT NULL,
    PRIMARY KEY (Id)
  ) ENGINE=InnoDB;</code></p>
  
  <p>... and 2 sessions with autocommit mode switched off:</p>
  
  <p>In first session I execute:</p>
  
  <p><code>INSERT INTO Test (A)
    SELECT A FROM (SELECT 1 AS A) Inn
      WHERE NOT EXISTS (SELECT A FROM Test WHERE A = 1 FOR UPDATE);</code></p>
  
  <p>In second session I execute:</p>
  
  <p><code>INSERT INTO Test (A)
    SELECT A FROM (SELECT 2 AS A) Inn
      WHERE NOT EXISTS (SELECT A FROM Test WHERE A = 2 FOR UPDATE);</code></p>
  
  <p>As you can see, I'm inserting independent values. But this second session is locked by the first one. Why? Maybe it is because of gap/next-key lock.</p>
  
  <p>How else i can insert records with "manual" avoiding of duplicate keys and without mutual locking?</p>
  
  <p>Default transaction isolation level is REPEATABLE READ and I don't want to change default settings of database, because other applications use it.</p>