2013年6月22日土曜日

トランザクションについて(SELECT FOR UPDATEその2)

今回は、前回の「トランザクションについて(SELECT FOR UPDATE)」に続きSELECT FOR UPDATEについてになります。

前回は挙動を確認したので、今回は実際にどういう場面で使えるのか試してみたいと思います。

想定する場面としては、ユーザーの会員登録などで何かを登録する際に
登録するデータがユニークである必要がある場合などになるかと思います。
(テーブル定義でユニーク制約がつけれない場合)

たとえばユーザー会員登録の場合に、
メールアドレスが既に登録されているかされていないかを判定する場合になります。

プログラムの流れ的には、

処理1.メールアドレスが既に登録されているか重複チェック。
処理2.重複していなければ登録。

という流れになると思います。

この際にサーバーが重かったり、登録処理に時間がかかったりしてしまい、
同時に処理1の重複チェック実行されてしまった場合に、
同じメールアドレスが登録データとして投げ込まれてきた場合にはどうなるでしょうか?(; ・`д・´)

答えは・・・両方と登録されてしまうド━━━━m9(゚∀゚)━━━━ン!!

( ´゚д゚`)エーって、言ってるそこの人っ!
実際に流れを確認してみましょう( ̄ー ̄)ニヤリ

■下準備

同じMySQLサーバーに別々のコンソールからログインして2つ開きます。
(以下、コンソールA、コンソールBとします。)

データベースにはMySQLのデフォルトで作られているtestを利用して、
テーブルはuserというテーブルを作って利用します。;

テーブル定義は次のものを利用しています。 mail_addressにはインデックスがつけられています。
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mail_address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mail_address` (`mail_address`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
コンソールAとコンソールBでauto_commitを無効化にします。
#自動コミットを無効化
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

#設定の確認
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)
あらかじめ次のデータをINSERTしておきます。
BEGIN;

INSERT INTO user( mail_address ) VALUES( '1@gmail.com' );
INSERT INTO user( mail_address ) VALUES( '2@gmail.com' );
INSERT INTO user( mail_address ) VALUES( '3@gmail.com' );

COMMIT;

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)

以上で下準備は完了です( ´∀`)bグッ!

■同時に同じメールアドレスが送信されてきた場合を想定した処理順に実行する

登録されるメールアドレスとして、「4@gmail.com」を利用します。

手順1)コンソールAでトランザクションを開始して、登録しようとしている「4@gmail.com」が既にあるか確認する。
#トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
4@gmail.com」が既にあるか確認
mysql> SELECT * FROM user WHERE mail_address = '4@gmail.com';
Empty set (0.00 sec)
「4@gmail.com」のデータが無かったので、登録を行う。
mysql> INSERT INTO user( mail_address ) VALUES( '4@gmail.com' );
Query OK, 1 row affected (0.00 sec)
登録が完了するけど、COMMITをせずにトランザクションを維持させておきます。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  4 | 4@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順2)コンソールBでトランザクションを開始して、登録しようとしている「4@gmail.com」が既にあるか確認する。

トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
「4@gmail.com」が既にあるか確認
mysql> SELECT * FROM user WHERE mail_address = '4@gmail.com';
Empty set (0.00 sec)
ここでさっきコンソールAで登録したはずの「4@gmail.com」が見つからないです(;゚ Д゚) …!?
なので、プログラム的には登録処理へ進んでしまうことになってしまいます。エッ(゚Д゚≡゚Д゚)マジ?

手順3)コンソールAでトランザクションを終了する。

トランザクションを終了
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

#データの反映を確認する
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  4 | 4@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順4)コンソールBでトランザクションを終了する。

トランザクションを終わらせる前に確認する。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  5 | 4@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
トランザクションを終了
mysql> COMMIT;
Query OK, 0 rows affected (0.04 sec)
データの反映を確認する
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  4 | 4@gmail.com  |
|  5 | 4@gmail.com  |
+----+--------------+
5 rows in set (0.00 sec)
トランザクションを終了する前には無かった、
コンソールAで登録したデータの
|  4 | 4@gmail.com  |
が出現しています(;・∀・)ハッ?

また、「4@gmail.com」が2つ登録されてしまいました!((((;゚Д゚))))ガクガクブルブル

これは、以前書いた記事の「トランザクションについて(その2) 」の通りで、
InnoDBのデフォルトのトランザクション分離レベルがREPEATABLE-READのために発生する現象になります。

トランザクションが開始された時点のSELECT時のスナップショットを保持し続けて利用するため、
今回はコンソールAのトランザクションが完了していない状態なので、
コンソールBでトランザクションを開始したタイミングでは、
コンソールAで追加した「4@gmail.com」がまだ登録されていません。

これにより、コンソールBでの「4@gmail.com」の重複チェックでは検知できずに、
コンソールBでも登録処理が実行されてしまいますガ━━(;゚Д゚)━━ン!!

これを防ぐ方法として、以下の方法があるそうです。

