こんにちは、エンジニアの川本です。
主にBUYMAの決済・配送を担当しているチームでバックエンドの開発をしています。
この記事は Enigmo Advent Calendar 2023 の 20 日目の記事です。
個人開発でPlanetScaleというMySQL互換のサーバーレスデータベースを使用しているのですが、特筆すべき仕様として外部キーのサポートがありません。
planetscale.com
外部キー制約はDBレベルで強い整合性を担保できる便利な手段ですが、PlanetScaleではその機能が利用できないので、アプリケーションレベルで整合性を担保する必要があります。
MySQLの外部キーのオプションにはいくつか種類がありますが、これらが使えない場合にアプリケーション側ではどのように担保すればよいのでしょうか？
今回は、Railsを例にしてアプリケーション側でMySQLの外部キーに相当する機能をどのように担保できるのかを検証してみようと思います。
余談：PlanetScaleについて
最近PlanetScaleはベータ版で外部キーをサポートし始めましたが、残念ながらHobbyプランではまだサポートされておりません。
PlanetScaleの基盤であるVitessはOnline DDLの機能を提供しており、それが原因で外部キーのサポートが長らく難しかったようです。
以下のドキュメントやブログには、PlanetScaleが外部キーをサポートできるようになるまでの背景や課題、そしてその克服方法についての詳細な情報が記載されています。興味がある方はぜひ読んでみてください。
外部キーのサポートが難しかった理由
外部キーをサポートするための取り組み
親子関係のテーブルを作成
まず親子関係にある、Parent, Childテーブルを作成してサンプルデータを入れる。
mysql> CREATE TABLE parent (
-> id INT NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=INNODB;
mysql> CREATE TABLE child (
-> id INT NOT NULL,
-> parent_id INT NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=INNODB;
mysql> INSERT INTO parent (id) VALUES (1), (2);
mysql> INSERT INTO child (id, parent_id) VALUES (1, 1), (2, 1), (3, 2);
mysql> SELECT * FROM parent p JOIN child c ON p.id = c.parent_id;
+
| id | id | parent_id |
+
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 2 | 3 | 2 |
+
MySQLでは以下4つのON DELETE副次句で指定できる参照アクションがあります。
ON UPDATE副次句もありますが、今回はON DELETEに限定することにします。
dev.mysql.com
親テーブルから行を削除し、子テーブル内の一致する行を自動的に削除する。
mysql> ALTER TABLE child
-> ADD CONSTRAINT fk_parent
-> FOREIGN KEY (parent_id)
-> REFERENCES parent(id)
-> ON DELETE CASCADE;
mysql> DELETE FROM parent WHERE id = 1;
mysql> SELECT * FROM child;
+
| id | parent_id |
+
| 3 | 2 |
+
ON DELETE SET NULL
親テーブルから行を削除し、子テーブルの外部キーカラムをNULLにする。
※ この設定をするときは、childのparent_idはNOT NULLにしない。
mysql> ALTER TABLE child
-> ADD CONSTRAINT fk_parent
-> FOREIGN KEY (parent_id)
-> REFERENCES parent(id)
-> ON DELETE SET NULL;
mysql> DELETE FROM parent WHERE id = 1;
mysql> SELECT * FROM child;
+
| id | parent_id |
+
| 1 | NULL |
| 2 | NULL |
| 3 | 2 |
+
ON DELETE RESTRICT or ON DELETE NO ACTION or 指定なし
親テーブルに対する削除操作は拒否されます。また、ON DELETE RESTRICT or ON DELETE NO ACTION or ON DELETE 指定なしは同じ挙動になります。以下の例ではON DELETE 指定なしで例を示します。
mysql> ALTER TABLE child
-> ADD CONSTRAINT fk_parent
-> FOREIGN KEY (parent_id)
-> REFERENCES parent(id)
mysql> DELETE FROM parent WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myapp_development`.`child`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
mysql> SELECT * FROM parent p JOIN child c ON p.id = c.parent_id;
+
| id | id | parent_id |
+
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 2 | 3 | 2 |
+
Railsでは、Active Recordのdependentオプションを使用して、MySQLの外部キー制約に相当する機能を実現できます。
dependentオプションは親レコードに対してActiveRecord::Persistence#destroyが実行されたときに、紐ずいている子レコードに対して実行されるメソッドのことです。
ON DELETE CASCADEに相当することは、delete_all, destory, destory_asyncのいずれかで実現することができます。これら3つは全て最終的に実現できることは同じですが、それぞれで以下のように挙動の違いがあります。
delete_all
delete_allは、parentに関連付けられたchildが一括で1つのSQLで削除します。
また、childに対してActiveRecord::Persistence#deleteが実行されるので、ActiveRecord::Persistence#destroy実行時に作用するbefore_destroyやafter_destroyといったコールバックや孫クラスのdependentオプションが実行されません。
そのため、単純に削除SQLを実行するだけなので関連するchildが多い場合にはdestroyよりパフォーマンスが向上する可能性があリます。
class Parent < ApplicationRecord
self.table_name = 'parent'
has_many :child, dependent: :delete_all
end
irb(main):002> parent = Parent.find(1)
irb(main):054> parent.destroy
TRANSACTION (0.7ms) BEGIN
Child Delete All (1.2ms) DELETE FROM `child` WHERE `child`.`parent_id` = 1
Parent Destroy (0.7ms) DELETE FROM `parent` WHERE `parent`.`id` = 1
TRANSACTION (1.9ms) COMMIT
=>
destroy
destroyは、parentに紐づくchildを全て取得して1件ずつ削除します。
ActiveRecord::Persistence#destroyが実行されるため、before_destroyやafter_destroyなどのコールバックも実行され、孫クラスにあるdependentオプションも実行されます。
そのため、関連するchildが多いと発行されるSQLも増え、コールバックの実行や孫クラスのdependentオプションの実行が多くなり、delete_allよりもパフォーマンスが低下する可能性があります。
class Parent < ApplicationRecord
self.table_name = 'parent'
has_many :child, dependent: :destroy
end
irb(main):002> parent = Parent.find(1)
irb(main):062> parent.destroy
TRANSACTION (0.3ms) BEGIN
Child Load (1.0ms) SELECT `child`.* FROM `child` WHERE `child`.`parent_id` = 1
Child Destroy (1.3ms) DELETE FROM `child` WHERE `child`.`id` = 1
Child Destroy (1.1ms) DELETE FROM `child` WHERE `child`.`id` = 2
Parent Destroy (0.9ms) DELETE FROM `parent` WHERE `parent`.`id` = 1
TRANSACTION (1.2ms) COMMIT
=>
destroy_async
destroy_asyncは、parentに関連する全てのchildを非同期で1件ずつ削除します。
紐づくchildが非常に多く、即時での削除を求められない場合に有効です。紐づくchildが多いと処理が最悪の場合はタイムアウトする可能性もあります。そのような場合、まずparentを削除してクライアントにレスポンスを速やかに返し、残りの紐づくchildは非同期で削除することで問題を解決できます。
class Parent < ApplicationRecord
self.table_name = 'parent'
has_many :child, dependent: :destroy_async
end
irb(main):002> parent = Parent.find(1)
irb(main):070> parent.destroy
TRANSACTION (0.3ms) BEGIN
Child Load (0.8ms) SELECT `child`.* FROM `child` WHERE `child`.`parent_id` = 1
Parent Destroy (0.8ms) DELETE FROM `parent` WHERE `parent`.`id` = 1
TRANSACTION (2.0ms) COMMIT
Enqueued ActiveRecord::DestroyAssociationAsyncJob (Job ID: 63fc4528-934a-405c-9311-7bee9fb706b1) to Async(default) with arguments: {:owner_model_name=>"Parent", :owner_id=>1, :association_class=>"Child", :association_ids=>[1, 2], :association_primary_key_column=>:id, :ensuring_owner_was_method=>nil}
=>
irb(main):071> Performing ActiveRecord::DestroyAssociationAsyncJob (Job ID: 63fc4528-934a-405c-9311-7bee9fb706b1) from Async(default) enqueued at 2023-12-16T09:16:43Z with arguments: {:owner_model_name=>"Parent", :owner_id=>1, :association_class=>"Child", :association_ids=>[1, 2], :association_primary_key_column=>:id, :ensuring_owner_was_method=>nil}
Parent Load (3.0ms) SELECT `parent`.* FROM `parent` WHERE `parent`.`id` = 1 LIMIT 1
Child Load (5.1ms) SELECT `child`.* FROM `child` WHERE `child`.`id` IN (1, 2) ORDER BY `child`.`id` ASC LIMIT 1000
TRANSACTION (0.3ms) BEGIN
Child Destroy (0.9ms) DELETE FROM `child` WHERE `child`.`id` = 1
TRANSACTION (2.0ms) COMMIT
TRANSACTION (0.3ms) BEGIN
Child Destroy (1.0ms) DELETE FROM `child` WHERE `child`.`id` = 2
TRANSACTION (1.7ms) COMMIT
Performed ActiveRecord::DestroyAssociationAsyncJob (Job ID: 63fc4528-934a-405c-9311-7bee9fb706b1) from Async(default) in 63.81ms
ON DELETE SET NULL
nullify
ON DELETE SET NULLに相当することはnullifyで実現できます。
parentに紐づくchildのparent_idをnullに更新して、parentを削除しています。
class Parent < ApplicationRecord
self.table_name = 'parent'
has_many :child, dependent: :nullify
end
irb(main):088> parent = Parent.find(1)
irb(main):090> parent.destroy
TRANSACTION (0.3ms) BEGIN
Child Update All (5.0ms) UPDATE `child` SET `child`.`parent_id` = NULL WHERE `child`.`parent_id` = 1
Parent Destroy (3.3ms) DELETE FROM `parent` WHERE `parent`.`id` = 1
TRANSACTION (1.3ms) COMMIT
=>
ON DELETE RESTRICT or ON DELETE NO ACTION or 指定なし
ON DELETE RESTRICTまたはON DELETE NO ACTIONに相当することは、
restrict_with_exceptionまたはrestrict_with_errorのいずれかで実現することができます。これら2つは全て最終的に実現できることは同じですが、それぞれで以下のように挙動の違いがあります。
restrict_with_exception
parentに紐づくchildが存在することを確認して、処理をロールバックしてActiveRecord::DeleteRestrictionErrorという例外を発生させます。
class Parent < ApplicationRecord
self.table_name = 'parent'
has_many :child, dependent: :restrict_with_exception
end
irb(main):088> parent = Parent.find(1)
irb(main):094> parent.destroy
TRANSACTION (0.6ms) BEGIN
Child Exists? (1.0ms) SELECT 1 AS one FROM `child` WHERE `child`.`parent_id` = 1 LIMIT 1
TRANSACTION (0.5ms) ROLLBACK
/usr/local/bundle/gems/activerecord-7.0.8/lib/active_record/associations/has_many_association.rb:16:in `handle_dependency': Cannot delete record because of dependent child (ActiveRecord::DeleteRestrictionError)
restrict_with_error
parentに紐づくchildが存在することを確認して、処理をロールバックしてfalseを返します。
class Parent < ApplicationRecord
self.table_name = 'parent'
has_many :child, dependent: :restrict_with_error
end
irb(main):088> parent = Parent.find(1)
irb(main):098> parent.destroy
TRANSACTION (0.5ms) BEGIN
Child Exists? (0.6ms) SELECT 1 AS one FROM `child` WHERE `child`.`parent_id` = 1 LIMIT 1
TRANSACTION (0.4ms) ROLLBACK
=> false
最後に
ここまでの紹介で、RailsアプリケーションでMySQLの外部キー制約の参照アクションを実現する手段が理解できました。
ただし、データ整合性が担保されるのは、外部キー制約に準拠したアプリケーションからの実行時に限られます。もし、同じDBを参照するが外部キー制約に準拠していないアプリケーションが存在する場合、どのような影響が生じますでしょうか？
外部キー制約のないアプリケーションからの実行により、データ整合性が維持されなくなる可能性があります。このような事態を避けるためには、できるだけDBレベルで整合性を担保する方が望ましいです。
Planet Scaleのような外部キー制約をサポートしていないDBでは、今回紹介したようなアプリケーションの実装が有効であるかもしれません。しかし、外部キー制約がサポートされているDBでは、DBレベルでの制御が安全であると言えるでしょう。
明日の記事担当はデータエンジニアリングチームです！お楽しみに！
株式会社エニグモ すべての求人一覧
hrmos.co
