RDB のデッドロックとロックについて

この記事は、しっかり理解していないと、場合によってはシステム障害になってしまいかねない RDB のデッドロックについて、基本を説明する機会があったので、調べ直したついでにまとめた記事になります。

デッドロックとは

そもそも、広い意味でのデッドロックとは。

2つ以上の処理がお互いの処理が終わるのを待ち待機状態になって、結果としてどちらの処理も先に進めなくなってしまう状態のことです。

RDB でデッドロックが発生するとき

よくあるデッドロック

RDS では、２つ以上のトランザクションが、お互いが必要なテーブルのロックを取得し、どちらも解放待ちになってしまい、処理が止まってしまうデッドロックが一般的です。

RDB でデッドロックが発生すると処理がストップしてしまい、システム障害の原因になってしまうこともあります。

トランザクションとロックについてわかっていないとイマイチピンとこないと思うので、
ここからは、トランザクションとロックについて確認していきます。

トランザクションとは

RDB におけるトランザクションは、データベースにおける複数の処理（作成、更新、削除）などをまとめて、１つの処理として実行する仕組みのことです。

一連の処理が全て成功した場合に成功とし、途中でどこかの失敗した場合には失敗として、一連の全ての処理を失敗の扱いとします。
そうすることで、一連の処理が途中で止まってしまった場合などに、整合性を担保することができます。

例えば、送金のシステムなどで、
A さんの口座 から B さんの口座 に 1,000 円送金した際、振り込みの処理が失敗し、A さんの口座 からは 1,000 円マイナスされたけど、 B さんの口座の金額は増えていない、なんてことになれば大変です。

送金と、振り込みの処理を１つのトランザクションとしてまとめておけば、振り込み処理が失敗した場合には、送金処理も失敗として整合性を保つことができます。

ACID 特性

トランザクションが持つべき特性として ACID 特性というものが定められています。

■ Atomicity（原子性）

トランザクションに含まれる処理は、全て実行されるか、全て実行されないことを保証するものです。

先ほどの口座の例で言うと、送金処理と振り込み処理全てが完了して、トランザクションの処理は完了となります。
どれか１つでも失敗すれば失敗として、送金処理も実行されません。

■ Consistency（一貫性）

トランザクション開始と終了時にデータの整合性を維持していることを保証するものです。

口座テーブルに口座はマイナスの値を取らないというルールがあれば、トランザクションが完了した際に、 A さんの口座がマイナスになっているような処理はできないということです。

他の特性については、DBが担保してくれていますが、一貫性についてはプログラムでも担保する必要がある内容になります。

■ Isolation（独立性）

トランザクションの実行中は他からの操作の影響を受けないことを保証するものです。

ここの挙動はトランザクション分離レベルというものによって変わってきます。ここでは深く触れませんので、トランザクションの実行中は他からの影響を受けないんだなくらいに思っておいてください。

■ Durability（永続性）

トランザクションが正常に完了した結果は永続的にデータベースへ保存されます。

トランザクションを実際に試してみる

Docker で MySQL を用意します。

docker container run \
    --name db \
    --rm \
    --detach \
    --publish 13306:3306 \
    --env MYSQL_ROOT_PASSWORD=root \
    --env MYSQL_USER=user \
    --env MYSQL_PASSWORD=password \
    --env MYSQL_DATABASE=study_deadlock \
    mysql:8

# コンテナにアクセス
docker exec -it db bash


# mysql へアクセス
mysql -u root -proot study_deadlock

テーブルとデータを作成

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16) NOT NULL,
    balance INT NOT NULL
);


INSERT INTO accounts (name, balance) VALUES
('A-san', 10000),
('B-san', 10000);


SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |   10000 |
|  2 | B-san |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)

トランザクションを試してみる

トランザクションの処理を切り戻（ロールバック）したい場合

MySQL では BEGIN コマンドでトランザクションを開始し、何かしらのエラーが発生した場合などはROLLBACK コマンドで切り戻すことができます。

-- トランザクションの開始
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE name = 'A-san';

SELECT * FROM accounts;

+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
|  2 | B-san |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)

-- なんらかのエラーが発生するなどして、 ROLLBACK で切り戻したい場合

ROLLBACK;

SELECT * FROM accounts;

+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |   10000 |
|  2 | B-san |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)

コミットしてトランザクションを正常に完了させる

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE name = 'A-san';

UPDATE accounts SET balance = balance + 1000 WHERE name = 'B-san';

SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
|  2 | B-san |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

-- COMMIT でトランザクションの処理を確定させる
COMMIT;

SELECT * FROM accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
|  2 | B-san |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)

このようにトランザクションでは複数の処理を１つの単位にまとめて、全て正常に完了した場合にのみ結果を反映させることができます。

ロックとは

次にもう１つの重要な要素であるロックについて見ていきます。

共有ロックと排他ロック

DB のロックには主に、共有ロックと排他ロックの２種類があります。

• 共有ロック

  • ロック対象への参照以外のアクセスを禁止する
    • SELECT でのアクセスは可能
    • 読み込みロック、S lock などとも呼ばれる

