MySQL で排他制御

並行処理において、排他制御は欠かせません。 Web プログラミングで使われることの多い、 MySQL において排他制御のやり方を考えてみます。

ショッピングサイトなどにおいては、ある商品をたくさんのユーザーが一斉に注文するという状況がおこります。 人気商品などの場合とくに起こりやすいと思われます。 このとき、注文にともなって商品の在庫を減らしていくという処理が必要になりますが、 在庫から減らした数と注文数が常に等しくなるように整合性を取らねばなりません。

注文を受け付けるときには

  • 残りの在庫数を確認
  • (在庫があれば)注文数を増やす
  • 在庫を減らす

という処理の流れになるでしょう。 しかし、この一連の処理はそのままでは不可分な操作ではなく、 それぞれの処理の間に別のユーザーからの注文処理(の一部)が割り込んでくる可能性があります。

すると、在庫と注文の間に不整合が生まれてきます。

以下で実際にMySQL データベースを操作しながら、この問題の解決策を探ります。

準備

まずは操作対象となるテーブルを作ります。 データベース名は concurrent とします。

■在庫テーブル

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

■ 注文テーブル

CREATE TABLE `orders` (
  `item_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在庫テーブルには在庫数を入れておきます(1レコードだけですが・・・)

mysql> select * from stocks;
+----+---------+----------+
| id | name    | quantity |
+----+---------+----------+
|  1 | 商品1   |       10 |
+----+---------+----------+

簡単のため、注文テーブルにも予めレコードを作っておきます

+---------+---------+----------+
| item_id | user_id | quantity |
+---------+---------+----------+
|       1 |       1 |        0 |
|       1 |       2 |        0 |
|       1 |       3 |        0 |
|       1 |       4 |        0 |
|       1 |       5 |        0 |
|       1 |       6 |        0 |
|       1 |       7 |        0 |
|       1 |       8 |        0 |
|       1 |       9 |        0 |
|       1 |      10 |        0 |
+---------+---------+----------+

プログラム

さて、この商品在庫に対して注文操作をかけるプログラムを考えましょう。

まずは SQL のクエリを投げる関数を用意しておきます

■ lib.sh

#!/bin/sh

# クエリを投げる
query() {
    mysql -uXXXXX -pXXXXX -Dconcurrent -Nse "$1"
}

つづいて、注文のクエリを発行するプログラムです。

■ customer.sh

. ./lib.sh

query "begin;\
          select quantity from stocks where id = 1 into @q;\
          select concat('残り在庫=', @q, '個:ユーザー $1 が1個購入します');\
          update stocks set quantity = @q - 1 where id = 1;\
          update orders set quantity = quantity + 1 where item_id = 1 and user_id = $1;\
          select quantity from stocks where id = 1 into @q;\
          select concat('残り在庫=', @q, '個:ユーザー $1 購入完了');\
       commit;"

これをテストしてみましょう。

sh customer.sh 1
残り在庫=10個:ユーザー 1 が1個購入します
残り在庫=9個:ユーザー 1 購入完了
mysql> select * from stocks;
+----+---------+----------+
| id | name    | quantity |
+----+---------+----------+
|  1 | 商品1   |        9 |
+----+---------+----------+
mysql> select * from orders;
+---------+---------+----------+
| item_id | user_id | quantity |
+---------+---------+----------+
|       1 |       1 |        1 |
|       1 |       2 |        0 |
|       1 |       3 |        0 |
|       1 |       4 |        0 |
|       1 |       5 |        0 |
|       1 |       6 |        0 |
|       1 |       7 |        0 |
|       1 |       8 |        0 |
|       1 |       9 |        0 |
|       1 |      10 |        0 |
+---------+---------+----------+

単独のプログラムを一度だけ動かす場合にはうまく行っているようです。 次に customer.sh を10回、逐次に呼び出してみます。

■ main.sh

#!/bin/sh

. ./lib.sh

# リセット処理
query "update orders set quantity = 0"
query "update stocks set quantity = 10 where id = 1"

echo "買い物中..."

for i in `seq 1 10`;do
    sh customer.sh ${i}
done;
wait

wait
echo "完了!"

rest=`query "select quantity from stocks where id = 1"`
echo "残り=$rest"

これを実行すると、

sh main.sh
買い物中...
残り在庫=10個:ユーザー 1 が1個購入します
残り在庫=9個:ユーザー 1 購入完了
残り在庫=9個:ユーザー 2 が1個購入します
残り在庫=8個:ユーザー 2 購入完了
残り在庫=8個:ユーザー 3 が1個購入します
残り在庫=7個:ユーザー 3 購入完了
残り在庫=7個:ユーザー 4 が1個購入します
残り在庫=6個:ユーザー 4 購入完了
残り在庫=6個:ユーザー 5 が1個購入します
残り在庫=5個:ユーザー 5 購入完了
残り在庫=5個:ユーザー 6 が1個購入します
残り在庫=4個:ユーザー 6 購入完了
残り在庫=4個:ユーザー 7 が1個購入します
残り在庫=3個:ユーザー 7 購入完了
残り在庫=3個:ユーザー 8 が1個購入します
残り在庫=2個:ユーザー 8 購入完了
残り在庫=2個:ユーザー 9 が1個購入します
残り在庫=1個:ユーザー 9 購入完了
残り在庫=1個:ユーザー 10 が1個購入します
残り在庫=0個:ユーザー 10 購入完了
完了!
残り=0
mysql> select * from stocks;
+----+---------+----------+
| id | name    | quantity |
+----+---------+----------+
|  1 | 商品1   |        0 |
+----+---------+----------+
mysql> select * from orders;
+---------+---------+----------+
| item_id | user_id | quantity |
+---------+---------+----------+
|       1 |       1 |        1 |
|       1 |       2 |        1 |
|       1 |       3 |        1 |
|       1 |       4 |        1 |
|       1 |       5 |        1 |
|       1 |       6 |        1 |
|       1 |       7 |        1 |
|       1 |       8 |        1 |
|       1 |       9 |        1 |
|       1 |      10 |        1 |
+---------+---------+----------+

この場合は注文が逐次に処理されるので注文数と在庫数の整合は取れています。 しかし、次のように customer.sh の実行に & をつけてバックグラウンドに回し、並行化するとどうでしょうか?

■main.sh

#!/bin/sh

. ./lib.sh

# リセット処理
query "update orders set quantity = 0"
query "update stocks set quantity = 10 where id = 1"

echo "買い物中..."

for i in `seq 1 10`;do
    sh customer.sh ${i} & # ← ここに注目
done;
wait

wait
echo "完了!"

rest=`query "select quantity from stocks where id = 1"`
echo "残り=$rest"

結果は

$ sh main.sh
買い物中...
残り在庫=10個:ユーザー 7 が1個購入します
残り在庫=10個:ユーザー 3 が1個購入します
残り在庫=10個:ユーザー 4 が1個購入します
残り在庫=10個:ユーザー 5 が1個購入します
残り在庫=10個:ユーザー 6 が1個購入します
残り在庫=9個:ユーザー 3 購入完了
残り在庫=9個:ユーザー 2 が1個購入します
残り在庫=9個:ユーザー 7 購入完了
残り在庫=9個:ユーザー 5 購入完了
残り在庫=9個:ユーザー 4 購入完了
残り在庫=9個:ユーザー 10 が1個購入します
残り在庫=9個:ユーザー 6 購入完了
残り在庫=8個:ユーザー 2 購入完了
残り在庫=9個:ユーザー 9 が1個購入します
残り在庫=8個:ユーザー 10 購入完了
残り在庫=8個:ユーザー 9 購入完了
残り在庫=8個:ユーザー 8 が1個購入します
残り在庫=8個:ユーザー 1 が1個購入します
残り在庫=7個:ユーザー 8 購入完了
残り在庫=7個:ユーザー 1 購入完了
完了!
残り=7
+----+---------+----------+
| id | name    | quantity |
+----+---------+----------+
|  1 | 商品1   |        7 |
+----+---------+----------+
mysql> select * from orders;
+---------+---------+----------+
| item_id | user_id | quantity |
+---------+---------+----------+
|       1 |       1 |        1 |
|       1 |       2 |        1 |
|       1 |       3 |        1 |
|       1 |       4 |        1 |
|       1 |       5 |        1 |
|       1 |       6 |        1 |
|       1 |       7 |        1 |
|       1 |       8 |        1 |
|       1 |       9 |        1 |
|       1 |      10 |        1 |
+---------+---------+----------+

今度はおかしなことになりました。10人のユーザーの注文が完了したにもかかわらず、 在庫は3つしか減っていません。このままユーザーが注文し続ければ 実際の在庫以上に注文が受け付けられてしまいます。 これは複数のユーザーが(逐次ではなく)同時に在庫を更新する処理が互いに競合し、 正しく処理が行われなかったためです。この問題は並行処理におけるロストアップデートの問題として知られています。

Select for update を使う

これを防ぐ一つの方法として、select for update で更新処理が終わるまで行をロックするというものがあります。 プログラム的には簡単で、最初に残り在庫数を確認する select 文の最後に "for update" をつけるだけです。

. ./lib.sh

query "begin;\
          select quantity from stocks where id = 1 into @q for update;\
          select concat('残り在庫=', @q, '個:ユーザー $1 が1個購入します');\
          update stocks set quantity = @q - 1 where id = 1;\
          update orders set quantity = quantity + 1 where item_id = 1 and user_id = $1;\
          select quantity from stocks where id = 1 into @q;\
          select concat('残り在庫=', @q, '個:ユーザー $1 購入完了');\
       commit;"

実行結果:

$ sh main.sh
買い物中...
残り在庫=10個:ユーザー 2 が1個購入します
残り在庫=9個:ユーザー 2 購入完了
残り在庫=9個:ユーザー 3 が1個購入します
残り在庫=8個:ユーザー 3 購入完了
残り在庫=8個:ユーザー 9 が1個購入します
残り在庫=7個:ユーザー 9 購入完了
残り在庫=7個:ユーザー 4 が1個購入します
残り在庫=6個:ユーザー 4 購入完了
残り在庫=6個:ユーザー 5 が1個購入します
残り在庫=5個:ユーザー 5 購入完了
残り在庫=5個:ユーザー 1 が1個購入します
残り在庫=4個:ユーザー 1 購入完了
残り在庫=4個:ユーザー 7 が1個購入します
残り在庫=3個:ユーザー 7 購入完了
残り在庫=3個:ユーザー 8 が1個購入します
残り在庫=2個:ユーザー 8 購入完了
残り在庫=2個:ユーザー 6 が1個購入します
残り在庫=1個:ユーザー 6 購入完了
残り在庫=1個:ユーザー 10 が1個購入します
残り在庫=0個:ユーザー 10 購入完了
完了!
残り=0

今度はうまくいきました。クエリが同時並行にきても、処理は逐次に実行されています。

http://dev.mysql.com/doc/refman/5.1/ja/innodb-locking-reads.html

によると、

A SELECT ... FOR UPDATE は、読み取る各行上に専用ロックを設定し、最新の有効データを読み取ります。従って、それは SQL UPDATE が行上に設定する物と同じロックを設定します。

(中略)

IN SHARE MODE と FOR UPDATE 読み取りによって設定されたロックは、トランザクションがコミットされたりロールバックされたりした時にリリースされます。

とあります。select for update を発行してからトランザクションをコミット(or ロールバック)するまでは行をロックして 他の注文処理を待たせることができるということですね。

長文になりましたが本日はこのへんで。