何が起きたのか

たまーにproduction環境でデッドロックが発生した

デッドロック発生時のログや各処理ごとで実行されるSQLのログから調査して
デッドロックが発生するクエリは特定できたがstagingやローカルでは再現せず

環境	DB
production	MySQL互換Aurora
staging	MySQL
development	dockerのMySQL

各環境は上記の状態だったため
Aurora独自の何かがあるのでは？と思い検証をしてみることに

デッドロック発生後の対処

デッドロック発生時、mysqlコマンドでDBにつないで
原因となるクエリをKILLしてみたが解決せず
リードレプリカをフェイルオーバーさせることで対応した

検証開始

デッドロックが起きていたテーブル群

offers
offer_child_1
offer_child_2
documents
document_offer_child_2

※わかりやすいようにテーブル名は実際とは変えています

スキーマ

CREATE TABLE offers (
  id bigint(8) AUTO_INCREMENT PRIMARY KEY,
  title text
);
CREATE TABLE offer_child_1 (
  id bigint(8) AUTO_INCREMENT PRIMARY KEY,
  title text,
  offer_id bigint(8),
  FOREIGN KEY (offer_id) REFERENCES offers(id)
);
CREATE TABLE offer_child_2 (
  id bigint(8) AUTO_INCREMENT PRIMARY KEY,
  offer_id bigint(8),
  FOREIGN KEY (offer_id) REFERENCES offers(id)
);
CREATE TABLE documents (
  id bigint(8) AUTO_INCREMENT PRIMARY KEY,
  url text
);
CREATE TABLE document_offer_child_2 (
  id bigint(8) AUTO_INCREMENT PRIMARY KEY,
  document_id bigint(8),
  offer_child_2_id bigint(8),
  FOREIGN KEY (document_id) REFERENCES documents(id),
  FOREIGN KEY (offer_child_2_id) REFERENCES offer_child_2(id)
);

デッドロックが起きていたトランザクションのクエリ

事前に流すクエリ

INSERT INTO offers (title) VALUES (CURRENT_TIMESTAMP);
INSERT INTO offer_child_2 (offer_id) VALUES (1);
INSERT INTO documents(url) VALUES ('http://example.com');

トランザクション

No	トランザクション1	トランザクション2
	`BEGIN;`	`BEGIN;`
1	`UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;`
2		`SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;`
3	`INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);`
4		`INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');`

なぜデッドロックが起きるのか

InnoDBの行レベルロックには共有ロック(IS)と排他ロック(IX)の2種類がある
共有ロック(IS)同士では競合は発生しないがそれ以外の組み合わせでは競合(ロック待ち)が発生する

	IS	IX
IS		競合
IX	競合	競合

そして今回のケースでいうと
No.1のUPDATE文は該当のレコードにIXを獲得
No.2のSELECT FOR UPDATE文は該当のレコードIXを獲得
No.3のINSERT文は外部キーの参照先(documentsとoffer_child_2)のレコードにISを獲得
No.4のINSERT文は外部キーの参照先(offers)のレコードにISを獲得
となるため

No.3はNo.2をロック待ち、No.4はNo.1をロック待ちをして
トランザクション1、２間でロックが交錯しデッドロックが発生する

各環境での挙動

事前にパラメータグループから

Aurora

innodb_lock_wait_timeout = 10
lock_wait_timeout = 15

MySQL(auroraに準拠)

autocommit = 0
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 0
innodb_table_locks = 1
lock_wait_timeout = 15

に設定しておく
innodb_rollback_on_timeoutとinnodb_table_locksは
Auroraにパラメータグループで設定できないためAuroraでのデフォルト値に準拠
SHOW VARIABLES;で確認可能

autocommitについてはAuroraのデフォルトが0で
デッドロック検知には0がいいらしいのでMySQL側も0に設定してAuroraに合わせる

ref: https://dev.mysql.com/doc/refman/5.6/ja/innodb-deadlock-detection.html

MySQL互換のAurora(5.7.12)

transaction1

mysql> BEGIN;
Query OK, 0 rows affected (0.05 sec)

mysql> UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);

transaction2

mysql> BEGIN;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
+----+----------+
| id | offer_id |
+----+----------+
|  1 |        1 |
+----+----------+
1 row in set (0.09 sec)