共有ロックのSELECT LOCK IN SHARE MODE
排他ロックのSELECT FOR UPDATE
(SERIALIZEBLE分離レベルを利用することで防ぐことも可能。)

今回は、SELECT FOR UPDATEを利用してみたいと思います。
では、さっきの手順の中にSELECT FOR UPDATEを入れて再度試してみたいと思います。

■同時に同じメールアドレスが送信されてきた場合を想定した処理順に実行する(SELECT FOR UPDATE)

一旦データをリセットします。
BEGIN;

TRUNCATE TABLE user;

INSERT INTO user( mail_address ) VALUES( '1@gmail.com' );
INSERT INTO user( mail_address ) VALUES( '2@gmail.com' );
INSERT INTO user( mail_address ) VALUES( '3@gmail.com' );

COMMIT;
前回と同じように、登録されるメールアドレスとして、「4@gmail.com」を利用します。

手順1)コンソールAでトランザクションを開始して、登録しようとしている「4@gmail.com」が既にあるか確認する。

トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
「4@gmail.com」が既にあるか確認
mysql> SELECT * FROM user WHERE mail_address = '4@gmail.com' FOR UPDATE;
Empty set (0.00 sec)
「4@gmail.com」のデータが無かったので、登録を行う。
mysql> INSERT INTO user( mail_address ) VALUES( '4@gmail.com' );
Query OK, 1 row affected (0.00 sec)
登録が完了するけど、COMMITをせずにトランザクションを維持させておきます。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  4 | 4@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順2)コンソールBでトランザクションを開始して、登録しようとしている「4@gmail.com」が既にあるか確認する。
トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
「4@gmail.com」が既にあるか確認
mysql> SELECT * FROM user WHERE mail_address = '4@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
エラー(゚∀゚)キタコレ!!

これは、コンソールAでSELECT FOR UPDATEを利用したために、
対象のレコードに対してロックがかかっている状態になっています。

他のレコードに対してはロックはかかっていないようです。
※mail_addressにインデックス制約がかかっているため。
もしmail_addressにインデックスやユニーク制約がついていない場合には、
レコード全体にロックがかかってしまいますので注意が必要です
詳細は以前の「トランザクションについて(SELECT FOR UPDATE) 」の記事を参照してください。
mysql> SELECT * FROM user WHERE mail_address = '1@gmail.com' FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user WHERE mail_address = '2@gmail.com' FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 2@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user WHERE mail_address = '3@gmail.com' FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 3@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user WHERE mail_address = '5@gmail.com' FOR UPDATE;
Empty set (0.00 sec)
プライマリーキーのidでも試してみる。
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user WHERE id = 2 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  2 | 2@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user WHERE id = 3 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 3@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

#コンソールAで挿入されたプライマリーキーのid値はエラーになる。
mysql> SELECT * FROM user WHERE id = 4 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SELECT * FROM user WHERE id = 5 FOR UPDATE;
Empty set (0.00 sec)
条件無しでやってみる。
mysql> SELECT * FROM user FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
手順3)コンソールAでトランザクションを終了する。
#トランザクションを終了
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

#データの反映を確認する
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  4 | 4@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
手順4)コンソールBでトランザクションを終了する。

コンソールAのトランザクションを終わらせたので、
再度、コンソールBから確認してみる。

その前に、SELECT FOR UPDATEを使わずに、
通常のSELECTを実行してみる。
mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
コンソールAのデータの追加は反映されていない模様。
今度はSELECT FOR UPDATEを使って確認する。
mysql> SELECT * FROM user WHERE mail_address = '4@gmail.com' FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  4 | 4@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)

ありゃ、データがあることになっている(゚д゚)!
ただ、SELECT FOR UPDATEを外すとやっぱり無いとはいわれるけど!(´・∀・`)ヘー
mysql> SELECT * FROM user WHERE mail_address = '4@gmail.com';
Empty set (0.00 sec)
トランザクションを終了させる。
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

データの確認をする。

mysql> SELECT * FROM user;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
|  4 | 4@gmail.com  |
+----+--------------+
4 rows in set (0.00 sec)
この結果、重複してデータが被ることはなくなりますド━━━━m9(゚∀゚)━━━━ン!!

(・∀・)イイネ!!

注意点としては、SELECT FOR UPDATEを使う際に、
レコードの絞込みを行うことと、絞込みを行う際に対象のカラムにユニークやインデックス制約をつけておく点になります。

これが行われていないと、SELECT FOR UPDATEした時点でロックされてしまい、
そのトランザクションが終わるまで、そのテーブルに対して更新処理が待たされてしまいます。
※詳細は以前の「トランザクションについて(SELECT FOR UPDATE) 」の記事を参照してください。

そもそも、mail_addressにユニーク制約つけちゃえば問題ないのですが
大人の事情でつけれない場合などに使える手段として、SELECT FOR UPDATEを使うって感じです(; ・`д・´)

以上です(`・ω・´)ゞビシッ!!

参考URL

0 件のコメント:

コメントを投稿