2013年6月22日土曜日

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

今回は、SELECT FOR UPDATE時のトランザクションの挙動を確認したいと思います(`・ω・´)ゞビシッ!!
その前に前回のトランザクションを読んでない人は読んでからの方が理解しやすいと思われます(; ・`д・´)

こちらになります。
トランザクションについて
ランザクションについて(その2)

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

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

テーブル定義は次のものを利用しています。
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mail_address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 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;
以上で下準備は完了です( ´∀`)bグッ!

■SELECT FOR UPDATE同士の挙動の確認

・idのカラムにプライマリーキー制約をつけている状態でのSELECT FOR UPDATE;
・最初のSELECT FOR UPDATE時にidを対象にする。

手順1)コンソールAからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順2)コンソールBからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順3)コンソールAからSELECT FOR UPDATEを実行する
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
手順4)コンソールBからSELECT FOR UPDATEを実行する(プライマリーキー制約のあるカラムに対して)

コンソールAでSELECT FOR UPDATEの対象になっているためエラーになる。
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
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)
手順5)コンソールBからSELECT FOR UPDATEを実行する(制約無しのカラムに対して)
mysql> SELECT * FROM user WHERE mail_address = '1@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT * FROM user WHERE mail_address = '2@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> SELECT * FROM user WHERE mail_address = '3@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
手順6)コンソールBからSELECT FOR UPDATEを実行する(制約無しのカラムに対して)
mysql> SELECT * FROM user FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
手順7)コンソールBでSELECT FOR UPDATEを実行した後に、コンソールAでCOMMITしてみる。 実行中になる。
SELECT * FROM user WHERE mail_address = '1@gmail.com' FOR UPDATE;
手順8)コンソールAをCOMMITする
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
手順9)コンソールBで実行中だった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 (6.52 sec)
■SELECT FOR UPDATE同士の挙動の確認(その2)

・idのカラムにプライマリーキー制約、
・mail_addressのカラムにユニーク制約、またはインデックス制約をつけている状態でのSELECT FOR UPDATE;

最初のSELECT FOR UPDATE時にidを対象にする。

mail_addressにユニーク制約を追加する
ALTER TABLE  `test`.`user` ADD UNIQUE  `mail_address` (  `mail_address` );
#ユニーク制約の削除
ALTER TABLE user DROP INDEX mail_address;
#インデックス制約の追加
ALTER TABLE  `test`.`user` ADD INDEX  `mail_address` (  `mail_address` )
手順1)コンソールAからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順2)コンソールBからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順3)コンソールAからSELECT FOR UPDATEを実行する
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
手順4)コンソールBからSELECT FOR UPDATEを実行する(プライマリーキー制約のあるカラムに対して)

コンソールAでSELECT FOR UPDATEの対象になっているためエラーになる。
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
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)
手順5)コンソールBからSELECT FOR UPDATEを実行する(制約無しのカラムに対して)

コンソールAでSELECT FOR UPDATEの対象になっているためエラーになる。
mysql> SELECT * FROM user WHERE mail_address = '1@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mail_addressにユニーク制約をつけたことで、エラーにならなくなった。
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)
mail_addressにユニーク制約をつけたことで、エラーにならなくなった。
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)
手順6)コンソールBからSELECT FOR UPDATEを実行する(制約無しのカラムに対して)
mysql> SELECT * FROM user FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
■SELECT FOR UPDATE同士の挙動の確認(その3)

・idのカラムにプライマリーキー制約、
・mail_addressのカラムにユニーク制約、またはインデックス制約をつけている状態でのSELECT FOR UPDATE;

最初のSELECT FOR UPDATE時にmail_addressを対象にする。
手順1)コンソールAからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順2)コンソールBからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順3)コンソールAからSELECT FOR UPDATEを実行する
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)
手順4)コンソールBからSELECT FOR UPDATEを実行する

コンソールAのSELECT FOR UPDATEの対象外のレコードのため取得される。
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE id = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象外のレコードのため取得される。
mysql> SELECT * FROM user WHERE id = 3 FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  3 | 3@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
コンソールAの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)
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE mail_address = '2@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象外のレコードのため取得される。
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)
■SELECT FOR UPDATE同士の挙動の確認(その4)

・idのカラムにプライマリーキー制約をつけている状態でのSELECT FOR UPDATE;
・最初のSELECT FOR UPDATE時に条件式を設定せずに全てのレコードを対象にする。

手順1)コンソールAからトランザクションを開始する。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順2)コンソールBからトランザクションを開始する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
手順3)コンソールAからSELECT FOR UPDATEを実行する 全てのレコードを対象にしたSQLを実行する。
mysql> SELECT * FROM user FOR UPDATE;
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
|  2 | 2@gmail.com  |
|  3 | 3@gmail.com  |
+----+--------------+
3 rows in set (0.00 sec)
手順4)コンソールBからSELECT FOR UPDATEを実行する コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE id = 1 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE id = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE id = 3 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE mail_address = '1@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
mysql> SELECT * FROM user WHERE mail_address = '2@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
コンソールAのSELECT FOR UPDATEの対象レコードのためエラー。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
SELECT FOR UPDATEを使わないでSELECT
mysql> SELECT * FROM user WHERE mail_address = '1@gmail.com';
+----+--------------+
| id | mail_address |
+----+--------------+
|  1 | 1@gmail.com  |
+----+--------------+
1 row in set (0.00 sec)
最初のコンソールAから実行されたSQLが
プライマリーキーやインデックスの制約があるものを指定していないため、
テーブル全体にロックがかかっているような状態になってしまっている?

そのため、後から実行されているコンソールBからの全てのSQLはエラーになってしまう。
(コンソールAのトランザクションが完了すればエラーにならない)

ただし、コンソールBから実行する際に通常のSELECTの場合には取得できる。

■SELECT FOR UPDATE同士の挙動の確認(その5)

・idのカラムにプライマリーキー制約をつけている。
・mail_addressにはインデックスやユニーク制約を一切つけていない状態。
・ただしSELECT FOR UPDATE時に条件式には、mail_addressを利用する
(制約がないカラムを指定するため全ての行にロックがされる)
・コンソールBからUPDATEやINSERTを行ってみる。

手順1)コンソールAでトランザクションを開始して、SELECT FOR UPDATEを実行する
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

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)
手順2)コンソールBでトランザクションを開始して、SELECT FOR UPDATEを実行する

コンソールAで制約がないカラムを指定して、SELECT FOR UPDATEを実行しているため、
レコード全体にロックがかかってしまっている状態なので、
INSERT、UPDATEなどの操作は行えない(´;ω;`)ウッ…
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user WHERE mail_address = '3@gmail.com' FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> INSERT INTO user( mail_address ) VALUES( '4@gmail.com' );
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> UPDATE user SET mail_address = '4@gmail.com' WHERE mail_address = '3@gmail.com';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> DELETE FROM user WHERE id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

まとめ!(`・ω・´)シャキーン
・SELECT FOR UPDATEを実行時の注意
 プライマリーキーやインデックスの制約があるカラムを
 条件式で利用しているかしていないかで、その後に実行されるSELECT FOR UPDATEの挙動が変わる。
 (SELECT FOR UPDATEを利用していないSELECTは対象外)

・SELECT FOR UPDATEを実行した際に、プライマリーキーやインデックスの制約があるカラムを対象に絞込みをしている場合
 対象のレコードのみにロックがかかり、別のトランザクションから対象のレコード以外をSELECT FOR UPDATEで参照が可能になる。

・SELECT FOR UPDATEを実行した際に、プライマリーキーやインデックスの制約があるカラムを対象に絞込みをしていなかった場合
 対象のテーブル全体にロックがかかり、別のトランザクションから全てのレコードの参照が待たされる。

・SELECT FOR UPDATEでロックされている間は、INSERT、UPDATEなどは行えない。

次回はこのSELECT FOR UPDATEの使いどころについて考えてみたいと思います(ΦωΦ)フフフ…

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

参考URL

0 件のコメント:

コメントを投稿