title: Locking during inserting rows in mysql
body: <p>We have table</p>
  
  <pre><code>CREATE TABLE TEST_SUBSCRIBERS (
    SUBSCRIPTION_ID varchar(255) NOT NULL COMMENT 'Subscriber id in format MSISDN-SERVICE_ID-TIMESTAMP',
    MSISDN varchar(12) NOT NULL COMMENT 'Subscriber phone',
    STATE enum ('ACTIVE', 'INACTIVE', 'UNSUBSCRIBED_SMS', 'UNSUBSCRIBED_PARTNER', 'UNSUBSCRIBED_ADMIN', 'UNSUBSCRIBED_REBILLING') NOT NULL,
    SERVICE_ID varchar(255) NOT NULL COMMENT 'Id of service',
    PRIMARY KEY (SUBSCRIPTION_ID)
  )
  ENGINE = INNODB
  CHARACTER SET utf8
  COLLATE utf8_general_ci;
  </code></pre>
  
  <p>In parallel threads we perform actions (in java) like these</p>
  
  <pre><code>1. Select active subscribers
  SELECT  *
      FROM  TEST_SUBSCRIBERS
      WHERE  SERVICE_ID='web-sub-1'
        and  MSISDN='000000002'
        AND  STATE IN ('ACTIVE', 'INACTIVE');
  2. If there are no such subscribers, I can insert it
  INSERT INTO  TEST_SUBSCRIBERS
                      (SUBSCRIPTION_ID, MSISDN, STATE, SERVICE_ID)
               VALUES ('web-sub-1-000000002-1504624819', '000000002', 'ACTIVE', 'web-sub-1');
  </code></pre>
  
  <p>In concurrency mode 2 threads can try to insert row with msisdn="000000002" and service-id="web-sub-1" and different subscriptionId because the current timestamp can be different. Both threads perform first select, get zero results and both insert. So we try to join these 2 queries into tranaction, but there is problem with locking for not existing rows - when we need lock for insert or something like that.
  And we do not want to lock all table during this 2 actions because we suppose that our system will work too slowly in this case.
  We cannot create uniq key for this situation, because for one abonent there can be multiple rows with the same unsubscribed statuses. And if we try to insert the 2 subscribers for the same service, primary key can contain timestamp with different seconds.
  We tried to use SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE, but we get deadlock and it's heavy operation for database server.</p>
  
  <p>For tests we opened 2 terminals and did step by step:</p>
  
  <pre><code># Window 1
  mysql&gt; start transaction;
  mysql&gt; SELECT SUBSCRIPTION_ID FROM TEST_SUBSCRIBERS s
             WHERE s.SERVICE_ID="web-sub-1" AND s.MSISDN="000000002" FOR UPDATE;
  
  # Window 2
  start transaction;
  mysql&gt; SELECT SUBSCRIPTION_ID FROM TEST_SUBSCRIBERS s
             WHERE s.SERVICE_ID="web-sub-1" AND s.MSISDN="000000002" FOR UPDATE;
  
  # Window 1
  mysql&gt; INSERT INTO TEST_SUBSCRIBERS
              (SUBSCRIPTION_ID, MSISDN, STATE, SERVICE_ID) 
          VALUES('web-sub-1-000000002-1504624818', '000000002', 'ACTIVE', 'web-sub-1');
  
  # Window 2
  mysql&gt; INSERT INTO TEST_SUBSCRIBERS
                (SUBSCRIPTION_ID, MSISDN, STATE, SERVICE_ID) 
             VALUES('web-sub-1-000000002-1504624819', '000000002', 'ACTIVE', 'web-sub-1');
  ERROR 1213 (40001): Deadlock found when trying to get lock;
                      try restarting transaction
  </code></pre>
  
  <p>Is there any way to do such without deadlocks and without locking full table? Other variants that we analyzed were: 
  1. separate table 
  2. inserting and deleting unwanted rows.</p>