mysql> INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

transaction1もtransaction2もタイムアウトせず止まり続け
INSERTのプロセスが残ったままDBのCPUが100%に貼りつく

MySQL(5.7.16)(5.7.12がなかったので仕方なく。。。)

transaction1

mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);
Query OK, 1 row affected (0.90 sec)

transaction2

mysql> BEGIN;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
+----+----------+
| id | offer_id |
+----+----------+
|  1 |        1 |
+----+----------+
1 row in set (0.07 sec)

mysql> INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

transaction1のINSERT INTO document_offer_child_2...は最初待たされるが
transaction2のINSERT INTO offer_child_1...を実行すると
transaction2は瞬時にdeadlockが検知されてトランザクションがロールバックされて
transaction1の待ちがなくなりINSERTを完了する

Auroraの他のパターンのデッドロックでの検知の挙動を検証

※見やすくなるようSQLのみ書いていく実行順はここまで同様transaction1/2交互

長いので折りたたんだ

全パターンを網羅しようとすると、
4クエリ(=2トランザクション×2クエリ)がそれぞれIS/IXのいずれかだとして
インテンションロックの組み合わせが16パターン

さらに
IXのDMLは SELECT FOR UPDATE / UPDATE
ISのDMLは INSERT / SELECT LOCK IN SHARE MODE
とすると、それぞれ2パターンとして
1つのインテンションロックの組み合わせの中でクエリの組み合わせは16パターン
全部で256(=16*16)パターンになる

また、IXにDELETE文も考慮するとクエリの組み合わせが36パターンになり
全部で576(16*36)パターンになってさすがにつらいので網羅せず
少しずつ条件を変えて検知できないデッドロックに作用してそうなクエリを探す

1. transaction1の先発クエリも`SELECT FOR UPDATE`にしてみる

結果: × デッドロックを検知せず止まり続ける
考察: 先発のUPDATEは関係なさそう

transaction1

BEGIN;
SELECT * from offers WHERE id = 1 LIMIT 1 FOR UPDATE;
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

2. transaction2の先発クエリも`UPDATE`にしてみる

結果: × デッドロックを検知せず止まり続ける
考察: 先発のSELECT FOR UPDATEは関係なさそう

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);

transaction2

BEGIN;
UPDATE offer_child_2 SET offer_id = 1 WHERE id = 1;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

3. transaction1の後発クエリを`SELECT LOCK IN SHARE MODE`にしてみる

結果: ◯ transaction1はデッドロックを検知してロールバックし、transaction2は正常終了
考察: 後発が両トランザクションINSERTじゃないと検知しないデッドロックにならない？

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

4. transaction2の後発クエリを`SELECT LOCK IN SHARE MODE`にしてみる

結果: ◯ transaction2はデッドロックを検知してロールバックし、transaction1は正常終了
考察: 3とはtransaction2がロールバックされた、InnoDBの仕様(※1)か

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
SELECT * FROM offers WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;

※1 https://dev.mysql.com/doc/refman/5.6/ja/innodb-deadlock-detection.html

小さいトランザクションを選択してロールバックしようと試みます

とあるので3のパターンではtransaction1が、今回はtransaction2が小さいと判断されたのだろう

ここまでで

transaction1/2いずれも後発がINSERTの時に検知されないデッドロックになることがわかった
では後発がいずれもSELECT/UPDATE/DELETEのときはどうだろうか

5. transaction1/2の後発クエリを`SELECT LOCK IN SHARE MODE`にしてみる

結果: ◯ transaction2はデッドロックを検知してロールバックし、transaction1は正常終了
考察: 後発がSELECTだとデッドロック検知してくれるらしい

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
SELECT * FROM offers WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;

6. transaction1/2の後発クエリを`UPDATE`にしてみる

結果: ◯ transaction2はデッドロックを検知してロールバックし、transaction1は正常終了
考察: 後発がUPDATEでもデッドロック検知してくれるらしい

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
UPDATE offer_child_2 SET offer_id = 1 WHERE id = 1;

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;

7. transaction1/2の後発クエリを`DELETE`にしてみる

結果: ◯ transaction2はデッドロックを検知してロールバックし、transaction1は正常終了
考察: 後発がDELETEでもデッドロック検知される INSERTだけがやはり特殊なのか

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
DELETE from offer_child_2 WHERE id = 1;

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
DELETE from offers WHERE id = 1;

