このページの最終更新日 2020/10/25

データベースの同時実行制御

1、悲観的同時実行制御

このページではデータベースの同時実行制御について解説していきます。プログラム言語はPHP、データベースはMysqlを使用します。また開発環境はXAMPPをインストールしている状態をもとに解説を行っていきます。

以下は実行する度にデータベースの scoreカラムに入力されている点数を、1つずつ加算していくプログラムです。

ファイル名「pessimism.php」
 <?php
	try {
	        $pdo = new PDO("mysql:host=localhost; dbname=training_db; charset=utf8", "root");
	        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	        $select = $pdo->prepare("SELECT score FROM score_table WHERE id = :id");
	        $select->bindValue(":id", 1, PDO::PARAM_INT);
	        $select->execute();
	        $row = $select->fetch(PDO::FETCH_ASSOC);
	        $scoreNow = $row["score"];
                echo date("H:i:s")." 点数を取得しました。現在の点数は".$scoreNow."点です。\r\n";

	        sleep(3); //3秒間待機

	        $scoreNext = $scoreNow + 1;
	        $update = $pdo->prepare("UPDATE score_table SET SCORE = :scoreNext WHERE id = :id");
	        $update->bindValue(":id", 1, PDO::PARAM_INT);
    	        $update->bindValue(":scoreNext", $scoreNext, PDO::PARAM_INT);
	        $update->execute();
                echo date("H:i:s")." 点数を更新しました。現在の点数は".$scoreNext."点です。\r\n";
	} catch (PDOException $e) {
		echo $e->getMessage()."\r\n";
	}
?>

 コマンドプロンプトからプログラムを実行すると、pic.1のような結果が出力されます。
まず現在入力されているデータベースの点数の取得を6~10行目で行い11行目で現在の点数を現在時刻と共に出力します。 その後、3秒間待機した後、点数を1つ上げてデータベースの値を更新し、更新後の値を出力します。
 pic.1のように1つのコマンドプロンプトから複数回プログラムを実行しても問題なく点数は1ずつ加算されていきます。

- pic.1 -

 問題は複数のコマンドプロンプトからプログラムを実行した場合です。pic.2のように左側のコマンドプロンプトからプログラムを実行し、その1秒後に今度は右側のコマンドプロンプトから実行します。 このように実行した場合、プログラムが2度起動されているのも関わらず点数は2点分加算はされず、1点だけが加算されるような状態になってしまいます。

- pic.2 -


  この例では複数のコマンドプロンプトからローカルのPHPを実行しているだけですが、実際の運用上は、複数のクライアントがWebブラウザから、Webサーバーに同時アクセスするような状況を考えます。 では、複数のユーザーから同時にアクセスがあった場合にも、プログラムが起動された分だけ点数が加算されるようにしましょう。PHPコードは次のようになります。

ファイル名「pessimism.php」
<?php
	try {
		echo date("H:i:s")." プログラムを実行しました。\r\n";

		$pdo = new PDO("mysql:host=localhost; dbname=training_db; charset=utf8", "root");
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  		$pdo->beginTransaction();

		try {
 			$select = $pdo->prepare("SELECT score FROM score_table WHERE id = :id FOR UPDATE");
			$select->bindValue(":id", 1, PDO::PARAM_INT);
			$select->execute();
			$row = $select->fetch(PDO::FETCH_ASSOC);
			$scoreNow = $row["score"];
			echo date("H:i:s")." 点数を取得しました。現在の点数は".$scoreNow."点です。\r\n";

			sleep(3);

			$scoreNext = $scoreNow + 1;
			$update = $pdo->prepare("UPDATE score_table SET SCORE = :scoreNext WHERE id = :id");
			$update->bindValue(":id", 1, PDO::PARAM_INT);
			$update->bindValue(":scoreNext", $scoreNext, PDO::PARAM_INT);
			$update->execute();
			echo date("H:i:s")." 点数を更新しました。現在の点数は".$scoreNext."点です。\r\n";

 			$pdo->commit();
		} catch(PDOException $e) {
			$pdo->rollBack();
			throw $e;
		}
	} catch (PDOException $e) {
		echo $e->getMessage()."\r\n";
	}
?> 

 データベースから情報を読み取る前の8行目でトランザクションの開始を宣言し、データベースへ情報を更新した後の27行目でコミットを行っていることが重要な変更点です。 また、データベースから点数を読み取る11行目は最初の例と変わっていないように見えますが最後に「FOR UPDATE」と書き加えられています。 これでどういう実行処理が行われるのかは、実際にコマンドプロンプトを2つ起動して同時に実行してみると解ります。
 先ほどと同様に、まず左側のコマンドプロンプトからPHPを実行し、その1秒後に右側のコマンドプロンプトから実行を行います。

