条件付きINSERT

Question

お世話になっております。

MySQLで、テーブルAに格納されているログデータのある賞品番号のカウント数が、テーブルBに格納されているその賞品の規定値未満だった時だけ、テーブルAにログデータをINSERTしたいと考えています。

テーブルA：
ID｜賞品番号｜日時 ｜当たり外れ
—————————————————————————————
1 ｜0001 ｜2016-11-02 09:03:23 ｜ 1
2 ｜0002 ｜2016-11-02 09:03:28 ｜ 1
3 ｜0003 ｜2016-11-03 09:16:23 ｜ 1
4 ｜0002 ｜2016-11-04 09:38:23 ｜ 1
5 ｜0000 ｜2016-11-05 09:45:23 ｜ 0

テーブルB：
ID｜賞品番号 ｜規定値 ｜現在の当選数｜更新日時
——————————————————————————————
1 ｜0001 ｜2000 ｜ 1450 ｜2016-11-02 09:03:23
2 ｜0002 ｜3000 ｜ 2378 ｜2016-11-02 09:38:23
3 ｜0003 ｜1000 ｜ ** 999 ｜2016-11-03 09:16:23**

テーブルC：
ID｜賞品番号 ｜勝率 ｜日時
—————————————————————————
1 ｜0001 ｜ 10 ｜2016-11-02 09:03:23
2 ｜0002 ｜ 30 ｜2016-11-02 09:38:23
3 ｜0003 ｜ 50 ｜2016-11-03 09:16:23

テーブルD：
ID｜ユーザID ｜IP ｜性別 ｜日時
———————————————————————————————
1 ｜foo ｜192.168.22.12 ｜ 1 ｜2016-11-02 09:03:23
2 ｜baa ｜192.168.22.13 ｜ 0 ｜2016-11-02 09:03:28
3 ｜pee ｜192.168.22.14 ｜ 1 ｜2016-11-03 09:16:23

今現在、SELECT文でcount数と規定値の比較結果を取得してからINSERTをしていますが、テーブルAのカウント数は常に変わっているため、万一同時アクセスされた場合、規定値を超えてしまって整合性が取れなくなるのではと心配しています。

条件付きインサートで検索してもめぼしい情報が得られません。
何か整合性を確実に保つ良い方法はありますでしょうか。

よろしくお願いします。

【追記】ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
とてもご丁寧にご回答頂いて大変恐縮なのですが、その後やや仕様が追加されてしまいました（テーブルの追加箇所は太字表記）。
ただ皆さんのご回答を読んでも消化できていない部分があるため、それはそれとしてご回答にいくらか質問をさせて頂きたいと思いますが、できましたらこれらの変更や懸念点をふまえてご回答頂けますと幸いです。（因みに、新規にDBを作成するため、DB単位での設定も可能です。）宜しくお願いします。

1. テーブルAに「当り外れ」を格納するカラム追加。ただし、各賞品のくじを順番に引いて、当りが出た時点でインサートとなるため、ハズレの賞品番号は常に0000となります。
2. 各賞品の勝率情報を格納するテーブルCが追加されました。
3. テーブルAへデータ挿入のタイミングでテーブルDにユーザ情報をインサートすることになりました。
4. テーブルBに「現在の当選数」のカラムが追加になりました→当りが出たときだけUPDATEする。
5. ロックをかけた際にデッドロックの懸念が上がっているため、その辺をできるだけ回避できる方式だと望ましいです

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー/【追記】

Comments

[kantomi]

用途を書いた方が良いと思います。カウントを取ったテーブルに追加すると、次の瞬間にカウント＋１になります。繰り返せば規定値に近づくでしょう。それに何の意味があるのか全く理解できません。

[kantomi]

ある条件のときだけ記録するのは、ログとは言いません。

[squirrel]

用途はロッテリーです。規定値は提供数上限であり、その上限があるのはキャンペーン資金に上限があるためです。

Answer 1

ロッテリーということで、
上の条件であれば、ログというよりも、「申し込み」とか「受付」テーブルの意味かな。
2つぐらい処理が考えられます。