8. transaction1/2の先発クエリを`INSERT`にしてみる

結果: ◯ transaction2はデッドロックを検知してロールバックし、transaction1は正常終了
考察: INSERTが後発であることに意味があるらしい

transaction1

BEGIN;
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;

transaction2

BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;

結果

Auroraでは、transaction1/2ともに後発のクエリがINSERTの時に発生するデッドロックが
検知されず止まり続けることがわかった
また、検知されないパターンのデッドロックをMySQLで試すと検知される

気になることがあったので追加で検証

そもそもデッドロックではなく普通にINSERTがロック待ちした時ならちゃんとタイムアウトするのか
UPDATEの外部キー参照時の挙動はINSERTと異なるのか

AuroraとMySQLの`INSERT`と`UPDATE`のロック待ちの挙動を検証

長いので折りたたんだ

9. `IX`獲得されたレコードを参照する`INSERT`をする

Aurora: 待ちが発生タイムアウトもせず
MySQL: 待ちが発生タイムアウトもせず

transaction1

BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 FOR UPDATE;

transaction2

BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

外部キーを持つレコードへの`UPDATE`時の待ちの挙動を検証

10. UPDATE対象レコードの外部キーは変更せずリレーションがないカラムを更新する

Aurora: 待ちなしで更新できた
MySQL: 待ちなしで更新できた

まず普通にデータ作る

INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

transaction1

BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 FOR UPDATE;

transaction2

-- id確認のSELECT
-- SELECT * FROM offer_child_1 WHERE offer_id = 1;
BEGIN;
UPDATE offer_child_1 SET title = CURRENT_TIMESTAMP WHERE id = 1;

11. UPDATE対象レコードの外部キーを変更すると

Aurora: 待ちが発生タイムアウトもせず
MySQL: 待ちが発生タイムアウトもせず

事前に

INSERT INTO offers (title) VALUES (CURRENT_TIMESTAMP);

transaction1

-- id確認
-- SELECT * FROM offers;
BEGIN;
SELECT * FROM offers WHERE id = 2 LIMIT 1 FOR UPDATE;

transaction2

-- id確認のSELECT
-- SELECT * FROM offer_child_1 WHERE offer_id = 1;
BEGIN;
UPDATE offer_child_1 SET offer_id = 2 WHERE id = 1;

12. デッドロック発生するクエリの後発クエリを、ロックされているレコードへ外部キーを変更する`UPDATE`にする

Aurora: デッドロック検知されず止まり続ける
MySQL: デッドロックを検知してロールバック

事前に流す

INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);
INSERT INTO offer_child_2 (offer_id) VALUES (2);

offer_child_1のid=1は10の検証で、
offersのid=2は11の検証で、
すでに作ったからここでは作らない

transaction1

-- id確認
-- SELECT * FROM document_offer_child_2 WHERE offer_child_2_id = 1;
BEGIN;
SELECT * from offers WHERE id = 2 LIMIT 1 FOR UPDATE;
UPDATE document_offer_child_2 SET offer_child_2_id = 2 WHERE id = 1;

transaction2

-- id確認
-- SELECT * FROM offer_child_1 WHERE offer_id = 1;
BEGIN;
SELECT * FROM offer_child_2 WHERE id = 2 LIMIT 1 FOR UPDATE;
UPDATE offer_child_1 SET offer_id = 2 WHERE id = 1;

結果

MySQLでもAuroraでも外部キーの参照先がIXを取られていると
INSERTとUPDATE(外部キー変更を含む時のみ)はロック待ちの時、タイムアウトしない
そして後発クエリがINSERTだけでなく、UPDATE(外部キー変更を含む時のみ)の場合も
Auroraはデッドロックを検知してくれない(MySQLはデッドロック検知してくれる)

さらに2点気になったので追加で検証

ref: https://dev.mysql.com/doc/refman/5.6/ja/innodb-foreign-key-constraints.html

InnoDBのINSERT時の挙動は外部キー参照先にISを取るという処理はAuroraも同じだろうか
だったらIS獲得されたレコードに対する外部キー参照のINSERTは待たずに実行されるよね？

あと
ref: https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