- pic.3 -

 まず 14:41:29に左側のコマンドプロンプトから実行処理が行われほぼ同時にデータベースから値を取得しています。この時点での点数は1006点です。重要なところは次なのですが、右側のコマンドプロンプトから実行処理が行われているのは14:41:30ですが、この時点で直ぐにはデータベースの値を取得せず待機した状態になっています。これはトランザクションとFOR UPDATE分によって排他的ロックが掛かっていることに起因します。
 次に 14:41:29から3秒後の14:41:32にデータベースが更新されこの時点での点数は1007点となりました。左側のコマンドプロンプトからの実行処理のトランザクションがコミットされた後で、右側のコマンドプロンプトの実行処理が行われることになります。この段階では点数は1007点となっていますから、次に更新するときは1008点となる訳です。
 このように、2回の実行処理が重なったとしても、きちんと2点分の点数が加算されるようになりました。サーバーのデータベースに複数のクライアントが同時にアクセスする場合は、整合性を保つための仕組みが必要となります。この例のように排他的ロックを掛けて、そもそもデータベースの特定の処理が同時に実行されないように片方のユーザーを待機させるような仕組みを「悲観的同時実行制御」というように呼びます。
 「悲観的同時実行制御」では処理を待機する必要があるため、待機時間が長くなるような場合にはユーザビリティが悪くなることが問題となります。これに対して待機が発生しないような方法として「楽観的同時実行制御」の方法を紹介します。


2、楽観的同時実行制御

 楽観的同時制御の例として、部屋の予約システムを考えてみます。room_reservationテーブルのroom_idは部屋番号を表し、is_vacantは部屋の空き状況を表しています。今は学習用のため、とりあえず部屋番号は101番と決め打ちしておきます。部屋の空き状況は、予約が入っているときは false、予約が入っていない場合は true が入力されているものとします。

ファイル名「optimism.php」
<?php
	try {
		$pdo = new PDO("mysql:host=localhost; dbname=training_db; charset=utf8", "root");
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		$select = $pdo->prepare("SELECT is_vacant FROM room_reservation WHERE room_id = 101");
		$select->execute();
		$row = $select->fetch();

		if (!$row["is_vacant"]) {
			echo "既に予約済みです。処理を中止します。\r\n";
			exit;
		}

		echo "この部屋を予約しますか?予約する場合は「Y」を入力。それ以外の入力は予約しないものとします。\r\n";
		$userInput = trim(fgets(STDIN));

		if ($userInput != "Y") {
			echo "予約処理を中止します。\r\n";
			exit;
		}

		$update = $pdo->prepare("UPDATE room_reservation SET is_vacant = false WHERE room_id = 101");
		$update->execute();
		echo "予約処理が完了しました。\r\n";
	} catch (PDOException $e) {
		echo $e->getMessage()."\r\n";
	}
?>

 悲観的制御と同様に、実行する人間が1人だけの問題であれば問題ありません。pic.4のように一度プログラムを起動して部屋の予約を行った後、再度プログラムを起動すると既に予約済みであることが確認出来るメッセージが表示されます。

- pic.4 -

 片方のコマンドプロンプトからプログラムを起動して「Y」と入力する前に、もう片方のコマンドプロンプトからも実行して その後に両方のコマンドプロンプトに「Y」と入力します。1つの部屋に対して複数のユーザーが同時に予約を取ったことになってしまいました。 これでは問題ですね。

- pic.5 -

 ここでは、後から「Y」と入力したユーザーには、プログラムの実行中に別のユーザーが予約を取ってしまい、予約処理が失敗したことを通知することを考えます。 どうやって判定するかが少しテクニカルで知っていないと難しいかなと思います。まず、データベースの更新処理を行った場合に大抵は更新の対象となる行が何行だったのかが 判定出来るようになっています。実際に更新の対象の行をカウントしているのが25行目です。
 先ほどのコードのままでは、後から更新しても更新の対象が1行であることに変わりはないので、23行目のように更新すべき行の条件として AND is_vacant という条件を追加します。 このように条件を追加することで、既に別のユーザーが予約を取ってしまった場合は is_vacant が false となっているため更新の対象の行は「0行」となり、 別のユーザーが予約を取ってしまったことの判断とすることが出来るようになります。

ファイル名「optimism.php」
<?php
	try {
		$pdo = new PDO("mysql:host=localhost; dbname=training_db; charset=utf8", "root");
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		$select = $pdo->prepare("SELECT is_vacant FROM room_reservation WHERE room_id = 101");
		$select->execute();
		$row = $select->fetch();

		if (!$row["is_vacant"]) {
			echo "既に予約済みです。処理を中止します。\r\n";
			exit;
		}

		echo "この部屋を予約しますか?予約する場合は「Y」を入力。それ以外の入力は予約しないものとします。\r\n";
		$userInput = trim(fgets(STDIN));

		if ($userInput != "Y") {
			echo "予約処理を中止します。\r\n";
			exit;
		}

		$update = $pdo->prepare("UPDATE room_reservation SET is_vacant = false WHERE room_id = 101 AND is_vacant");
		$update->execute();
		if ($update->rowCount() == 1) {
			echo "予約処理が完了しました。\r\n";
		} else {
			echo "プログラム実行中に別のユーザーが予約を行ったため、予約処理に失敗しました。\r\n";
		}
	} catch (PDOException $e) {
		echo $e->getMessage()."\r\n";
	}
?> 

このコードを同時に実行してみます。pic.6のように一人のユーザーでは予約が成功しますが、他のユーザーには予約が失敗したことが通知されるようになりました。

- pic.6 -