■ Aパターン
INSERTされるのを防ぐには、テーブルロックしかなくなるためとりあえず、ステータス0で全部INSERTしておき、バッチ処理でステータスを一括更新更新する。
日付はタイムスタンプにするべきですが、idが連番なら若い方から規定値数までフラグを立てれば良いでしょう。

テーブルA：
ID｜賞品番号｜日付 ｜STS｜
————————————————————
1 ｜0001 ｜2016-11-02 ｜ 1｜
2 ｜0002 ｜2016-11-02 ｜ 1｜
3 ｜0003 ｜2016-11-03 ｜ 1｜
4 ｜0002 ｜2016-11-04 ｜ 0｜

■ Bパターン
テーブルBに最終処理時間のカラムを追加する。

テーブルB：
ID｜賞品番号 ｜規定値 ｜最終処理時間｜
——————————————————
1 ｜0001 ｜2000 ｜タイムスタンプ｜
2 ｜0002 ｜3000 ｜タイムスタンプ｜
3 ｜0003 ｜1000 ｜タイムスタンプ｜

以下の処理を行う。

1. トランザクション開始
2. テーブルB から、対象の賞品番号を取得する。SELECT ... FOR UPDATE で取得する。
3. テーブルA の対象賞品番号のレコードが、規定値以内なら追加する。
4. テーブルBの最終処理時間を更新する。
5. テーブルBのリザルトセットを開放する（ロックが外れる）
6. コミットしてトランザクションを終える。

という流れなら、ロックが掛かるのはテーブルBの対象賞品のレコードだけですが、同時実行されたとき、遅い方の処理は、2のところで待たされることになるため、結果的に順番に処理でき規定値を超えることはなくなります。

※ 私ならAB両方のハイブリッドで実装しますけど。

Comments

[squirrel]

２パターン示して頂いて有り難うございました。パターンAがよく咀嚼できなかったのですが、最初のインサート時にSTS（勝ち負けステータス？）を入れ籠まないのはなぜでしょうか。テーブルAは負けても結果をインサートするので、IDと当選結果とのペアをどこかに保持しておく必要があるように思えます。

SELECT ... FOR UPDATEは使用していないものの、今現在、2の段階でテーブルBとCを結合した情報をもってくじ引きをし、その結果とユーザ情報をテーブルAとDにINSERT、テーブルBに「現在の当選数」に１足したものをUPDATEしてコミットしています。
トランザクションの使用で整合性は保たれるとは思いますが、懸念点されている点は、ある程度の過密アクセスにも絶えうるシステムにするという目的のためにデッドロックの心配があることです。この辺については現在自分で勉強中ですが、非ロック参照系（SELECT ... FOR UPDATE）さえ使用すれば、その後、複数のINSERTやUPDATEをしても果たして回避できるものでしょうか。結合でSELECT ... FOR UPDATEした場合の挙動も不明です。

[squirrel]

誤）非ロック参照系（SELECT ... FOR UPDATE）→SELECT ... FOR UPDATEはロック系参照でした。

Answer 2

ロック読み取りを使えば解決できると思います。

「MySQLのINSERT/UPDATE時におこる不整合対策」ここの解説が詳しくてわかりやすいですよ。

Comments

[squirrel]

これは分かりやすいですね。楽観的ロックの手法は使えそうです。もう少しほじって勉強してみます。

Answer 3

INSERT ... SELECT 構文を使用すると、明示的なロックを取得する必要なくデータの整合性を保つための「条件付きインサート」を実現できます。

なぜなら、INSERT ... SELECT 構文は自動的に INSERT するテーブルおよび SELECT するテーブルに適切なロックをかけるためです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-locks-set.html

INSERT INTO T SELECT ... FROM S WHERE ... は、T に挿入された各行に、ギャップロックなしの排他インデックスレコードロックを設定します。トランザクション分離レベルが READ COMMITTED である場合、または innodb_locks_unsafe_for_binlog が有効になっていて、トランザクション分離レベルが SERIALIZABLE でない場合、InnoDB は一貫性読み取り (ロックなし) として S 上で検索を実行します。それ以外の場合、InnoDB は S から取得した行に共有ネクストキーロックを設定します。

若干 複雑ですが、例えば以下のようなSQL文になります。
※ 商品番号'0001'を挿入する場合