• 排他ロック

  • ロック対象への全てのアクセスを禁止する
    • SELECT UPDATE DELETE などとにかく全部アクセス禁止
    • 書き込みロック、X lock などとも呼ばれる

ざっくり言うと、読み込みだけなら許してあげるか、読み込みすら許さないかの違いです。

概念的な、共有ロック/排他ロックと RDBMS における共有ロック/排他ロックの違い

この辺り正直あまり自信はないですが、

概念的なロックと MySQL などの RDBMS で言われるロックは若干ニュアンスが違うようです。

概念的な共有ロック/排他ロックは、ロックをとっていると他からの参照や、更新をブロックするように書かれていることが多いです。

対して、 RDBMS での共有ロック/排他ロックは、他からのロックをどの程度許容するか。という観点になります。

例えば、MySQL のデフォルトのトランザクション分離レベル（REPEATABLE READ）では、 「SELECT ... FOR UPDATE」 で排他ロックが取れますが、他からの参照（単純な SELECT ）は可能です。

概念的な排他ロックの話だと、排他ロックをかけている時点で、他からの参照はできないように思いますが、 MySQL で扱われる排他ロックは、他からの共有ロックと排他ロックをブロックするという挙動をしています。

デフォルトのトランザクション分離レベルでは、単純な SELECT では共有ロックを取らないため、排他ロックのブロックに引っ掛からず、実行することができるようです。

トランザクション分離レベルを SERIALIZABLE に変更すると、SELECT でも共有ロックを取るようになるため、排他ロックがかかっていると参照もできない、概念的なロックのイメージと同じ挙動になるそうです。

なぜこのような挙動になっているかというと、おそらくですが、実際のプログラムでは同時に複数のアクセスがあり、ある程度並列な処理に対応できるようにロックの判定を緩くできるようにするためだと思います。

表ロックと行ロック

• 表ロック
  • テーブル内のすべての行が対象になりロックされる
  • テーブルロックとも呼ばれる
• 行ロック
  • 行単位でロックをかける、１行の場合もあれば複数行になる場合もある
  • レコードロックとも呼ばれる

例えば、　共有ロック + 行ロック　であれば、対象の行は他のトランザクションから参照はできるが、更新はできない。
排他ロック + 表ロック であれば、テーブル全体が他のトランザクションからは参照も更新もできない状態になります。

ロックを試してみる

共有ロック + 行ロック

MySQL では SELECT ... FOR SHARE をつけることで、読み取られる行に共有モードロックを設定することができます。

mysql> begin;
mysql> SELECT * FROM accounts WHERE id=1 FOR SHARE;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
+----+-------+---------+
1 row in set (0.00 sec)

この時点でのロックの状況を確認してみます。

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+---------------+-------------+-----------+
| accounts    | TABLE     | IS            | GRANTED     | NULL      |
| accounts    | RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

performance_schema.data_locks の中身については本筋ではないので、ここではあまり深くは触れませんが、LOCK_TYPE=RECORD、LOCK_MODE=S,REC_NOT_GAP、LOCK_DATA=1 となっていることから id=1 のデータに 共有レコードロック + 行ロック がかかっていることがわかります。

別タブで同じ用に MySQL にアクセスして、他のトランザクションからアクセス可能かどうかを確認してみます。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM accounts WHERE id=1 FOR SHARE;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
+----+-------+---------+
1 row in set (0.00 sec)

SELECT は行うことができました。 UPDATE はどうでしょうか？

mysql> UPDATE accounts SET balance = 10000 WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

処置が完了せずに待機状態になってしまい、しばらくして次のようなエラーが返ってきました。

mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+---------------+-------------+-----------+
| accounts    | TABLE     | IX            | GRANTED     | NULL      |
| accounts    | RECORD    | X,REC_NOT_GAP | WAITING     | 1         |
| accounts    | TABLE     | IS            | GRANTED     | NULL      |
| accounts    | RECORD    | S,REC_NOT_GAP | GRANTED     | 1         |
+-------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)

LOCK_DATA=1 に LOCK_STATUS=WAITING が増えていますね。

MySQL では UPDATE 時にレコードに対しての排他ロックを取得するため、そちらが実行待ちの状態になっていることが確認できます。

今かかっているのは id = 1 に対しての行ロックなので、 id = 2 は更新することができます。

mysql> UPDATE accounts SET balance = 12000 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

元のタブでトランザクションを終了すれば、 id = 1 も更新できるようになります。

排他ロック + 行ロック

MySQL では SELECT ... FOR UPDATE をつけることで、 UPDATE を実行した場合と同じように、読み取られる行に排他ロックをつけることができます。

mysql> SELECT * FROM accounts WHERE id=1 FOR UPDATE; 
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
+----+-------+---------+
1 row in set (0.01 sec)

前回と同じように別タブから SELECT ... FOR SHARE を実行します。

mysql> SELECT * FROM accounts WHERE id=1 FOR SHARE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

