mysql - 在 mysql 中插入行时锁定

Question

我们有 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;

在并行线程中，我们执行这些操作(在 java 中)

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

在并发模式下，2 个线程可以尝试插入 msisdn="000000002"和 service-id="web-sub-1"以及不同 subscriptionId 的行，因为当前时间戳可能不同。两个线程都先执行选择，得到零结果，然后都插入。所以我们尝试将这 2 个查询连接到事务中，但是锁定不存在的行存在问题 - 当我们需要锁定插入或类似的东西时。而且我们不想在这 2 个操作期间锁定所有表，因为我们认为我们的系统在这种情况下会运行得太慢。我们不能为这种情况创建 uniq 键，因为对于一个 abonent 可以有多个具有相同取消订阅状态的行。如果我们尝试为同一服务插入 2 个订阅者，则主键可以包含具有不同秒数的时间戳。我们尝试使用 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE，但我们遇到了死锁，而且它对数据库服务器来说是繁重的操作。

为了进行测试，我们打开了 2 个终端并逐步执行:

# 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

有没有办法做到既不死锁又不锁定全表？我们分析的其他变体是:1.单独的表2. 插入和删除不需要的行。

Answer 1

A 计划。这将插入(如有必要)或静默不执行任何操作:

INSERT IGNORE ...;

B 计划。这可能有点矫枉过正，因为没有什么需要“更新”:

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

Plan C. 这条语句大部分被IODKU替代:

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

A 和 B(可能还有 C)是“原子的”，因此不会出现死锁。