INSERT INTO `テーブルA` (`商品番号`, `日付`) 
SELECT b.`商品番号`, CURDATE() 
FROM `テーブルB` AS b 
LEFT OUTER JOIN `テーブルA` AS a ON b.`商品番号` = a.`商品番号`
WHERE b.`商品番号` = '0001' 
GROUP BY b.`商品番号`, b.`規定値` 
HAVING b.`規定値` > COUNT(a.`商品番号`);

mysql> CREATE TABLE `テーブルA` (
    ->   ID int PRIMARY KEY AUTO_INCREMENT,
    ->   `商品番号` varchar(4) NOT NULL,
    ->   `日付` DATE NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `テーブルB` (
    ->   ID int PRIMARY KEY AUTO_INCREMENT,
    ->   `商品番号` varchar(4) NOT NULL,
    ->   `規定値` int NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `テーブルA` (`商品番号`, `日付`) VALUES
    -> ('0001', '2016-11-02'),
    -> ('0002', '2016-11-02'),
    -> ('0003', '2016-11-03'),
    -> ('0002', '2016-11-04');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `テーブルB` (`商品番号`, `規定値`) VALUES 
    -> ('0001', 2),
    -> ('0002', 2),
    -> ('0003', 2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `テーブルA`;
+----+--------------+------------+
| ID | 商品番号     | 日付       |
+----+--------------+------------+
|  1 | 0001         | 2016-11-02 |
|  2 | 0002         | 2016-11-02 |
|  3 | 0003         | 2016-11-03 |
|  4 | 0002         | 2016-11-04 |
+----+--------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM `テーブルB`;
+----+--------------+-----------+
| ID | 商品番号     | 規定値    |
+----+--------------+-----------+
|  1 | 0001         |         2 |
|  2 | 0002         |         2 |
|  3 | 0003         |         2 |
+----+--------------+-----------+
3 rows in set (0.00 sec)

mysql> INSERT INTO `テーブルA` (`商品番号`, `日付`) 
    -> SELECT b.`商品番号`, CURDATE() 
    -> FROM `テーブルB` AS b 
    -> LEFT OUTER JOIN `テーブルA` AS a ON b.`商品番号` = a.`商品番号`
    -> WHERE b.`商品番号` = '0001' 
    -> GROUP BY b.`商品番号`, b.`規定値` 
    -> HAVING b.`規定値` > COUNT(a.`商品番号`);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `テーブルA`;
+----+--------------+------------+
| ID | 商品番号     | 日付       |
+----+--------------+------------+
|  1 | 0001         | 2016-11-02 |
|  2 | 0002         | 2016-11-02 |
|  3 | 0003         | 2016-11-03 |
|  4 | 0002         | 2016-11-04 |
|  5 | 0001         | 2016-10-11 |
+----+--------------+------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO `テーブルA` (`商品番号`, `日付`) 
    -> SELECT b.`商品番号`, CURDATE() 
    -> FROM `テーブルB` AS b 
    -> LEFT OUTER JOIN `テーブルA` AS a ON b.`商品番号` = a.`商品番号`
    -> WHERE b.`商品番号` = '0001' 
    -> GROUP BY b.`商品番号`, b.`規定値` 
    -> HAVING b.`規定値` > COUNT(a.`商品番号`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `テーブルA`;
+----+--------------+------------+
| ID | 商品番号     | 日付       |
+----+--------------+------------+
|  1 | 0001         | 2016-11-02 |
|  2 | 0002         | 2016-11-02 |
|  3 | 0003         | 2016-11-03 |
|  4 | 0002         | 2016-11-04 |
|  5 | 0001         | 2016-10-11 |
+----+--------------+------------+
5 rows in set (0.00 sec)

---

上記 SQL文によってデータの整合性を保てることを検証するため、以下の手順を実行してみてください。
0. ターミナルを複数 起動し、それぞれのターミナルから MySQLサーバにログインする。
0. １つのターミナルから BEGIN コマンドに続けて上記 SQLを実行する。
ただし、COMMIT および ROLLBACK はしない。
0. 別のターミナルから、テーブルA および テーブルB に対して様々なSQL文を実行してみる。

"3."において、INSERT が実行された商品番号に対する更新系のクエリだけがロックされることが確認できると思います。

以下追記

私の回答のコメント欄に追記いただいた内容を鑑みるに、私の当初の回答は実現したいことにそぐわないです。

当初の回答は
「1回の試行で、ユーザーが選択したただ1つの商品の当たり外れを判定する」
という仕様を想定したものでしたので。

---

で、どうすれば良いかと言うと、 SELECT ... FOR UPDATE 構文を使用するのが良さそうです。

ワンクエリで全ての処理を実行できればベターではありますが、要件が複雑なため、できそうにありません。
（少なくとも、私にはどのようなSQLを書けば良いか思いつきませんorz）

以下のようにSQLを実行すれば、テーブルBおよびテーブルCの現在の当選数が規定値に達していないレコードをロックしつつ、賞品の"当たり外れ"を判定するのに必要なデータを取得できます。

BEGIN;

SELECT b.`賞品番号`, b.`規定値`, b.`現在の当選数`, c.`勝率` 
FROM `テーブルB` AS b 
INNER JOIN `テーブルC` AS c ON b.`賞品番号` = c.`賞品番号` 
WHERE b.`規定値` > b.`現在の当選数` 
FOR UPDATE;

あとは
0. （PHPなどの）アプリケーション側で"当たり外れ"の判定を行ない、
0. その結果を各テーブルに INSERT または UPDATE していき、
0. 最後に COMMIT

してやれば十分です。

---

"過密アクセス"が懸念されるなら、上述の一連の操作をストアドプロシージャーとして定義してやれば、多少はパフォーマンスが改善するかも知れません。

ストアドプロシージャーなら 1回の通信で複数のSQL文を実行できるため、
アプリケーション <-> MySQL間の通信の往復を減らせるからです。

その場合、OUT パラメータで賞品番号（ハズレの場合は'0000'）を返してやれば、
アプリケーション側でどの賞品が当たったのか、あるいは外れたのかを判断できます。

別のアプローチとして、
「"当たり外れ"の判定を非同期化してしまう」
という手もあります。

（ご質問の機能がWEBサービスの一部だと仮定して）ユーザーが"くじを引く"というボタンを押したらすぐに結果ページを表示してしまい、
例えば「当たり」または「外れ」という文言の部分だけを、処理が完了するまで"進捗インジケータ"を表示するようにしておくのです。

たいていのユーザーは開こうとしたページがなかなか表示されないとストレスを感じますが、
このようにしておけば、数秒以内のタイムラグであればドキドキしながら(w)待ってくれるはずです。

---

ただし、上述の方法で保証できるのは「データの整合性」だけです。

「デッドロックの防止」については、
テーブルA, B, C, D（および、その他 本機能で更新系クエリを実行する全てのテーブル）にアクセスする全てのプロセスが、該当テーブルのロックを取得する順番を揃える必要があります。

具体的には、
上述の方法ではテーブルB, Cのロックを取得してから、その他のテーブルのロックを取得しています。
しかし、他のプロセスが「その他のテーブル」のロックを取得してからテーブルB, Cのロックを取得しようとすると、
デッドロックの原因となります。

デッドロックの対処については、以下のリンクが参考になるかと思います。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-deadlocks.html

ついでに、
以下のページを読むと SELECT ... FOR UPDATE 構文の挙動について 理解の助けになるかと思います。
http://nippondanji.blogspot.jp/2013/12/innodbrepeatable-readlocking-read.html

Comments

[squirrel]

ありがとうございます。これは「規定値を上回っていない時だけINSERTをする」という条件付きINSERTそのものですね。規定値についてはこれで解決できそうですが、追記させて頂いた通り、もう一つINSERTするテーブルとUPDATEテーブルが出て来たため、現在それらの整合性をどうとろうかかと思案中です。

[KiyoshiMotoki]

squirrel様

返信および情報の追記、ありがとうございます。

しかし、かえって内容に曖昧だと感じる部分が出てきてしまいましたので、
以下についてご質問の追記・補足をお願いします。

・「各賞品のくじを順番に引いて」とは、どういう意味か？
　ユーザーは1回の試行で1本だけくじを引くのではなく、
　全ての賞品の当たり外れを1回ずつ、判定するのか？
　後者の場合、”テーブルB”の”勝率”カラムは
　「どの賞品が当たるか」を判定するためのものなのか？

・「新規にDBを作成するため、DB単位での設定も可能です。」の
　「DB単位での設定」とは、どのような意味か？

・”テーブルA”に商品番号”0000"のレコードを登録する意図は何か？
　現状のテーブル設計だと
　「ある日時に誰かがハズレを引いた」ということしか分からないため。

・"テーブルD"には他のテーブルと紐づくカラムが無いが、それで問題ないのか？

・ついでにMySQLのバージョン。
　バージョンによって使える機能 / 使えない機能があるため。

[squirrel]

分かりづらくてすみません。以下返信です。

＞「各賞品のくじを順番に引いて」とは、どういう意味か？

利用者は1回の抽選で、規定値に達していない全賞品を各勝率に基づいてくじを引き、1つ当たった時点でループを抜けます。全部はずれたらハズレ判定となります。

＞「DB単位での設定」とは、どのような意味か？
DB作成からの作業になるので作成時にDB自体の設定が可能という意味でした。

ハズレの際にテーブルAに賞品番号を入れる理由は特に無い…と言うより、個人的にはこのカラムだけ見れば当たりハズレが分かるので、寧ろ「当たり外れ」カラムの方が要らないのではと思っているのですが、外部的要望に基づいてこのようになっています。

＞"テーブルD"には他のテーブルと紐づくカラムが無いが、それで問題ないのか？
すみません。実際には、テーブルAとDを紐づける申込受付テーブルZがあり、両テーブルにそのテーブルのidが入るのですが、そうするとインサートテーブルだらけで無駄に複雑になるため、ここではテーブルAのidを格納するカラムa_idがテーブルDにあると考えておいてください。
（後で元の投稿に追記します）

＞MySQLのバージョン
Ver 14.14 Distrib 5.6.26, for Linuxです。

よろしくお願いします。

[KiyoshiMotoki]

squirrel様

返信ありがとうございます。

回答を追記しましたので、確認願います。

あと、コメントに追記いただいた内容は、質問欄にも記載しておくことをお勧めします。
そうすれば、より多くの人の目に留まりやすくなりますので。

[squirrel]

大変丁寧な解説をありがとうございます。
他の皆さんも非常に丁寧にご回答くださってますので、これ以上はご回答と頂いたリンクを参照しながらやってみて、また疑問点が出て来た時に質問させて頂いた方が良いような気がして来ました。この辺りは勉強不足でしたのでこれを機に穿ってみようと思います。大変有用な情報ありがとうございます。

[KiyoshiMotoki]

b

Answer 4

トランザクションで処理すれば規定値を越えることは
ないと思いますが、どうしても気になるなら
データ投入後規定値をオーバーしていたらデータ削除
されるようにすればいいでしょう

追記

普通にプロシージャでトランザクション処理すればよいような気もしますが

• テーブル作成とテーブルBに基本データ投入

create table tbl_a(id int not null primary key auto_increment,no int,dt datetime,hit tinyint);
create table tbl_b(no int not null primary key,max int,hit int,dt datetime);
insert into tbl_b values(1,3,0,0),(2,10,0,0),(3,10,0,0);

• プロシージャ作成

DROP PROCEDURE IF EXISTS ADD_TBL_A;
DELIMITER //
CREATE PROCEDURE ADD_TBL_A(IN a INT)
BEGIN
START TRANSACTION;
SET @now:=NOW();
INSERT INTO tbl_a SET no=a,dt=@now,hit=1;
SET @a:=(SELECT max FROM tbl_b WHERE no=a);
SET @b:=(SELECT COUNT(*) FROM tbl_a WHERE no=a);
IF @a<@b THEN
ROLLBACK;
ELSE 
UPDATE tbl_b SET hit=@b,dt=@now WHERE no=a;
COMMIT;
END IF;
END
//
DELIMITER ;

• データ投入

call ADD_TBL_A(1);

• 上記例では1を3回投入するとtbl_bの制約に引っかかってrollbackされる
• tbl_bを更新するのと同様にtbl_cがあれば合わせて更新すればOK