MySQLを使っていてこんな疑問を持ったことはありませんか?
- 排他制御が必要というが、そもそもどんなときにデータの不整合が起きるのかわからない
- どのようにロックを掛けたら良いの?
- 不整合が起きたらどうやったら改善するの?
今回は、上記のような3つの疑問に答えていきます。
あるシステムで、データの不整合による不具合で、1000万円以上も損失したという話があるくらいのため、不整合には気をつける必要があります。
データの不整合の発生方法
MySQLにおいてどんなときにデータの不整合が起きるの?
データの不整合がよく起きるケースは、複数のタスクが並列に動いていて、同じリソース(テーブルの行)を参照し、更新(update)したときに発生します。
どのように不整合が起きるのか、本の在庫管理を例に実際に確かめてみます。
まずは、データを作るところから解説していきます。
テーブルを作成する
CREATE TABLE `m_books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL COMMENT '名前',
`count` int(10) DEFAULT '0' COMMENT '在庫',
`created` datetime NOT NULL,
`modified` datetime DEFAULT NULL,
`del` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`created`),
UNIQUE KEY `uniqe` (`id`,`created`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
データを追加します。
初期の本の在庫数を10に設定します。
INSERT INTO `m_books` (`id`, `name`, `count`, `created`, `modified`, `del`)
VALUES
(1, '本1', 10, '0000-00-00 00:00:00', NULL, 0);
SQLを1つ1つ実行する
プロセス(タスク)A,Bでは、それぞれ以下のようなスクリプトを作成します。
■タスクA
begin;
select @remain_count := count from m_books where id = 1;
update m_books set count = (@remain_count - 2) where id = 1;
commit;
■タスクB
begin;
select @remain_count := count from m_books where id = 1;
update m_books set count = (@remain_count - 5) where id = 1;
commit;
処理の実行順番は、以下の画像の番号通りにします。
実際に検証の際は、
Sequel Proを使うと、プロセスを分けてSQLを1行ずつ実行できるので、検証するのに便利です。
処理の概要:
・初期値 m_books.count=10
・タスクAが本2冊購入(update文実行)
・タスクBが本5冊購入(update文実行)
上記の処理の結果、本来の残りは3冊になった状態になってほしいはずです。
ところが、⑦のところで⑤の処理が考慮されていない(排他制御なし)ため、5冊になってしまいます。
これが、不整合です。
updateなどの更新系のクエリは、共有ロックになります。
このようなことをやってしまうと、大手企業が使う大きなシステムでは、一千万円単位の損失になるため、ちょっとしたミスが命取りになります。
2つのロックとは
ロックには、次に解説する2つのロックの方法があります。
排他ロック
排他ロックは対象行を全てのクエリからロックするため、UPDATEやDELETEなどの更新クエリはもちろん、SELECTなどの読み取りクエリも通しません。今回の様は本の在庫数を管理する場合に、必須になるロックといえます。
共有ロック
共有ロックは更新クエリを通さないが、読み取りクエリは通すクエリです。
詳しくは、本家サイト参照。
不整合の解決策
前章のような不整合はどのように解消したら良いのか解説します。
結論から言うとselect文の最後に「for update」を追加します。
「for update」を使うと、「排他ロック」をかけることでき、select文を実行した段階でロックを掛けることができます。select文でロックを掛ける(排他ロック)ことにより、データの不整合を防ぎます。
「for update」を考慮したSQL文
プロセス(タスク)A,Bでは、それぞれ以下のようなスクリプトを作成します。
■タスクA
begin;
select @remain_count := count from m_books where id = 1 for update;
update m_books set count = (@remain_count - 2) where id = 1;
commit;
■タスクB
begin;
select @remain_count := count from m_books where id = 1 for update;
update m_books set count = (@remain_count - 5) where id = 1;
commit;
処理の実行順番は、以下の画像の番号通りにします。
ここでのポイントは、タスクAの「select 〜 for update」のところで、排他ロックをかけていることです。
その結果、タスクBのselectのところでロック解除待ちが発生します。ロック解除は、タスクAのcommitが終わったタイミングで解除のため、整合性が保たれるのです。
処理の概要:
・初期値 m_books.count=10(本10冊)
・排他ロックをする
・タスクAが本2冊購入(update文実行)
・タスクBが本5冊購入(update文実行)
上記の処理が終わったあとは、本の在庫数は3冊(10-2-5=3)なので正しく更新できました。
よって、正しく更新できます。
まとめ
不整合の不具合は、気づくのが困難です。
しかも、本番環境でやってしまうと在庫間違えで、ユーザーから問い合わせが発生して、その対応におわれることにより時間を無駄にしてしまいます。
設計段階から、不整合が発生しないように心がけましょう。
というお話でした。
\IT未経験者からのサポートあり!転職サービス3選!!/
サービス名 | |||
---|---|---|---|
未経験 | 未経験OK | 未経験の転職専用 | 経験者向け |
公開の求人数 | ITエンジニア全体で1万件以上 ITエンジニア未経験で600件以上 |
未公開 | 5,000件以上 |
利用対象 | 全職種 | IT特化 | IT特化 |
特徴 | ✓誰もが知る転職サービス ✓経歴を登録しておくとオファーが来る |
✓企業担当者と条件交渉 ✓スキルの身につく企業を紹介 |
✓IT専門のエージェントが対応 ✓転職成功すると年収200万円以上の大幅アップがある |
転職サポート内容 |
|
|
|
公式サイト | リクナビネクスト | テックゲート | レバテックキャリア |