MySQLとPostgreSQLのRepeatable Read時の挙動の違いについて
    このエントリをはてなブックマークに登録

2010/7/2 金曜日 matsui Posted in サーバ | 1 Comment »

モバイルとは全く関係ないですが、自分のメモ代わりに記事にしてみたいと思います。

たまたまテストしていて、MySQLとPostgreSQLのRepeatable Read時の挙動の違いを見つけました。

 

AさんとBさんという二人のユーザが同時に一つのレコードを更新している場合です。

user_tblのuser_typeというカラムを「0」→「1」にアップデートしています。

分離レベルはMySQL、PostgreSQLともにRepeatable Readです。

 

Aさん
Bさん
mysql> begin;
Query OK, 0 rows affected (0.00 sec)		 トランザクション開始
mysql> select user_type from user_tbl where user_id
= 1000;
+———–+
| user_type |
+———–+
| 0 |
+———–+
1 row in set (0.00 sec)		 参照
mysql> begin;
Query OK, 0 rows affected (0.00 sec)		 トランザクション開始
mysql> select user_type from user_tbl where user_id
= 1000 for update;
+———–+
| user_type |
+———–+
| 0 |
+———–+
1 row in set (0.00 sec)		 行ロック
mysql> update user_tbl set user_type = 1 where user_id
= 1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0		 更新
mysql> commit;
Query OK, 0 rows affected (0.05 sec)		 コミット
mysql> select user_type from user_tbl where user_id
= 1000;
+———–+
| user_type |
+———–+
| 0 |
+———–+
1 row in set (0.00 sec)		 参照（ロックなし）
mysql> select user_type from user_tbl where user_id
= 1000 for update;
+———–+
| user_type |
+———–+
| 1 |
+———–+
1 row in set (0.00 sec)		 参照（ロックあり）

※結果が違うことに注意

 

最後のSELECTが、「FOR UPDATE」の有り無しで違う結果を返していることにご注意ください。

FOR UPDATE以外は全く同じSQL文なのに、結果が違うというのは、少し違和感がありますね。

 

Aさん
Bさん
test=# begin;
BEGIN		 トランザクション開始
test=# select user_type from user_tbl where user_id = 1000;
user_type
———–
1
(1 row)		 参照
test=# begin;
BEGIN		 トランザクション開始
test=# select user_type from user_tbl where user_id = 1000 for update;
user_type
———–
1
(1 row)		 行ロック
test=# update user_tbl set user_type = 0 where user_id = 1000;
UPDATE 1		 更新
test=# commit;
COMMIT		 コミット
test=# select user_type from user_tbl where user_id = 1000;
user_type
———–
0
(1 row)		 参照（ロックなし）
test=# select user_type from user_tbl where user_id = 1000 for update;
user_type
———–
0
(1 row)		 参照（ロックあり）

PostgreSQLの場合は「FOR UPDATE」の有る無しでSELECTの結果は変わりません。
こちらの方が直感的な感じがしますね。

 

（MySQLの内部構造には詳しくないのできっとですが）MySQLでは最初のSELECTでスナップショットが展開されるので、FOR UPDATEで取得した場合とそうじゃない場合でSELECTの結果が違うのだと思います。

「あるテーブルの値を元に、他テーブルの値を更新する」みたいなプログラムの場合、上記の例のようなパターンにはまることがありそうです。

そのような参照の前にはしっかりロックをかけましょう、というそんなお話でした。

 

関連：


タグ: ,

 

最近の記事







One Response to “MySQLとPostgreSQLのRepeatable Read時の挙動の違いについて”

  1. SH2 Says:
    7月 4th, 2010 at 21:03:22

    PostgreSQLはREAD COMMITTEDとSERIALIZABLEしか
    サポートしておらず、REPEATABLE READを要求したときは
    一つ上のSERIALIZABLEで動作します。
    http://www.postgresql.jp/document/current/html/transaction-iso.html

    と、いうことを考慮すると、どちらの挙動も正しいはずです。

コメントを書く

«
»