INSERT時にinnodb_lock_wait_timeoutが効かないってことは参照先をテーブルロックしようとしている可能性もある？ってことでこれらも一応検証してみる

長いので折りたたんだ

13. 親レコードのIS取って、それを参照する`INSERT`を書いてみる

Aurora: 待たずにINSERTができた
MySQL: 待たずにINSERTができた
考察: INSERT時の外部キー参照先に取るロックはISで間違いなさそう

transaction1

BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;

transaction2

BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

14. `INSERT`したあと、そのレコードの外部キー参照先のテーブルの別レコードを`IX`でロックしてみる

Aurora: 待たずにSELECTできた
MySQL: 待たずにSELECTできた
考察: つまり参照先がテーブルロックの可能性もない

transaction1

BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

transaction2

BEGIN;
SELECT * FROM offers WHERE id = 2 FOR UPDATE;

結果

予想通り、かつInnoDBのドキュメント通り外部キーの参照先へのロックはISだった

ここまでの結果をまとめて考察すると

INSERT時は外部キー参照先にはIS(行レベル共有ロック)を取るだけ
外部キー変更のないUPDATEであれば外部キー参照先にIS取らないので参照先がロック取られてても待ちなく更新可能
MySQLでもAuroraでも、外部キーの参照先が先にロックが取られているときは外部キー参照を持つINSERTや外部キーの変更があるUPDATEのロック待ちはタイムアウトしない
そしてAuroraでは後発クエリがそのようなINSERTやUPDATEの時に発生するデッドロックはDBは検知してくれない
MySQLならそのようなデッドロックでも検知してロールバックしてくれる

ということがわかった

MySQL互換Auroraでの外部キーを持つINSERTや外部キーの変更を行うUPDATEのロック待ちの挙動が危うそうなのでアプリケーションレイヤーでも実装時に以下のようなことに気をつけた方がいいかもしれない

今回の事象の解決法としてはtransaction2がINSERTの外部キー参照先(offers)を最初にロック取りに行けばデッドロックは起きなくなる
- ロックを取る順番を合わせるっていう基本的なデッドロック対策ですね
  - 外部キー参照先のIS取ればシンプルに今回のデッドロックが解決
  - IXだと同時にINSERTやUPDATEできなくなって同時アクセス時に待ちが発生する
  - どっちを取るかはケースバイケース
- INSERTやUPDATEだけのトランザクションであれば気にする必要はないが他のレコードもロックするような場合は注意が必要
- 親レコードを更新して子レコード(外部キー持っている)更新するような処理があるとINSERT/UPDATE側が親をロックするようにする以外に回避策なさそう？
中間テーブルのような外部キー参照が多いテーブルが多く、そこへのINSERTや外部キー変更のUPDATEも多く、さらにその参照先もロックを取る処理があるアプリケーションは気をつけて実装しないと検知されないデッドロックで死ぬ可能性が高そう
スループットは落ちるがあえてMySQLを使うと少し安全かもしれない

ちなみにRailsでも共有ロック(`IS`)取れる

ただし、MySQL依存になってちょっと悲しい

Model.lock('LOCK IN SHARE MODE').find(...)

ref: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html

タイムアウトしないとかデッドロック検知しない問題、何か解決法とか知ってる方いたらぜひコメントで教えて頂けると超助かります
※「このパラメータを設定すれば解決するよ」とかいうオチだったら恥ずかしすぎる。。。

おまけ編 PostgreSQL互換Auroraでも検証

長いので折りたたんだ

スキーマ

CREATE TABLE offers (
  id SERIAL PRIMARY KEY,
  title text
);
CREATE TABLE offer_child_1 (
  id SERIAL PRIMARY KEY,
  title text,
  offer_id bigint,
  FOREIGN KEY (offer_id) REFERENCES offers(id)
);
CREATE TABLE offer_child_2 (
  id SERIAL PRIMARY KEY,
  offer_id bigint,
  FOREIGN KEY (offer_id) REFERENCES offers(id)
);
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  url text
);
CREATE TABLE document_offer_child_2 (
  id SERIAL PRIMARY KEY,
  document_id bigint,
  offer_child_2_id bigint,
  FOREIGN KEY (document_id) REFERENCES documents(id),
  FOREIGN KEY (offer_child_2_id) REFERENCES offer_child_2(id)
);

