{"id":3302,"date":"2010-07-02T22:42:06","date_gmt":"2010-07-02T13:42:06","guid":{"rendered":"http:\/\/ke-tai.org\/blog\/?p=3302"},"modified":"2010-07-02T22:42:26","modified_gmt":"2010-07-02T13:42:26","slug":"mysqlpostgresqlrepeatableread","status":"publish","type":"post","link":"http:\/\/ke-tai.org\/blog\/2010\/07\/02\/mysqlpostgresqlrepeatableread\/","title":{"rendered":"MySQL\u3068PostgreSQL\u306eRepeatable Read\u6642\u306e\u6319\u52d5\u306e\u9055\u3044\u306b\u3064\u3044\u3066"},"content":{"rendered":"

\u30e2\u30d0\u30a4\u30eb\u3068\u306f\u5168\u304f\u95a2\u4fc2\u306a\u3044\u3067\u3059\u304c\u3001\u81ea\u5206\u306e\u30e1\u30e2\u4ee3\u308f\u308a\u306b\u8a18\u4e8b\u306b\u3057\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u305f\u307e\u305f\u307e\u30c6\u30b9\u30c8\u3057\u3066\u3044\u3066\u3001MySQL\u3068PostgreSQL\u306eRepeatable Read\u6642\u306e\u6319\u52d5\u306e\u9055\u3044\u3092\u898b\u3064\u3051\u307e\u3057\u305f\u3002<\/p>\n

 <\/p>\n

A\u3055\u3093\u3068B\u3055\u3093\u3068\u3044\u3046\u4e8c\u4eba\u306e\u30e6\u30fc\u30b6\u304c\u540c\u6642\u306b\u4e00\u3064\u306e\u30ec\u30b3\u30fc\u30c9\u3092\u66f4\u65b0\u3057\u3066\u3044\u308b\u5834\u5408\u3067\u3059\u3002<\/p>\n

user_tbl\u306euser_type\u3068\u3044\u3046\u30ab\u30e9\u30e0\u3092\u300c0\u300d\u2192\u300c1\u300d\u306b\u30a2\u30c3\u30d7\u30c7\u30fc\u30c8\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n

\u5206\u96e2\u30ec\u30d9\u30eb\u306fMySQL\u3001PostgreSQL\u3068\u3082\u306bRepeatable Read\u3067\u3059\u3002<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n\n
\n
A\u3055\u3093<\/font><\/div>\n<\/td>\n
\n
B\u3055\u3093<\/font><\/div>\n<\/td>\n<\/tr>\n
mysql> begin;
\n Query OK, 0 rows affected (0.00 sec)<\/font><\/td>\n		\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u958b\u59cb<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n
mysql> select user_type from user_tbl where user_id
\n = 1000;
\n +———–+
\n | user_type |
\n +———–+
\n | 0 |
\n +———–+
\n 1 row in set (0.00 sec)<\/font><\/td>\n		\u53c2\u7167<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\nmysql> begin;
\n Query OK, 0 rows affected (0.00 sec)<\/font><\/td>\n		\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u958b\u59cb<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\nmysql> select user_type from user_tbl where user_id
\n = 1000 for update;
\n +———–+
\n | user_type |
\n +———–+
\n | 0 |
\n +———–+
\n 1 row in set (0.00 sec)<\/font><\/td>\n		\u884c\u30ed\u30c3\u30af<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\nmysql> update user_tbl set user_type = 1 where user_id
\n = 1000;
\n Query OK, 1 row affected (0.00 sec)
\n Rows matched: 1 Changed: 1 Warnings: 0<\/font><\/td>\n		\u66f4\u65b0<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\nmysql> commit;
\n Query OK, 0 rows affected (0.05 sec)<\/font><\/td>\n		\u30b3\u30df\u30c3\u30c8<\/font><\/td>\n<\/tr>\n
mysql> select user_type from user_tbl where user_id
\n = 1000;
\n +———–+
\n | user_type |
\n +———–+
\n | 0 |
\n +———–+
\n 1 row in set (0.00 sec)<\/font><\/td>\n		\u53c2\u7167\uff08\u30ed\u30c3\u30af\u306a\u3057\uff09<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n
mysql> select user_type from user_tbl where user_id
\n = 1000 for update;
\n +———–+
\n | user_type |
\n +———–+
\n | 1 |
\n +———–+
\n 1 row in set (0.00 sec)<\/font><\/td>\n		\u53c2\u7167\uff08\u30ed\u30c3\u30af\u3042\u308a\uff09<\/p>\n

\u203b\u7d50\u679c\u304c\u9055\u3046\u3053\u3068\u306b\u6ce8\u610f<\/b><\/font><\/font><\/td>\n

<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n<\/table>\n

 <\/p>\n

\u6700\u5f8c\u306eSELECT\u304c\u3001\u300cFOR UPDATE\u300d\u306e\u6709\u308a\u7121\u3057\u3067\u9055\u3046\u7d50\u679c\u3092\u8fd4\u3057\u3066\u3044\u308b\u3053\u3068\u306b\u3054\u6ce8\u610f\u304f\u3060\u3055\u3044\u3002<\/p>\n

FOR UPDATE\u4ee5\u5916\u306f\u5168\u304f\u540c\u3058SQL\u6587\u306a\u306e\u306b\u3001\u7d50\u679c\u304c\u9055\u3046\u3068\u3044\u3046\u306e\u306f\u3001\u5c11\u3057\u9055\u548c\u611f\u304c\u3042\u308a\u307e\u3059\u306d\u3002<\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n\n
\n
A\u3055\u3093<\/font><\/div>\n<\/td>\n
\n
B\u3055\u3093<\/font><\/div>\n<\/td>\n<\/tr>\n
test=# begin;
\n BEGIN<\/td>\n		\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u958b\u59cb<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n
test=# select user_type from user_tbl where user_id = 1000;
\n user_type
\n ———–
\n 1
\n (1 row)<\/td>\n		\u53c2\u7167<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\ntest=# begin;
\n BEGIN<\/td>\n		\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u958b\u59cb<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\ntest=# select user_type from user_tbl where user_id = 1000 for update;
\n user_type
\n ———–
\n 1
\n (1 row)<\/td>\n		\u884c\u30ed\u30c3\u30af<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\ntest=# update user_tbl set user_type = 0 where user_id = 1000;
\n UPDATE 1<\/td>\n		\u66f4\u65b0<\/font><\/td>\n<\/tr>\n
<\/font><\/td>\n<\/font><\/td>\ntest=# commit;
\n COMMIT<\/td>\n		\u30b3\u30df\u30c3\u30c8<\/font><\/td>\n<\/tr>\n
test=# select user_type from user_tbl where user_id = 1000;
\n user_type
\n ———–
\n 0
\n (1 row)<\/td>\n		\u53c2\u7167\uff08\u30ed\u30c3\u30af\u306a\u3057\uff09<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n
test=# select user_type from user_tbl where user_id = 1000 for update;
\n user_type
\n ———–
\n 0
\n (1 row)<\/td>\n		\u53c2\u7167\uff08\u30ed\u30c3\u30af\u3042\u308a\uff09<\/font><\/td>\n<\/font><\/td>\n<\/font><\/td>\n<\/tr>\n<\/table>\n

PostgreSQL\u306e\u5834\u5408\u306f\u300cFOR UPDATE\u300d\u306e\u6709\u308b\u7121\u3057\u3067SELECT\u306e\u7d50\u679c\u306f\u5909\u308f\u308a\u307e\u305b\u3093\u3002
\n\u3053\u3061\u3089\u306e\u65b9\u304c\u76f4\u611f\u7684\u306a\u611f\u3058\u304c\u3057\u307e\u3059\u306d\u3002<\/p>\n

 <\/p>\n

\uff08MySQL\u306e\u5185\u90e8\u69cb\u9020\u306b\u306f\u8a73\u3057\u304f\u306a\u3044\u306e\u3067\u304d\u3063\u3068\u3067\u3059\u304c\uff09MySQL\u3067\u306f\u6700\u521d\u306eSELECT\u3067\u30b9\u30ca\u30c3\u30d7\u30b7\u30e7\u30c3\u30c8\u304c\u5c55\u958b\u3055\u308c\u308b\u306e\u3067\u3001FOR UPDATE\u3067\u53d6\u5f97\u3057\u305f\u5834\u5408\u3068\u305d\u3046\u3058\u3083\u306a\u3044\u5834\u5408\u3067SELECT\u306e\u7d50\u679c\u304c\u9055\u3046\u306e\u3060\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u300c\u3042\u308b\u30c6\u30fc\u30d6\u30eb\u306e\u5024\u3092\u5143\u306b\u3001\u4ed6\u30c6\u30fc\u30d6\u30eb\u306e\u5024\u3092\u66f4\u65b0\u3059\u308b\u300d\u307f\u305f\u3044\u306a\u30d7\u30ed\u30b0\u30e9\u30e0\u306e\u5834\u5408\u3001\u4e0a\u8a18\u306e\u4f8b\u306e\u3088\u3046\u306a\u30d1\u30bf\u30fc\u30f3\u306b\u306f\u307e\u308b\u3053\u3068\u304c\u3042\u308a\u305d\u3046\u3067\u3059\u3002<\/p>\n

\u305d\u306e\u3088\u3046\u306a\u53c2\u7167\u306e\u524d\u306b\u306f\u3057\u3063\u304b\u308a\u30ed\u30c3\u30af\u3092\u304b\u3051\u307e\u3057\u3087\u3046\u3001\u3068\u3044\u3046\u305d\u3093\u306a\u304a\u8a71\u3067\u3057\u305f\u3002<\/p>\n

 <\/p>\n

\u95a2\u9023\uff1a<\/em><\/strong><\/p>\n