アプリエンジニアですが今回はサーバーサイドのSQLの話を書きます。
テーブルの集計結果を効率的に取得するために、結果を格納した「サマリーテーブル」が作られる事は多いと思います。サマリーテーブルは効率的ですが、一方で実際の集計結果とずれてしまっているというケースも見かけます。
そこで、サマリーテーブルをどのように実装すると何が起こるかを一通り試してみました。
会社で使っているのがMySQLなのでMySQL(InnoDB)を中心に書きますが、PostgreSQLとの比較もしています。バージョンは MySQL 8.0.13、PostgreSQL 11.0 で、両方とも Docker のイメージを使用しています。
シナリオ1: レコード数を格納する
以下のような records というテーブルと summary というサマリーテーブルを例にとって考えます。
CREATE TABLE records(
id INTEGER NOT NULL PRIMARY KEY
);
-- サマリーテーブル。行数を格納
CREATE TABLE summary(
count INTEGER NOT NULL
);
INSERT INTO summary VALUES(0);
records にレコードを追加・削除した場合には、例えば以下のように summary.count の整合性を保つ必要があります。
-- records を insert したら summary.count を調整する
insert into records values(10);
update summary set count = count + 1;
同時に records を insert する
上の処理を複数から同時に行ったらどうなるかを試してみます。
|mysql> begin; ||mysql> begin;
|Query OK, 0 rows affected (0.00 sec) ||Query OK, 0 rows affected (0.00 sec)
| ||
|mysql> insert into records values(10); ||
|Query OK, 1 row affected (0.00 sec) ||
| ||mysql> insert into records values(20);
| ||Query OK, 1 row affected (0.00 sec)
|mysql> update summary set count = count + 1; ||
|Query OK, 1 row affected (0.01 sec) ||
|Rows matched: 1 Changed: 1 Warnings: 0 ||
| ||mysql> update summary set count = count + 1;
|mysql> commit; || 🔺🔺🔺 ロックが発生🔺🔺🔺
|Query OK, 0 rows affected (0.04 sec) ||Query OK, 1 row affected (14.58 sec)
| ||Rows matched: 1 Changed: 1 Warnings: 0
| ||mysql> commit;
| ||Query OK, 0 rows affected (0.10 sec)
右のトランザクションで update すると、待ちが発生します。この待ちは左側が commit したタイミングで完了します。
records には二つ挿入され、 summary.count も正しい値を保持しています。
mysql> select * from records;
+----+
| id |
+----+
| 10 |
| 20 |
+----+
2 rows in set (0.00 sec)
mysql> select * from summary;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
PostgreSQL でも同様です。
タイムアウトした場合
先ほどのロックのタイミングで左側が commit しないとどうなるでしょう?
|mysql> begin; ||mysql> begin;
|Query OK, 0 rows affected (0.00 sec) ||Query OK, 0 rows affected (0.00 sec)
| ||
|mysql> insert into records values(10); ||
|Query OK, 1 row affected (0.00 sec) ||
| ||mysql> insert into records values(20);
| ||Query OK, 1 row affected (0.00 sec)
|mysql> update summary set count = count + 1; ||
|Query OK, 1 row affected (0.00 sec) ||
|Rows matched: 1 Changed: 1 Warnings: 0 ||
| ||mysql> update summary set count = count + 1;
🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺 ここまでは一緒 🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺🔺
|mysql> || ◀︎ ◀︎ ◀︎ 左の修正をしばらくコミットをしない
| ||
| ||🔻🔻🔻 タイムアウトしてしまう 🔻🔻🔻
| ||ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
| ||mysql>
|mysql> commit; ||
|Query OK, 0 rows affected (0.09 sec) ||
ロックが長い間続くとタイムアウトします。DBの値はどうなっているでしょうか?
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from records;
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
mysql> select * from summary;
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
右側のトランザクションをロールバックすれば、成功した records への insert もなかった事になります。
今回の場合、右側をコミットしてしまうと insert だけ実行されて整合性が失われてしまいます1。基本的には失敗したらロールバックするのが無難でしょう。
PostgreSQL の場合
デフォルトでタイムアウトが発生しないのですが、あらかじめ以下のようにセットする事でそのセッションのタイムアウトをセットできます。
set lock_timeout = 5000; -- ロック待ち5秒でタイムアウト
同時に insert して summary.count を更新してみます。
|postgres=# begin; ||postgres=# begin;
|BEGIN ||BEGIN
|postgres=# insert into records values(10); ||
|INSERT 0 1 ||
| ||postgres=# insert into records values(20);
| ||INSERT 0 1
|postgres=# update summary set count = count + 1||
|; ||
|UPDATE 1 ||
| ||postgres=# update summary set count = count + 1;
|postgres=# || ◀︎ ◀︎ ◀︎ 左の修正をしばらくコミットをしない
| ||
| ||🔻🔻🔻 タイムアウトしてしまう 🔻🔻🔻
| ||ERROR: canceling statement due to lock timeout
| ||CONTEXT: while updating tuple (0,4) in relation "summary"
|postgres=# commit; ||
|COMMIT ||
| ||postgres=# commit;
| ||ROLLBACK
InnoDB とほぼ同じ動きをしましたが、エラー時にトランザクションごとロールバックされ、commitしても何も起きません。
トリガーでサマリーテーブルを更新する
サマリーテーブルの更新し忘れを防ぐために、トリガーを使うケースもあると思います。
CREATE TRIGGER records_synchronize_counter
AFTER INSERT ON records
FOR EACH ROW
UPDATE summary SET count = count + 1;
この場合、レコードを同時に追加してエラーが発生したらどうなるでしょう? 上と同じように試してみます。
|mysql> begin; ||mysql> begin;
|Query OK, 0 rows affected (0.00 sec) ||Query OK, 0 rows affected (0.00 sec)
| ||
|mysql> insert into records values(10); ||
|Query OK, 1 row affected (0.02 sec) ||
| ||
| ||mysql> insert into records values(20);
|mysql> || ◀︎ ◀︎ ◀︎ 左の修正をしばらくコミットをしない
| ||
| ||🔻🔻🔻 タイムアウトしてしまう 🔻🔻🔻
| ||ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
| ||mysql>
|mysql> commit; ||
|Query OK, 0 rows affected (0.02 sec) ||
今度は右側の insert でエラーが発生します。値はどうなっているでしょう?
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from records;
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
mysql> select * from summary;
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
期待通り、きちんとロールバックされています。
Auto Commit の中でトリガーが実行された場合
ちょっとアレなケースとして、トランザクションを使わずに Auto Commit の状態で同様にエラーが発生した場合を考えてみます。
|mysql> begin; || 🔻🔻🔻 トランザクションを開始しない 🔻🔻🔻
|Query OK, 0 rows affected (0.00 sec) ||
| ||
|mysql> insert into records values(10); ||
|Query OK, 1 row affected (0.00 sec) ||
| ||
| ||mysql> insert into records values(20);
|mysql> || ◀︎ ◀︎ ◀︎ 左の修正をしばらくコミットをしない
| ||
| ||🔻🔻🔻 タイムアウトしてしまう 🔻🔻🔻
| ||ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
|mysql> commit; ||
|Query OK, 0 rows affected (0.05 sec) ||
レコードを見てみます。
mysql> select * from records;
+----+
| id |
+----+
| 10 |
+----+
1 row in set (0.01 sec)
mysql> select * from summary;
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
トランザクションを開始しなくても、トリガーの処理が失敗した場合、元の insert ごとロールバックされるようです。
PostgreSQL の場合
トリガーを作成します。
CREATE OR REPLACE FUNCTION synchronize_summary_f() RETURNS TRIGGER AS $$
BEGIN
UPDATE summary SET count = count + 1;
RETURN null;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER synchronize_summary
AFTER INSERT ON records
EXECUTE PROCEDURE synchronize_summary_f();
同じように、片方だけ Auto Commit で実行します。
|postgres=# begin; ||
|BEGIN ||
|postgres=# insert into records values(10); ||
|INSERT 0 1 ||
| ||postgres=# insert into records values(20);
|postgres=# ||
| ||
| ||ERROR: canceling statement due to lock timeout
| ||CONTEXT: while updating tuple (0,13) in relation "summary"
| ||SQL statement "UPDATE summary SET count = count + 1"
| ||PL/pgSQL function synchronize_summary_f() line 3 at SQL statement
|postgres=# commit; ||
|COMMIT ||
MySQL の場合と同様、Auto Commit した場合でもトリガー元までロールバックされます。
postgres=# select * from records;
id
----
10
(1 row)
postgres=# select * from summary;
count
-------
1
(1 row)
シナリオ1のまとめ
MySQL(InnoDB) や PostgreSQL では以下のような動作になります。
同じサマリーが同時に更新された時の挙動
- update 文で自動的に行ロックがかかる。
- それにより意図しない上書きは防がれる。
トリガーでサマリーを更新した場合
- トリガーを呼び出したクエリを含めて同じトランザクションとして処理される。そのためトリガー元だけ実行される、という状況は防がれる。
- Auto Commit でトリガーが呼ばれた場合も同様。
カウントするだけであれば、トリガーを作れば割となんとかなりそうです。
シナリオ2: 最新のレコードのIDを格納する
今度は別の例を考えます。先ほどと同じように、records というテーブルと summary というサマリーテーブルを例にとって考えます。
-- レコード。追加された時刻も記録する。
CREATE TABLE records(
id INTEGER NOT NULL PRIMARY KEY,
creation_date DATE NOT NULL
);
-- サマリーテーブル。最後に追加されたレコードを格納
CREATE TABLE summary(
-- 最大の creation_date の値を持つ records の id
latest_record INTEGER
);
INSERT INTO summary VALUES(null);
今回は records にはレコードの追加時刻があり、summary には最後に作成された record の ID を格納します。summary テーブルの更新のためには先ほどの例よりも複雑な処理が必要になります。
同時に records を insert する
前回と同じく、record を追加してから同じトランザクションで summary を更新する、という流れを同時に行ってみます。
MySQL の変数を使う方法
わかりやすいので MySQL で変数を使用してみます。以下のようなコードになります。
insert into records values(10, '2000-01-01');
select id into @latest from records order by creation_date desc limit 1;
update summary set latest_record = @latest;
これを同時に実行してみます。
|mysql> begin; ||mysql> begin;
|Query OK, 0 rows affected (0.00 sec) ||Query OK, 0 rows affected (0.00 sec)
| ||
| || 🔻🔻🔻 先にレコードを追加 🔻🔻🔻
| ||mysql> insert into records values(10, '2000-01-01');
| ||Query OK, 1 row affected (0.00 sec)
|mysql> insert into records values(20, '2099-12-|| ◀︎ ◀︎ ◀︎
|31'); || ◀︎ ◀︎ ◀︎ 後にレコードが追加される
|Query OK, 1 row affected (0.00 sec) || ◀︎ ◀︎ ◀︎
| ||
|mysql> select id into @latest from records orde|| ◀︎ ◀︎ ◀︎
|r by creation_date desc limit 1; || ◀︎ ◀︎ ◀︎
|Query OK, 1 row affected (0.00 sec) || ◀︎ ◀︎ ◀︎
| || ◀︎ ◀︎ ◀︎ しかし summary テーブルの更新は左側が先
|mysql> update summary set latest_record = @late|| ◀︎ ◀︎ ◀︎
|st; || ◀︎ ◀︎ ◀︎
|Query OK, 1 row affected (0.00 sec) || ◀︎ ◀︎ ◀︎
|Rows matched: 1 Changed: 1 Warnings: 0 || ◀︎ ◀︎ ◀︎
| ||mysql> select id into @latest from records order by creation_date desc limit 1;
| ||Query OK, 1 row affected (0.00 sec)
| ||
| ||mysql> update summary set latest_record = @latest;
| || 🔺🔺🔺 summaryに同時アクセスしてるのでロック待ち 🔺🔺🔺
| ||
|mysql> commit; ||
|Query OK, 0 rows affected (0.10 sec) ||Query OK, 1 row affected (4.62 sec)
| ||Rows matched: 1 Changed: 1 Warnings: 0
| ||
| ||mysql> commit;
| ||Query OK, 0 rows affected (0.02 sec)
結果を見てみます。
mysql> select * from records;
+----+---------------+
| id | creation_date |
+----+---------------+
| 10 | 2000-01-01 |
| 20 | 2099-12-31 |
+----+---------------+
2 rows in set (0.01 sec)
mysql> select * from summary;
+---------------+
| latest_record |
+---------------+
| 10 |
+---------------+
1 row in set (0.00 sec)
古いレコードが記録されてしまいました。コミットしていないお互いの状態が見えないので、ロックなどを使わないとどうしても値がずれてしまいます。
変数を使わない方法
変数を使わず、update 文でサブクエリを使う事で records の状態を参照してみます。
insert into records values(10, '2000-01-01');
update summary,
(select id from records order by creation_date desc limit 1) new_value
set latest_record = new_value.id;
同時に実行すると以下のようになります。
|mysql> begin; ||mysql> begin;
|Query OK, 0 rows affected (0.00 sec) ||Query OK, 0 rows affected (0.00 sec)
| ||
| || 🔻🔻🔻 先にレコードを追加 🔻🔻🔻
| ||mysql> insert into records values(10, '2000-01-01');
| ||Query OK, 1 row affected (0.00 sec)
| ||
|mysql> insert into records values(20, '2099-12-|| ◀︎ ◀︎ ◀︎
|31'); || ◀︎ ◀︎ ◀︎ 後にレコードが追加される
|Query OK, 1 row affected (0.00 sec) || ◀︎ ◀︎ ◀︎
| ||
|mysql> update || ◀︎ ◀︎ ◀︎
| -> summary, || ◀︎ ◀︎ ◀︎
| -> (select id from records || ◀︎ ◀︎ ◀︎ しかし summary テーブルの更新は左側が先
| -> order by creation_date desc || ◀︎ ◀︎ ◀︎
| -> limit 1) new_value || ◀︎ ◀︎ ◀︎
| -> set latest_record = new_value.id; || ◀︎ ◀︎ ◀︎
| ||
| ||
| ||
| || 🔻🔻🔻 デッドロック発生!! 🔻🔻🔻
| ||mysql> update
| || -> summary,
| || -> (select id from records
| || -> order by creation_date desc
| || -> limit 1) new_value
| || -> set latest_record = new_value.id;
|ERROR 1213 (40001): Deadlock found when trying ||Query OK, 1 row affected (0.00 sec)
|to get lock; try restarting transaction ||Rows matched: 1 Changed: 1 Warnings: 0
| ||
| ||mysql> commit;
| ||Query OK, 0 rows affected (0.11 sec)
| ||
|mysql> ||
|mysql> rollback; ||
|Query OK, 0 rows affected (0.00 sec) ||
うまくいきません。左側の update 文が走った時にロックされ、右側の update 文が走った途端デッドロックが発生しています。
テーブルの中身はどうなったでしょうか?
mysql> select * from records;
+----+---------------+
| id | creation_date |
+----+---------------+
| 10 | 2000-01-01 |
+----+---------------+
1 row in set (0.00 sec)
mysql> select * from summary;
+---------------+
| latest_record |
+---------------+
| 10 |
+---------------+
1 row in set (0.00 sec)
mysql>
エラーとなったトランザクションの処理は全てロールバックされました。
(おまけ： デッドロックが発生した理由)
下の二つの理由が組み合わさったためだと思います。
- update 文でサブクエリを使う場合、クエリ対象の行に対して読み込みロックがされる2
- ネクストキーロックにより、別のトランザクションでinsertされた行も読み取りロックする事がある
update 文とは別に select した場合、変数を使った例を見ればわかるようにデッドロックは起きません。また、同様の処理を PostgreSQL で行ってみましたがやはりデッドロックは発生しません。
PostgreSQL の場合
update 文の中でサブクエリを使います。
insert into records values(10, '2000-01-01');
update summary set latest_record =
(select id from records order by creation_date desc limit 1);
これを MySQL の例と同様に同時に実行します。
|postgres=# begin; ||postgres=# begin;
|BEGIN ||BEGIN
| || 🔻🔻🔻 先にレコードを追加 🔻🔻🔻
| ||postgres=# insert into records values(10, '2000-01-01');
| ||INSERT 0 1
| ||
|postgres=# insert into records values(20, '2099|| ◀︎ ◀︎ ◀︎
|-12-31'); || ◀︎ ◀︎ ◀︎ 後にレコードが追加される
|INSERT 0 1 || ◀︎ ◀︎ ◀︎
|postgres=# update summary set latest_record = (|| ◀︎ ◀︎ ◀︎
|postgres(# select id from records || ◀︎ ◀︎ ◀︎
|postgres(# order by creation_date desc || ◀︎ ◀︎ ◀︎ しかし summary テーブルの更新は左側が先
|postgres(# limit 1 || ◀︎ ◀︎ ◀︎
|postgres(# ); || ◀︎ ◀︎ ◀︎
|UPDATE 1 || ◀︎ ◀︎ ◀︎
| ||postgres=# update summary set latest_record = (
| ||postgres(# select id from records
| ||postgres(# order by creation_date desc
| ||postgres(# limit 1
| ||postgres(# );
| ||🔺🔺🔺 ロックが発生🔺🔺🔺
|postgres=# commit; ||
|COMMIT ||UPDATE 1
| ||postgres=# commit;
| ||COMMIT
ロックが発生しましたが、コミットは成功しました。結果を見てみます。
postgres=# select * from records;
id | creation_date
----+---------------
10 | 2000-01-01
20 | 2099-12-31
(2 rows)
postgres=# select * from summary;
latest_record
---------------
10
(1 row)
先ほどのMySQL の変数を使った場合と同様、latest_record に古いレコードのIDが記録されてしまいました。
解決策
上のケースだといずれも一貫性を失ったりデッドロックが起きたりとあまりうまくいきません。どうすれば良いのでしょうか?
一つの方法としては、records を更新する前に summary テーブルの行を必ずロックするというルールにする というのがあります。
-- サマリーテーブルの更新したい行をロック
select * from summary for update;
-- 先ほどと同じ更新処理
insert into records values(10, '2000-01-01');
select id into @latest from records order by creation_date desc limit 1;
update summary set latest_record = @latest;
一貫性を失ったりデッドロックが起きるのを防ぐ事ができました。これは粗粒度ロックと呼ばれたりするやり方になります。
シナリオ2のまとめ
サマリーを更新する際に元となるレコードを参照する必要がある場合、以下のように少しややこしくなります。
- トランザクションを使っても一貫性が崩れたりデッドロックが発生するケースがある。
- サマリーを先にロックする事で上の問題は回避できる。
-
InnoDBの場合はエラーの種類によって分だけがロールバックされる場合とトランザクションがロールバックされる場合があるみたいです。 https://dev.mysql.com/doc/refman/5.6/ja/innodb-error-handling.html ↩
-
https://www.percona.com/blog/2017/09/25/avoid-shared-locks-from-subqueries-when-possible/ ↩