事前に流すクエリ

INSERT INTO offers (title) VALUES (CURRENT_TIMESTAMP);
INSERT INTO offer_child_2 (offer_id) VALUES (1);
INSERT INTO documents(url) VALUES ('http://example.com');

15. 最初のデッドロックのパターンを試す

Aurora: transaction2の後発クエリでロック待ちが発生しなかった
Postgres: transaction2の後発クエリでロック待ちが発生しなかった
考察: Postgresは行ロックの種類が多くUPDATEとINSERTが競合しないようだ
ref: https://www.postgresql.jp/document/9.6/html/explicit-locking.html#locking-rows

transaction1

BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

16. デッドロック再現のため`UPDATE`を`SELECT FOR UPDATE`に変える

Aurora: デッドロックを検知した
Postgres: デッドロックを検知した

transaction1

BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 FOR UPDATE;
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 1 LIMIT 1 FOR UPDATE;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

17. 後発クエリを外部キー変更のあるUPDATEにしてみる

データ用意

INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
INSERT INTO offers (title) VALUES (CURRENT_TIMESTAMP);
INSERT INTO document_offer_child_2 (document_id, offer_child_2_id) VALUES (1, 1);
INSERT INTO offer_child_2 (offer_id) VALUES (2);

Aurora: デッドロックを検知した
Postgres: デッドロックを検知した

transaction1

BEGIN;
SELECT * FROM offers WHERE id = 2 LIMIT 1 FOR UPDATE;
UPDATE document_offer_child_2 SET offer_child_2_id = 2 WHERE id = 1;

transaction2

BEGIN;
SELECT * FROM offer_child_2 WHERE id = 2 LIMIT 1 FOR UPDATE;
UPDATE offer_child_1 SET offer_id = 2 WHERE id = 1;

18. `SELECT FOR UPDATE`への`INSERT`の待ちを検証

