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

# 何が起きたのか
たまーに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

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

### スキーマ
```sql
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)
);
```

## デッドロックが起きていたトランザクションのクエリ
### 事前に流すクエリ
```sql
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、２間でロックが交錯しデッドロックが発生する

## 各環境での挙動

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

```conf:Aurora
innodb_lock_wait_timeout = 10
lock_wait_timeout = 15
```

```conf: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)
```sql: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);
```

```sql: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がなかったので仕方なく。。。)
```sql: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)
```

```sql: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交互

<details><summary> 長いので折りたたんだ</summary><div>
全パターンを網羅しようとすると、
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`は関係なさそう**

```sql: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);
```

```sql: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`は関係なさそう**

```sql: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);
```

```sql: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`じゃないと検知しないデッドロックにならない？**

```sql: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;
```

```sql: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)か**

```sql: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);
```

```sql: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`だとデッドロック検知してくれるらしい**

```sql: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;
```

```sql: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`でもデッドロック検知してくれるらしい**

```sql:transaction1
BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
UPDATE offer_child_2 SET offer_id = 1 WHERE id = 1;
```

```sql: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`だけがやはり特殊なのか**

```sql:transaction1
BEGIN;
UPDATE offers SET title = CURRENT_TIMESTAMP WHERE id = 1;
DELETE from offer_child_2 WHERE id = 1;
```

```sql: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`が後発であることに意味があるらしい**

```sql: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;
```

```sql: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;
```

</div></details>

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

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

## AuroraとMySQLの`INSERT`と`UPDATE`のロック待ちの挙動を検証
<details><summary> 長いので折りたたんだ</summary><div>

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

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

```sql:transaction1
BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 FOR UPDATE;
```

```sql:transaction2
BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
```

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

### 10. UPDATE対象レコードの外部キーは変更せずリレーションがないカラムを更新する
Aurora: **待ちなしで更新できた**
MySQL: **待ちなしで更新できた**

```sql:まず普通にデータ作る
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
```

```sql:transaction1
BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 FOR UPDATE;
```

```sql: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: **待ちが発生 タイムアウトもせず**

```sql:事前に
INSERT INTO offers (title) VALUES (CURRENT_TIMESTAMP);
```

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

```sql: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: **デッドロックを検知してロールバック**

```sql:事前に流す
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の検証で、
すでに作ったからここでは作らない

```sql: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;
```

```sql: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;
```

</div></details>

### 結果
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`が効かないってことは参照先をテーブルロックしようとしている可能性もある？
ってことでこれらも一応検証してみる

<details><summary> 長いので折りたたんだ</summary><div>

### 13. 親レコードのIS取って、それを参照する`INSERT`を書いてみる
Aurora: **待たずに`INSERT`ができた**
MySQL: **待たずにINSERTができた**
考察: **`INSERT`時の外部キー参照先に取るロックは`IS`で間違いなさそう**

```sql:transaction1
BEGIN;
SELECT * FROM offers WHERE id = 1 LIMIT 1 LOCK IN SHARE MODE;
```

```sql:transaction2
BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
```

### 14. `INSERT`したあと、そのレコードの外部キー参照先のテーブルの別レコードを`IX`でロックしてみる
Aurora: **待たずに`SELECT`できた**
MySQL: **待たずに`SELECT`できた**
考察: **つまり参照先がテーブルロックの可能性もない**

```sql:transaction1
BEGIN;
INSERT INTO offer_child_1 (offer_id, title) VALUES (1, 'a');
```

```sql:transaction2
BEGIN;
SELECT * FROM offers WHERE id = 2 FOR UPDATE;
```

</div></details>

### 結果
予想通り、かつ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依存になってちょっと悲しい

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

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

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


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

<details><summary> 長いので折りたたんだ</summary><div>

### スキーマ
```sql
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)
);
```

```sql:事前に流すクエリ
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

```sql: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);	
```

```sql: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: **デッドロックを検知した**

```sql: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);	
```

```sql: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にしてみる

```sql:データ用意
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: **デッドロックを検知した**

```sql: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;
```

```sql: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秒でロックがタイムアウトした**

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

```sql: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秒でタイムアウトした**

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

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

</div></details>

### 結果
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だけが異質