こちらも先ほどの共有ロックと同じように、待機時間が発生した後、タイムアウトのエラーが返ってきました。

行ロックなので、他の id のデータは確認することができます。

mysql> SELECT * FROM accounts WHERE id=2 FOR SHARE;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  2 | B-san |   12000 |
+----+-------+---------+
1 row in set (0.01 sec)

排他ロック + 表ロック

次は表ロックについてみてみます。
先ほどと同じように SELECT ... FOR UPDATE を実行してみます。

mysql> SELECT * FROM accounts WHERE name='A-san' FOR UPDATE;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
+----+-------+---------+
1 row in set (0.00 sec)

別タブで同じく A-san のレコードを取得しようとします。

mysql> SELECT * FROM accounts WHERE name='A-san' FOR SHARE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

こちらは、行ロックの場合と同じように、取得できませんでした。
次に B-san のレコードを取得してみます。

mysql> SELECT * FROM accounts WHERE name='B-san' FOR SHARE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

今回は B-san のレコードも取得することができませんでした。

前回の行ロックとの違いはなんなのかというと、 WHERE での検索内容の違いになります。
（今回は SELECT ですが、UPDATE の場合でも同じ）

行ロックではインデックスの効いた id カラムで検索をしていましたが、今回はインデックスが効いていない name カラムで検索しています。

インデックスについては前に書いた別記事を参照してください。
https://qiita.com/tkek321/items/a5db528a9666360075d1

インデックスを効かない検索をすると、テーブルの全件が検索対象になってしまいます。
MySQL のデフォルトのトランザクション分離レベルでは、検索対象になったものにはロックをかけてしまうらしいので、今回はテーブル全体がロックされ、表ロック（テーブルロック）がかかってしまいました。

ロックの挙動は RDB の種類やトランザクション分離レベルによって変わってくるため注意が必要です。

RDB の種類によるロックの違いでは、PostgreSQL では SELECT でもロックを取得したり、
MySQL のネクストキーロックやギャップロックなどがあります。

トランザクション分離レベルによる違いでは、MySQL では分離レベルが serializable なら SELECT 時に自動的に共有ロックを取りますが、分離レベルが repeatable read 以下なら SELECT 時に自動的に共有ロックを取らないなどの違いがあったりするそうです。

この辺りの話をするとかなり長くなりそうなので、別の機会に調べます。

デッドロックを試してみる

ここまで理解できたら、最後にデッドロックを実際に試してみたいと思います。

今の accounts テーブルはこんな感じになっています。

mysql> select * from accounts;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | A-san |    9000 |
|  2 | B-san |   12000 |
+----+-------+---------+
2 rows in set (0.01 sec)

今回の処理のイメージとしては、

• A　さんから B さんに 1,000 円が振り込まれる処理①
• B さんから A さんに 2,000 円が振り込まれる処理②

これの処理①、②が同時に動いている状態です。

では、まず処理①の A さんの口座から残高を引く処理を実行します。

mysql> begin;
-- トランザクション１
mysql> UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

別トランザクション（別タブ）で、 処理②の B さんの口座から残高を引く処理を実行します。

mysql> begin;
-- トランザクション2
mysql> UPDATE accounts SET balance = balance - 2000 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

トランザクション１のタブに元って B さんの口座に 1,000 円を振り込む処理をします。

-- トランザクション１
mysql> UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- 待ち状態になる

トランザクション２のタブで A さんの口座に 2,000 円振りこむ処理をします。

-- トランザクション2
mysql> UPDATE accounts SET balance = balance + 2000 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

デッドロックが発生しました。

デッドロックが発生し、一番初めに説明したような状態になっています。

画像はテーブルになっていますが、今回はレコード単位で同じことが起こっています。
先ほど説明した、表ロックか、行ロックの違いです。

MySQL では設定によってデッドロックの発生を自動で検知して、片方の処理を自動でロールバックし、ロックを解放してくれるようです。

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

先ほどのトランザクション１は、トランザクション２でデッドロックのエラーメッセージが表示されたあと、
ロック待ちから解放され、処理が進んでいます。

--　トランザクション1
mysql> UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
Query OK, 1 row affected (8.67 sec)
Rows matched: 1  Changed: 1  Warnings: 0

デッドロックの回避について

デッドロックを回避する方法には、

• 複数のレコードを更新する場合は更新する順番を揃える
• ロックの範囲を小さくする

などがあります。

ロックの範囲を広く取りすぎると、他の処理がその間待ち状態になってしまったり、デットロックのリスクが増えるので、UPDATE 文を実行する際にも、インデックスが効いた条件指定になっているかなど、ロックの範囲が小さくなるよう意識した方が良さそうです。

本番環境でのみ、特定の処理がたまに落ちるとか、あるバッチが動いている時だけ、特定の処理が動かなくなるなどがあれば、デッドロックが起こっているせいかもしれません。

終わり

デッドロックについてまとめようと思ったらほとんどロックの話になってしまいました。
RDB のロックは RDBMS の種類によって変わったり、トランザクション分離レベルによって変わるので、かなり複雑なようです。

参考