lock_timeoutを設定したかったがパラメータグループに項目がなく設定できなかったのでコンソールから設定
(これAWS RDSだと設定永続化できないのかな。。。)
postgresの`lock_timeoutはmsecなので3000(=3sec)で

Aurora: 3秒でロックがタイムアウトした
Postgres: 3秒でロックがタイムアウトした

transaction1

SET lock_timeout = 3000;
BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 FOR UPDATE;

transaction2

SET lock_timeout = 3000;
BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');

19. `SELECT FOR UPDATE`への`UPDATE`の待ちを検証

Aurora: 3秒でタイムアウトした
Postgres: 3秒でタイムアウトした

transaction1

SET lock_timeout = 3000;
BEGIN;
SELECT * FROM offers WHERE id = 2 LIMIT 1 FOR UPDATE;

transaction2

SET lock_timeout = 3000;
BEGIN;
UPDATE offer_child_1 SET offer_id = 2 WHERE id = 1;(3)

結果

Postgresの方がロックのモードが多彩で暗黙的なロックで競合しづらいようなので安心かもしれない
また、Postgres互換のAuroraやPostgresでは
外部キーが絡んだINSERTやUPDATEでのロック待ちはちゃんとタイムアウトするし
デッドロックが検知されない問題も起きなかった

結論

パターンとして見落としがあるかもしれないのでこれで完璧とは言えないが

MySQL互換のAuroraもMySQLも外部キーが絡んだINSERTやUPDATEでタイムアウトしないロック待ちが発生する
MySQL互換のAuroraではさらにそのINSERT/UPDATEによるデッドロックが検知されない
Postgres互換のAuroraやPostgresはINSERT/UPDATEのデッドロック検知されたし
- 外部キーが絡んだINSERTやUPDATEによる待ちもちゃんとタイムアウトする
- 行ロックの種類が多いのでMySQLよりもそもそも競合しづらい

デッドロック発生しやすさや発生時の挙動など考えて安全度が高そうなのは
Postgres互換Aurora = Postgres > MySQL > MySQL互換Aurora
といった感じかな

雑な追記

ふと検証してないことに気づいて
Aurora(MySQL互換)で外部キー参照先がロックされている状態でのDELETEを試したところ
正常にロックタイムアウトされた

2トランザクション2クエリでデッドロックを起こすパターンも
後続クエリを外部キーを先にロックされたDELETEにして試したが
両方のトランザクションともロックタイムアウトしたのでちょっと予想外だったけどまぁ問題ない挙動
やはりINSERT/UPDATEだけが異質

Aurora(MySQL互換)で、外部キーが絡んだINSERT/UPDATEによるデッドロックが検知されない問題

何が起きたのか

デッドロック発生後の対処

検証開始

デッドロックが起きていたテーブル群

スキーマ

デッドロックが起きていたトランザクションのクエリ

事前に流すクエリ

トランザクション

なぜデッドロックが起きるのか

各環境での挙動

MySQL互換のAurora(5.7.12)

MySQL(5.7.16)(5.7.12がなかったので仕方なく。。。)

Auroraの他のパターンのデッドロックでの検知の挙動を検証

1. transaction1の先発クエリもSELECT FOR UPDATEにしてみる

2. transaction2の先発クエリもUPDATEにしてみる

3. transaction1の後発クエリをSELECT LOCK IN SHARE MODEにしてみる

4. transaction2の後発クエリをSELECT LOCK IN SHARE MODEにしてみる

ここまでで

5. transaction1/2の後発クエリをSELECT LOCK IN SHARE MODEにしてみる

6. transaction1/2の後発クエリをUPDATEにしてみる

7. transaction1/2の後発クエリをDELETEにしてみる

8. transaction1/2の先発クエリをINSERTにしてみる

結果

気になることがあったので追加で検証

AuroraとMySQLのINSERTとUPDATEのロック待ちの挙動を検証

9. IX獲得されたレコードを参照するINSERTをする

外部キーを持つレコードへのUPDATE時の待ちの挙動を検証

10. UPDATE対象レコードの外部キーは変更せずリレーションがないカラムを更新する

11. UPDATE対象レコードの外部キーを変更すると

12. デッドロック発生するクエリの後発クエリを、ロックされているレコードへ外部キーを変更するUPDATEにする

結果

さらに2点気になったので追加で検証

13. 親レコードのIS取って、それを参照するINSERTを書いてみる

14. INSERTしたあと、そのレコードの外部キー参照先のテーブルの別レコードをIXでロックしてみる

結果

ここまでの結果をまとめて考察すると

ちなみにRailsでも共有ロック(IS)取れる

おまけ編 PostgreSQL互換Auroraでも検証

スキーマ

15. 最初のデッドロックのパターンを試す

16. デッドロック再現のためUPDATEをSELECT FOR UPDATEに変える

17. 後発クエリを外部キー変更のあるUPDATEにしてみる

18. SELECT FOR UPDATEへのINSERTの待ちを検証

19. SELECT FOR UPDATEへのUPDATEの待ちを検証

結果

結論

雑な追記

1. transaction1の先発クエリも`SELECT FOR UPDATE`にしてみる

2. transaction2の先発クエリも`UPDATE`にしてみる

3. transaction1の後発クエリを`SELECT LOCK IN SHARE MODE`にしてみる

4. transaction2の後発クエリを`SELECT LOCK IN SHARE MODE`にしてみる

5. transaction1/2の後発クエリを`SELECT LOCK IN SHARE MODE`にしてみる

6. transaction1/2の後発クエリを`UPDATE`にしてみる

7. transaction1/2の後発クエリを`DELETE`にしてみる

8. transaction1/2の先発クエリを`INSERT`にしてみる

AuroraとMySQLの`INSERT`と`UPDATE`のロック待ちの挙動を検証

9. `IX`獲得されたレコードを参照する`INSERT`をする

外部キーを持つレコードへの`UPDATE`時の待ちの挙動を検証

12. デッドロック発生するクエリの後発クエリを、ロックされているレコードへ外部キーを変更する`UPDATE`にする

13. 親レコードのIS取って、それを参照する`INSERT`を書いてみる

14. `INSERT`したあと、そのレコードの外部キー参照先のテーブルの別レコードを`IX`でロックしてみる

ちなみにRailsでも共有ロック(`IS`)取れる

16. デッドロック再現のため`UPDATE`を`SELECT FOR UPDATE`に変える

18. `SELECT FOR UPDATE`への`INSERT`の待ちを検証

19. `SELECT FOR UPDATE`への`UPDATE`の待ちを検証