INSERT ON DUPLICATE KEY UPDATE はデッドロックする

MySQLにはUPSERT用途に使える、INSERT ... ON DUPLICATE KEY UPDATE ... という文がありますが、これをUNIQUE制約のあるテーブルに対して使うと、明示的にトランザクションを使わなくても簡単にデッドロックします。

この記事はデッドロックを披露する記事です。

※ MySQL9.1を使用

テーブルの準備

まず、使用するテーブルと初期データを追加します。

CREATE TABLE a
(
  id bigint NOT NULL PRIMARY KEY AUTO_INCREMENT,
  uniq_col varchar(255) NOT NULL,
  updated_at datetime,
  UNIQUE KEY uniq (uniq_col)
);

INSERT INTO a (id, uniq_col, updated_at)
VALUES (1, '既存1', now()),
       (2, '既存2', now()),
       (3, '既存3', now()),
       (4, '既存4', now());

UNIQUE制約を持つテーブルを作成し、簡単なデータを挿入しています。

デッドロック

テーブルが出来たので、デッドロックさせます。

クエリの実行にはターミナルを使います。
2つのターミナルから、トランザクションをかけずに、ただ、同時にクエリを実行するだけです。

別の順番で更新するとデッドロック

まずは既存の行を別の順番で更新します。

INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()), ('既存2', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

と

INSERT INTO a (uniq_col, updated_at) VALUES('既存2', now()), ('既存1', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

実行すると Error 1213 (40001): Deadlock found when trying to get lock; try restarting transaction と、デッドロックになります。

別の行を更新してもデッドロック

同じ行を更新する必要はありません。全く別の行を更新してもデッドロックします。

INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()), ('既存2', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

と

INSERT INTO a (uniq_col, updated_at) VALUES('既存3', now()), ('既存4', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

デッドロックになります。

INSERT ON DUPLICATE KEY UPDATEは滅茶苦茶ロックする

INSERT ... ON DUPLICATE KEY UPDATE ...がどんなロックをしているかトランザクションをかけながら確認します。

挿入時のロック

まずは新規に挿入した場合

BEGIN;
INSERT INTO a (uniq_col, updated_at) VALUES('新規', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

data_locksを使って、ロック状況を確認します。

SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

#=>
+-----------+------------+-------------+-----------+-----------+-------------+-----------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+------------+-------------+-----------+-----------+-------------+-----------+
|        79 | NULL       | a           | TABLE     | IX        | GRANTED     | NULL      |
+-----------+------------+-------------+-----------+-----------+-------------+-----------+
1 row in set (0.00 sec)

テーブルに対して、IXロックだけかかっていることが確認できます。普通ですね。

確認が終わったらロールバックします。

ROLLBACK;

更新時のロック

次に、既存の行を更新したときのロックを確認します。

BEGIN;
INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

ロックの状況を確認します。

SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+---------------+-------------+------------------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+-----------+------------+-------------+-----------+---------------+-------------+------------------------+
|        79 | NULL       | a           | TABLE     | IX            | GRANTED     | NULL                   |
|        79 | uniq       | a           | RECORD    | X             | GRANTED     | '既存1', 1             |
|        79 | PRIMARY    | a           | RECORD    | X             | GRANTED     | supremum pseudo-record |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
+-----------+------------+-------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)

滅茶苦茶ロックしています。
3行目でsupremum pseudo-record と、IDの上限までXロックをかけているのがわかります。
新しい行を作るためには待機しなくてはいけなくなりました。

ロックされていたら挿入できない

確認のため、別のプロセスから新しい行の挿入をしてみます。

BEGIN;
INSERT INTO a (uniq_col, updated_at) VALUES('待ち続ける', now());

このクエリは終わりません。
data_locksでも待っていることが確認できます。

SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
|        79 | NULL       | a           | TABLE     | IX                 | GRANTED     | NULL                   |
|        80 | NULL       | a           | TABLE     | IX                 | GRANTED     | NULL                   |
|        79 | uniq       | a           | RECORD    | X                  | GRANTED     | '既存1', 1             |
|        79 | PRIMARY    | a           | RECORD    | X                  | GRANTED     | supremum pseudo-record |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP      | GRANTED     | 1                      |
|        80 | PRIMARY    | a           | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
6 rows in set (0.00 sec)

ロックされていると更新もできない

新規の挿入だけでなく、既存の行も更新できません。

ロックをかけたまま、別のプロセスから別の行を更新しようとします。

BEGIN;
INSERT INTO a (uniq_col, updated_at) VALUES('既存2', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

このクエリも終わりません。
data_locksを見ます。

SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
|        79 | NULL       | a           | TABLE     | IX                 | GRANTED     | NULL                   |
|        80 | NULL       | a           | TABLE     | IX                 | GRANTED     | NULL                   |
|        79 | uniq       | a           | RECORD    | X                  | GRANTED     | '既存1', 1             |
|        79 | PRIMARY    | a           | RECORD    | X                  | GRANTED     | supremum pseudo-record |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP      | GRANTED     | 1                      |
|        80 | PRIMARY    | a           | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
6 rows in set (0.00 sec)

挿入時と同じように、X,INSERT_INTENTIONを取ろうとして止まっています。

INSERT ... ON DUPLICATE KEY UPDATE ...は既存の行を更新するか関係なく、挿入先を確保しようとするわけです。

トランザクションを使わなくてもロック

今度はトランザクションなしでデッドロックしているときの様子を見ます。

3つのクエリを別プロセスから同時に実行します。

INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()), ('既存2', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;
INSERT INTO a (uniq_col, updated_at) VALUES('既存3', now()), ('既存4', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;
SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

タイミングの問題でロック状況が変わりますが、何回かやると、両方のプロセスがWAITINGになっていることを見ることができます。デッドロックですね。

SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
|        80 | NULL       | a           | TABLE     | IX                 | GRANTED     | NULL                   |
|        79 | NULL       | a           | TABLE     | IX                 | GRANTED     | NULL                   |
|        80 | uniq       | a           | RECORD    | X                  | GRANTED     | '既存3', 3             |
|        79 | uniq       | a           | RECORD    | X                  | GRANTED     | '既存1', 1             |
|        80 | PRIMARY    | a           | RECORD    | X                  | GRANTED     | supremum pseudo-record |
|        80 | PRIMARY    | a           | RECORD    | X                  | GRANTED     | supremum pseudo-record |
|        80 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP      | GRANTED     | 3                      |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP      | GRANTED     | 1                      |
|        79 | PRIMARY    | a           | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
|        80 | PRIMARY    | a           | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
+-----------+------------+-------------+-----------+--------------------+-------------+------------------------+
10 rows in set (0.00 sec)

トランザクションをかけた時と同じように、INSERT ... ON DUPLICATE KEY UPDATE ...は更新時にもsupremum pseudo-recordのXロックを取って挿入を妨害するせいで、同時に実行するとデッドロックになるわけです。

READ COMMITTEDならロックが減る

トランザクションのレベルをREAD COMMITEDにするとロックの数が減ります。

READ COMMITEDにした状態で更新します。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;

ロック状態をみると、

 SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA    |
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
|        79 | NULL       | a           | TABLE     | IX            | GRANTED     | NULL         |
|        79 | uniq       | a           | RECORD    | X             | GRANTED     | '既存1', 1   |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP | GRANTED     | 1            |
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
3 rows in set (0.00 sec)

と、supremum pseudo-recordのXロックがなくなりました。

前と同じように、トランザクション無しで3つ同時に実行しても、デッドロックしません。

INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()), ('既存2', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;
INSERT INTO a (uniq_col, updated_at) VALUES('既存3', now()), ('既存4', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;
SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

supremum pseudo-recordがないことが確認できます。

 SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA    |
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
|        79 | NULL       | a           | TABLE     | IX            | GRANTED     | NULL         |
|        80 | NULL       | a           | TABLE     | IX            | GRANTED     | NULL         |
|        80 | uniq       | a           | RECORD    | X             | GRANTED     | '既存3', 3   |
|        79 | uniq       | a           | RECORD    | X             | GRANTED     | '既存1', 1   |
|        80 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP | GRANTED     | 3            |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP | GRANTED     | 1            |
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
6 rows in set (0.00 sec)

READ COMMITED だけで解決するのか

READ COMMITEDにすることができるのかという問題はおいておいても、UNIQUEに対するロックは残るので別の順番で更新するとデッドロックで起きたデッドロックは解決しません。

READ COMMITEDにした状態で3つ同時に実行すると、

INSERT INTO a (uniq_col, updated_at) VALUES('既存1', now()), ('既存2', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;
INSERT INTO a (uniq_col, updated_at) VALUES('既存2', now()), ('既存1', now()) AS new ON DUPLICATE KEY UPDATE updated_at = new.updated_at;
SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;

Xロックでデッドロックになることを確認できます。順番は大切ですね。

SELECT THREAD_ID,INDEX_NAME,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
#=>
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
| THREAD_ID | INDEX_NAME | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA    |
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
|        79 | NULL       | a           | TABLE     | IX            | GRANTED     | NULL         |
|        80 | NULL       | a           | TABLE     | IX            | GRANTED     | NULL         |
|        80 | uniq       | a           | RECORD    | X             | GRANTED     | '既存2', 2   |
|        79 | uniq       | a           | RECORD    | X             | GRANTED     | '既存1', 1   |
|        80 | uniq       | a           | RECORD    | X             | WAITING     | '既存1', 1   |
|        79 | uniq       | a           | RECORD    | X             | WAITING     | '既存2', 2   |
|        79 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP | GRANTED     | 1            |
|        80 | PRIMARY    | a           | RECORD    | X,REC_NOT_GAP | GRANTED     | 2            |
+-----------+------------+-------------+-----------+---------------+-------------+--------------+
8 rows in set (0.00 sec)

ということで、UNIQUE制約のあるテーブルにINSERT ... ON DUPLICATE KEY UPDATE ...すると簡単にデッドロックする、という話でした。