Locking during inserting rows in mysql

Question

We have table

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;

In parallel threads we perform actions (in java) like these

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');

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.

For tests we opened 2 terminals and did step by step:

# Window 1
mysql> start transaction;
mysql> 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> SELECT SUBSCRIPTION_ID FROM TEST_SUBSCRIBERS s
           WHERE s.SERVICE_ID="web-sub-1" AND s.MSISDN="000000002" FOR UPDATE;

# Window 1
mysql> INSERT INTO TEST_SUBSCRIBERS
            (SUBSCRIPTION_ID, MSISDN, STATE, SERVICE_ID) 
        VALUES('web-sub-1-000000002-1504624818', '000000002', 'ACTIVE', 'web-sub-1');

# Window 2
mysql> 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

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.

Answer 1

Following answer from @RickJames.

Plan D. Use READ-COMMITTED

Window 1

mysql> set tx_isolation='READ-COMMITTED';
mysql> start transaction;
mysql> SELECT SUBSCRIPTION_ID FROM TEST_SUBSCRIBERS s
       WHERE s.SERVICE_ID="web-sub-1" AND s.MSISDN="000000002" FOR UPDATE;

Window 2

mysql> set tx_isolation='READ-COMMITTED';
mysql> start transaction;
mysql> SELECT SUBSCRIPTION_ID FROM TEST_SUBSCRIBERS s
       WHERE s.SERVICE_ID="web-sub-1" AND s.MSISDN="000000002" FOR UPDATE;

Window 1

mysql> INSERT INTO TEST_SUBSCRIBERS (SUBSCRIPTION_ID, MSISDN, STATE, SERVICE_ID) 
    VALUES('web-sub-1-000000002-10', '000000002', 'ACTIVE', 'web-sub-1');

Window 2

mysql> INSERT INTO TEST_SUBSCRIBERS (SUBSCRIPTION_ID, MSISDN, STATE, SERVICE_ID) 
    VALUES('web-sub-1-000000002-10', '000000002', 'ACTIVE', 'web-sub-1');

<begins lock wait>

Window 1

mysql> commit;

Window 2

<lock wait ends immediately>

ERROR 1062 (23000): Duplicate entry 'web-sub-1-000000002-10' for key 'PRIMARY'

The duplicate key error is not a deadlock, but it's still an error. But it doesn't roll back the entire transaction, it just cancels the attempted insert. You still have an active transaction with any other changes that have been successfully executed still pending.

Plan E. Use a queue

Instead of having concurrent Java threads inserting to the database, just have the Java threads enter items into a message queue (e.g. ActiveMQ). Then create one Java thread to do nothing but pull items from the queue and insert them into the database. This prevents deadlocks because there's only one thread inserting to the database.

Plan F. Embrace the deadlocks

You can't prevent all types of deadlocks, you can only handle them when they occur. Concurrent systems should be designed to anticipate some number of deadlocks, and retry operations when necessary.

Answer 2

Plan A. This will either insert (if necessary) or silently do nothing:

INSERT IGNORE ...;

Plan B. This may be overkill, since nothing needs "updating":

INSERT INTO ...
    (...)
    ON DUPLICATE KEY UPDATE
    ...;

Plan C. This statement is mostly replaced by IODKU:

REPLACE ... (same syntax as INSERT, but it does a silent DELETE first)

A and B (and probably C) are "atomic", so there is no chance of a deadlock.