【1000万円以上の損失も!】MySQLのupdate文でデータの不整合を発生させる方法とlockの使い方

  • 2019年8月10日
  • 2022年12月9日
  • SQL
  • 52view
SQL

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などの読み取りクエリも通しません。今回の様は本の在庫数を管理する場合に、必須になるロックといえます。

 

共有ロック

共有ロックは更新クエリを通さないが、読み取りクエリは通すクエリです。

 

詳しくは、本家サイト参照。

14.2.3 InnoDB のロックモード

 

不整合の解決策

 

前章のような不整合はどのように解消したら良いのか解説します。

結論から言うと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)なので正しく更新できました。

よって、正しく更新できます。

 

まとめ

不整合の不具合は、気づくのが困難です。

しかも、本番環境でやってしまうと在庫間違えで、ユーザーから問い合わせが発生して、その対応におわれることにより時間を無駄にしてしまいます。

設計段階から、不整合が発生しないように心がけましょう。

というお話でした。

 

プログラミング・実務経験がある方も必見!
空いた時間をスキルアップ・収入増に使ってみませんか?

副業で仕事を取る方法を教えてくれる!
プログラミングスクール4選の詳細

最新情報をチェックしよう!
>プログラミングスクール検索・比較表サイト

プログラミングスクール検索・比較表サイト

ワンクリック、さらに詳細に条件を指定してプログラミングスクールの検索ができます。さらに比較表により特徴を細かく比較できる!

CTR IMG