ããã¯ããªã«ãããããŠæžãããã®ïŒ
ã¢ããªã±ãŒã·ã§ã³ãäœæããŠãããšãããããæä»ã®æ¹æ³ã®ã²ãšã€ãšããŠæ¥œèŠ³çããã¯ããã䜿ããšæããŸãã
ããšãã°ããããå®çŸ©ã®ããŒãã«ããã£ããšããŸãã
create table mytable( id varchar(3), message varchar(100), version int, primary key(id) );
ããããããŒã¿ãå ¥ã£ãŠãããšããŸãã
MySQL localhost:3306 ssl practice SQL > insert into mytable(id, message, version) values('id1', 'Hello', 1); Query OK, 1 row affected (0.1130 sec) MySQL localhost:3306 ssl practice SQL > select * from mytable; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0005 sec)
ãããããmessage
ã«ã©ã ãæŽæ°ãããšããŸãã
ããããã¢ããªã±ãŒã·ã§ã³ã®æ¥œèŠ³çããã¯ã®å®è£
ã ãšãããããSQLãå®è¡ãããšæããŸãã
â»messageã«ã©ã ãæå³çã«æŒç®ã«ããŠããŸã
update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
ãã®æãåæã«å®è¡ããŠããå¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ããæŽæ°ãããããšããŸãã
update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
ãã®æããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãšããŠãªã«ãæå®ããŠããã®ãã§ãã©ã®ãããªããšãèµ·ããã®ãã確èªããŠã¿ãããšããã®ãä»åã®ãé¡ã§ãã
ç¹ã«REPEATABLE READã®æãã©ããªããæ°ã«ãªããã§ãããã
確èªã®ã¡ã€ã³ã¯MySQLãšããŸãããæ¯èŒã®ããã«PostgreSQLã§ãè©ŠããŠããããšæããŸãã
MySQLãšPostgreSQLã§äœ¿ãããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«
æåã«ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã«ã€ããŠç¢ºèªããŠãããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã« | 説æ |
---|---|
READ UNCOMMITTED | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®æªã³ãããã®ããŒã¿ãèªã¿åã |
READ COMMITTED | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããæžã¿ã®ããŒã¿ãèªã¿åã |
REPEATABLE READ | ãã©ã³ã¶ã¯ã·ã§ã³ãéå§ããããŸã§ã«ã³ããããããããŒã¿ã®ã¿ãåç §ããããä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ãè¿œå ãŸãã¯åé€ããããŒã¿ãèªã¿åã |
SERIALIZABLE | ãã©ã³ã¶ã¯ã·ã§ã³ãé 次å®è¡ãããã®ãããªæ¯ãèããããããåæå®è¡æ§ãæãäœã |
MySQLã§èšå®ã§ãããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã¯ä»¥äžã§ãã
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
ããã©ã«ãã¯REPEATABLE READã§ãã
MySQL :: MySQL 8.4 Reference Manual :: 17.7.2.1 Transaction Isolation Levels
SET TRANSACTION
ã䜿ãããšã§ããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãå€æŽã§ããŸãã
MySQL :: MySQL 8.4 Reference Manual :: 15.3.7 SET TRANSACTION Statement
PostgreSQLã§èšå®ã§ãããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã¯ä»¥äžã§ãã
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
ãã ããREAD UNCOMMITTED
ã¯READ COMMITTED
ã®ããã«åäœãããšãããŠããŸãããšããããããã¥ã¡ã³ãã«ã¯å
éšçã«3ã€ã®
åé¢ã¬ãã«ããå®è£
ãããŠããªãããšæžãããŠããŸãããããã©ã«ãã¯READ COMMITTEDã§ãã
PostgreSQL: Documentation: 16: 13.2. Transaction Isolation
PostgreSQLãSET TRANSACTION
ã䜿ãããšã§ããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãå€æŽã§ããŸãã
PostgreSQL: Documentation: 16: SET TRANSACTION
BEGIN TRANSACTION
ã«ISOLATION LEVEL
ãä»äžããããšã§ãå¯èœãªããã§ãã
ãšããã§ããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãå€æŽãããšã©ã®ãããªããšãçºçããã®ã§ããããïŒ
A Critique of ANSI SQL Isolation Levels
ANSI SQL-92ã§ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã«ãããŠçºçãããäºè±¡ãã以äžã®ããã«å®çŸ©ããŠããŸãã
å称 | çºçããçŸè±¡ |
---|---|
P1ïŒ Dirty Read | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®æªã³ãããã®ããŒã¿ãèªã¿åã£ãŠããŸã |
P2ïŒ Non-Reatable Read | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã³ãããããããŒã¿ãè¡ãåé€ãããããšãèªã¿åã£ãŠããŸãïŒæåã«èªã¿åã£ãçµæããå€ãã£ãŠããŸãïŒ |
P3ïŒ Phantom ïŒReadïŒ | çµæãè€æ°è¡ã«ãªãã¯ãšãªãŒãå®è¡ããéã«ãå¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®å€æŽïŒè¿œå è¡ïŒãæ€åºããŠããŸãïŒæåã«èªã¿åã£ãçµæã®éåããå€ãã£ãŠããŸãïŒ |
ISO/IEC 9075:1992, Database Language SQLïŒANSI SQL-92 Standard)
ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãšçºçããçŸè±¡ã®çµã¿åããã¯ã以äžã®ããã«ãªã£ãŠããŸãã
ãã®ANSI SQL-92ã§å®çŸ©ãããåé¢ã¬ãã«ã§ã¯ç¹åŸŽãæŽçããããŠããªããšããŠãæŽçãçŽãããšããè«æã§ãã
[cs/0701157] A Critique of ANSI SQL Isolation Levels
PDFã¯ãã¡ãã
A Critique of ANSI SQL Isolation Levels
ãã®è«æã§ãŸãšããããŠããäºè±¡ã¯ãã¡ãã
å称 | çºçããçŸè±¡ |
---|---|
P0ïŒ Dirty Write | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®æªã³ãããã®ããŒã¿ãäžæžãããŠããŸã |
P1ïŒ Dirty Read | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®æªã³ãããã®ããŒã¿ãèªã¿åã£ãŠããŸã |
P4CïŒ Cursor Lost Update | ã«ãŒãœã«ã§èªã¿èŸŒãã§ããããŒã¿ïŒLocking ReadïŒã«å¯ŸããŠãä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ãå€æŽããããŒã¿ãäžæžãããŠããŸã |
P4ïŒ Lost Update | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã³ãããããããŒã¿ãäžæžãããŠããŸã |
P2ïŒ Fuzzy Read | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ãå€æŽããïŒã³ãããããïŒããŒã¿ãè¡ãåé€ãããããšãèªã¿åã£ãŠããŸãïŒæåã«èªã¿åã£ãçµæããå€ãã£ãŠããŸãïŒ |
P3ïŒ Phantom | çµæãè€æ°è¡ã«ãªãã¯ãšãªãŒãå®è¡ããéã«ãå¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®å€æŽïŒè¿œå è¡ïŒãæ€åºããŠããŸãïŒæåã«èªã¿åã£ãçµæã®éåããå€ãã£ãŠããŸãïŒ |
A5AïŒ Read Skew | ä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®æªã³ãããã®ããŒã¿ãèªã¿åã£ãŠããŸãïŒP2ãšã®éãã¯ãè€æ°ã®ããŒã¿ãèªã¿åãæã«æåããä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããæžã¿ã®ããŒã¿ãèŠãŠããŸããäžè²«æ§ããªããªã£ãŠããŸãããšïŒ |
A5BïŒ Write Skew | ãããã©ã³ã¶ã¯ã·ã§ã³T1ãxã®å€ãèªã¿åã£ãŠyã®å€ãå€æŽããå¥ã®ãã©ã³ã¶ã¯ã·ã§ã³T2ãyã®å€ãèªã¿åã£ãŠxã®å€ãå€æŽããæãäžè²«æ§ã®ãªãå€æŽã«ãªã£ãŠããŸãããšïŒããšãã°xãŸãã¯yã®å€ããã®éã«ã³ããŒããåŠçãèããæïŒåæå€ãx=10ãy=50ïŒã«ãT1 readïŒx=10ïŒãT2 readïŒy=50ïŒãT1 writeïŒy=10ïŒãT2 writeïŒx=50ïŒãçµæx=50ãy=10ãšãªããæ¬æ¥xãšyã¯çãããªãåŠçã®ã¯ããªã®ã«äžè²«æ§ããªããªãïŒ |
å®çŸ©ãšããŠã¯ãããªã£ãŠããã¿ããã§ãã
P0: w1[x]...w2[x]...((c1 or a1) and (c2 or a2) in any order) P1: w1[x]...r2[x]...((c1 or a1) and (c2 or a2) in any order) P4C: rc1[x]...w2[x]...w1[x]...c1 (Lost Update) P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update) P2: r1[x]...w2[x]...((c1 or a1) and (c2 or a2) in any order) P3: r1[P]...w2[y in P]...((c1 or a1) and (c2 or a2) any order) A5A: r1[x]...w2[x]...w2[y]...c2...r1[y]...(c1 or a1) (Read Skew) A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur) (Write Skew)
è¡šèšã®æå³ã¯ãããªã£ãŠããã¿ããã§ãã
- w1[x] ⊠ãã©ã³ã¶ã¯ã·ã§ã³1ãããŒã¿xã«æžã蟌ãã
- r2[x] ⊠ãã©ã³ã¶ã¯ã·ã§ã³2ãããŒã¿xãèªã¿åã£ã
- r1[P]ãw1[P] ⊠ãã©ã³ã¶ã¯ã·ã§ã³1ã«ãããè¿°èªPãæºããç¯å²ãžã®èªã¿åããæžã蟌ã¿
- c1 ⊠ãã©ã³ã¶ã¯ã·ã§ã³1ã®ã³ããã
- a1 ⊠ãã©ã³ã¶ã¯ã·ã§ã³2ã®ã¢ããŒãïŒããŒã«ããã¯ïŒ
- rc1[x] ⊠ãã©ã³ã¶ã¯ã·ã§ã³1ã§ã«ãŒãœã«ã®çŸåšäœçœ®ã®ã¬ã³ãŒããèªã¿åã£ã
- wc1[x] ⊠ãã©ã³ã¶ã¯ã·ã§ã³1ã§ã«ãŒãœã«ã®çŸåšäœçœ®ã®ã¬ã³ãŒãã«æžã蟌ãã
ã¡ãªã¿ã«ãANSI SQL-92ã§ã¯P1ãP2ãP3ã¯A1ãA2ãA3ãšããŠä»¥äžã®å®çŸ©ã«ãªã£ãŠããŸãã
A1: w1[x]...r2[x]...(a1 and c2 in either order) (Dirty Read) A2: r1[x]...w2[x]...c2...r1[x]...c1 (Fuzzy or Non-Repeatable Read) A3: r1[P]...w2[y in P]...c2....r1[P]...c1 (Phantom)
ããèŠããšA2ãšA3ã¯c1ãc2ïŒã³ãããïŒãã€ããŠããŸããããã®è«æã®P2ãP3ã§ã¯ã³ããããå€ããŠããã®ã§ç¯å²ãåºããªã£ãŠããããã§ãã
ãã®è«æã§ã¯ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãšçºçããçŸè±¡ã®çµã¿åããã¯ã以äžã®ããã«ãªã£ãŠããŸãã
â»ANSI SQL-92ããCursor StabilityãšSnapshot Isolationãšããåé¢ã¬ãã«ãè¿œå ãããŠããŸã
ã§ãå®éã®ãšãããããã®åé¡ãšMySQLãPostgreSQLã®åãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã®çµã¿åããã§ã©ã®ãããªåäœã«ãªãã®ãã¯ã
以äžã®ããŒãžãåèã«ã
MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する
å®ã¯ãA Critique of ANSI SQL Isolation Levelsãã®è«æãèŠãŠãã段éã§ããšããšã®è¶£æšããã¯ã¡ãã£ãšè±ç·æ°å³ã«ãªã£ãŠããŠã ãã®ãããã¯ååã«èªã¿è§£ããŠããªãã®ã§ãŸãå¥ã®æ©äŒã«èªã¿èŸŒãããšæããŸãâŠã
åè
ãã®ä»ããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãçºçããåé¡ïŒã¢ãããªãŒïŒã®è©±ã
トランザクション分離レベルの古典的論文 A Critique of ANSI SQL Isolation Levels を読む - Hatena Developer Blog
Isolation Levelの階層 #ポエム - Qiita
トランザクションの最先端研究 | 分離レベルの追跡・究明―TiDBの分離レベルを理解する(上) #初心者 - Qiita
ç°å¢
ä»åã®ç°å¢ã¯ãã¡ãã
MySQLã
$ mysqlsh myuser@localhost:3306/mydatabase --sql MySQL localhost:3306 ssl mydatabase SQL > select version(); +-----------+ | version() | +-----------+ | 8.4.0 | +-----------+ 1 row in set (0.0010 sec)
PostgreSQLã
$ psql -h localhost -U myuser mydatabase ãŠãŒã¶ãŒ myuser ã®ãã¹ã¯ãŒã: psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1)) SSLæ¥ç¶(ãããã³ã«: TLSv1.3ãæå·åæ¹åŒ: TLS_AES_256_GCM_SHA384ãå§çž®: ãªã) "help"ã§ãã«ãã衚瀺ããŸãã mydatabase=> select version(); version ----------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit (1 è¡) mydatabase=>
ããã©ã«ãã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã確èªãã
æåã«ãããããã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã確èªããŠãããŸãããã
MySQLã
MySQL localhost:3306 ssl mydatabase SQL > show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.0057 sec)
PostgreSQLã
mydatabase=> show default_transaction_isolation; default_transaction_isolation ------------------------------- read committed (1 è¡)
ã§ã¯ã確èªããŠãããŸãããã
MySQL
æåã¯MySQLã§ã
READ UNCOMMITTED
ãŸãã¯READ UNCOMMITTEDããã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0007 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.0008 sec)
MySQL Shellã§ã¯ãã©ã³ã¶ã¯ã·ã§ã³å ã§ã¯ããã³ããã«ãâ ããšãã衚瀺ãä»ããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0009 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.0008 sec)
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0016 sec)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0014 sec)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; Query OK, 1 row affected (0.0015 sec) Rows matched: 1 Changed: 1 Warnings: 0
ããŒã¿æžã蟌ã¿ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ãã®ãŸãŸæŽæ°ã§ããã«åŸ ã£ãŠããŸããŸãã
ã³ãããïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0972 sec)
ã³ããããããšãTX-Bã®æ¹ãé²ã¿ãŸããæŽæ°ã§ããŠããŸãããã
MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; Query OK, 0 rows affected (30.0974 sec) Rows matched: 0 Changed: 0 Warnings: 0
ããã¯ãä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããæžã¿ã®ããŒã¿ãèŠããŠããŸãã®ã§ïŒREAD UNCOMMITTEDãªã®ã§ã³ãããããªããŠã
èŠããŠããŸãã¯ãã§ããïŒãæ€çŽ¢æ¡ä»¶ã«äžèŽããªããªãæŽæ°ã§ããªããªã£ããšããããšããšã
確èªïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase SQL > select * from mytable where id = 'id1'; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0020 sec)
ã³ãããïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0011 sec)
æçµçãªçµæã¯ããã¡ãã§ããã
MySQL localhost:3306 ssl mydatabase SQL > select * from mytable where id = 'id1'; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0010 sec)
READ COMMITTED
READ COMMITTEDã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0011 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level read committed; Query OK, 0 rows affected (0.0011 sec)
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0006 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level read committed; Query OK, 0 rows affected (0.0007 sec)
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0019 sec)
ããŒã¿èªã¿èŸŒã¿ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0009 sec)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; Query OK, 1 row affected (0.0023 sec) Rows matched: 1 Changed: 1 Warnings: 0
ããŒã¿æžã蟌ã¿ïŒTX-BïŒããã¡ãã¯ããã®ãŸãŸåŸ ã¡ã«ãªããŸãã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0173 sec)
ãããšãTX-Bãé²ã¿ãŸãã空æ¯ãã«ãªããŸããã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; Query OK, 0 rows affected (27.2145 sec) Rows matched: 0 Changed: 0 Warnings: 0
ãã¡ããä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããæžã¿ã®ããŒã¿ãèŠããŠããŸãããã§ããã
ã³ãããïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0009 sec)
æçµçµæã¯ãã¡ãã§ããã
MySQL localhost:3306 ssl mydatabase SQL > select * from mytable where id = 'id1'; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0014 sec)
REPEATABLE READ
REPEATABLE READã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0009 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.0010 sec)
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0007 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.0007 sec)
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0014 sec)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0019 sec)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; Query OK, 1 row affected (0.0021 sec) Rows matched: 1 Changed: 1 Warnings: 0
ããŒã¿æžã蟌ã¿ïŒTX-BïŒããã®ãŸãŸåŸ ã¡ã«ãªããŸãã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0188 sec)
ãããšãTX-Bãé²ã¿ãŸãã空æ¯ãã«ãªããŸãããâŠïŒ
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; Query OK, 0 rows affected (17.8448 sec) Rows matched: 0 Changed: 0 Warnings: 0
REPEATABLE READã ãšãä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããããããŒã¿ã¯èŠããªãã¯ãã§ãããã©ããªã£ãŠãããã§ãããã
ã³ãããïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0015 sec)
æçµçµæã
MySQL localhost:3306 ssl mydatabase SQL > select * from mytable where id = 'id1'; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0013 sec)
ã¡ãã£ãšæ°ã«ãªãã®ã§ããã1床確èªããŠã¿ãŸãã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0007 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.0009 sec)
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0009 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.0008 sec)
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0014 sec)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0015 sec)
ããŒã¿æŽæ°ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; Query OK, 1 row affected (0.0022 sec) Rows matched: 1 Changed: 1 Warnings: 0
ãã®æãTX-BããããŒã¿ãèŠãŠã¿ãŸãã
--TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0019 sec)
TX-Aã®å€æŽããããŒã¿ã¯èŠããŠããŸããã
TX-Aãã³ãããããŠã¿ãŸãã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0147 sec)
ãã1床TX-Bã§èªã¿èŸŒã¿ã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0023 sec)
TX-Aã®ã³ãããåã®ããŒã¿ãèŠããŠããŸãã
ããŒã¿æžã蟌ã¿ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; Query OK, 0 rows affected (0.0021 sec) Rows matched: 0 Changed: 0 Warnings: 0
空æ¯ããŸããã
ã©ããªã£ãŠãããã§ãããïŒïŒïŒ
ããã§ãselectæã«for update
ãä»ããŠã¿ãŸãã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1' for update; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0015 sec)
ãªããšãä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããæžã¿ã®ããŒã¿ãèŠããŠããŸããŸããã
versionã«ã©ã ãå«ããŠã¿ãŸãããã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1' and version = 1 for update; Empty set (0.0012 sec) MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1' and version = 2 for update; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0014 sec)
ãããšåãæåã«ãªã£ãã®ã§ãTX-Bã®updateæã¯ç©ºæ¯ãã«ãªã£ããšèããã®ãããããã§ãã
ã³ãããïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0011 sec)
æçµçµæã
MySQL localhost:3306 ssl mydatabase SQL > select * from mytable where id = 'id1'; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0011 sec)
ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã®ããŠã³ã°ã¬ãŒã
ãã¡ããèŠããšãããã¯ä»ãã®èªã¿åãïŒLocking ReadïŒã§ã¯ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã®ããŠã³ã°ã¬ãŒãïŒREAD COMMITTEDçžåœã«
ãªã£ãŠããŸãïŒãè¡ããããããªã®ã§ãããä»åã®åãã¯ãã¡ããåçŸããããšã«ãªãããã§ããã
MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する
ã¡ãã£ãšé©ããŸããâŠã
REPEATABLE READ WITH CONSISTENT SNAPSHOT
MySQLã§ã¯ããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãREPEATABLE READ WITH ã®æã«éãWITH CONSISTENT SNAPSHOTãã€ããããšã§
Snapshot Isolationãšåçã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã«ããããšãã§ããããã§ãã
MySQL localhost:3306 ssl mydatabase SQL > start transaction with consistent snapshot; Query OK, 0 rows affected (0.0014 sec)
MySQL :: MySQL 8.4 Reference Manual :: 15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements
ã¡ãã£ãšè©Šããã¿ãã®ã§ãããä»åã®å 容ã ãšREPEATABLE READãšåãã«å·®ããªãã£ãã®ã§çµæã¯å²æããŸãã
SERIALIZABLE
SERIALIABLEã
ãã©ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0010 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level serializable; Query OK, 0 rows affected (0.0010 sec)
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase SQL > start transaction; Query OK, 0 rows affected (0.0011 sec) MySQL localhost:3306 ssl mydatabase â SQL > set session transaction isolation level serializable; Query OK, 0 rows affected (0.0003 sec)
ããŒã¿èªã¿èŸŒã¿ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0013 sec)
ããŒã¿èªã¿èŸŒã¿ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1'; +-----+---------+---------+ | id | message | version | +-----+---------+---------+ | id1 | Hello | 1 | +-----+---------+---------+ 1 row in set (0.0016 sec)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; Query OK, 1 row affected (0.0023 sec) Rows matched: 1 Changed: 1 Warnings: 0
ããŒã¿æžã蟌ã¿ïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0550 sec)
TX-Bã¯é²ã¿ã空æ¯ãã«ãªããŸãã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; Query OK, 0 rows affected (24.9590 sec) Rows matched: 0 Changed: 0 Warnings: 0
ã³ãããïŒTX-BïŒã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > commit; Query OK, 0 rows affected (0.0014 sec)
æçµçµæã
MySQL localhost:3306 ssl mydatabase SQL > select * from mytable where id = 'id1'; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0019 sec)
REPEATABLE READãšåãåäœã«ãªããŸãããã
ã¡ãªã¿ã«ãSERIALIZABLEã§ãã£ãŠãããã¯ä»ãã®èªã¿èŸŒã¿ã ãšä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ãããæžã¿ã®ããŒã¿ãèªã¿åãããšãã§ããŸãâŠã
-- TX-B MySQL localhost:3306 ssl mydatabase â SQL > select * from mytable where id = 'id1' for update; +-----+-----------------+---------+ | id | message | version | +-----+-----------------+---------+ | id1 | Hello from TX-A | 2 | +-----+-----------------+---------+ 1 row in set (0.0012 sec)
PostgreSQL
æåŸã¯PostgreSQLã§ã
READ UNCOMMITTED
READ UNCOMMITTEDãããã¥ã¡ã³ããèŠãéãREAD COMMITTEDãšåãåäœã®ããã§ãããäžå¿ãã£ãŠãããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level read uncommitted; SET
PostgreSQLã®å Žåããã©ã³ã¶ã¯ã·ã§ã³å ã§ã¯ããã³ããã«ã*ããšãã衚瀺ãä»ããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level read uncommitted; SET
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; UPDATE 1
ããŒã¿æžã蟌ã¿ïŒTX-BïŒãããã§ã¯åŸ ã¡ã«ãªããŸãã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A mydatabase=*> commit; COMMIT
ãã®æãTX-Bã¯ç©ºæ¯ãããŸãã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; UPDATE 0
ã³ãããïŒTX-BïŒã
-- TX-B mydatabase=*> commit; COMMIT
æçµçµæã
mydatabase=> select * from mytable where id = 'id1'; id | message | version -----+-----------------+--------- id1 | Hello from TX-A | 2 (1 è¡)
READ COMMITTED
READ COMMITTEDã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level read committed; SET
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level read committed; SET
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; UPDATE 1
ããŒã¿æžã蟌ã¿ïŒTX-BïŒã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A mydatabase=*> commit; COMMIT
TX-Bã¯åãåºãã空æ¯ãããŸãã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; UPDATE 0
ã³ãããïŒTX-BïŒã
-- TX-B mydatabase=*> commit; COMMIT
æçµçµæã
mydatabase=> select * from mytable where id = 'id1'; id | message | version -----+-----------------+--------- id1 | Hello from TX-A | 2 (1 è¡)
ãããŸã§ã¯ãMySQLãšåãã§ããã
REPEATABLE READ
REPEATABLE READã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level repeatable read; SET
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level repeatable read; SET
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; UPDATE 1
ããŒã¿æžã蟌ã¿ïŒTX-BïŒã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A mydatabase=*> commit; COMMIT
ãããšãTX-BåŽã¯ãšã©ãŒã«ãªããŸãã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ERROR: could not serialize access due to concurrent update
PostgreSQLã ãšããããæåã«ãªã£ãŠççŸãé²ãããšãããã§ãããããæå³ãããŒã¿ããŒã¹åŽã§æ¥œèŠ³çæä»å¶åŸ¡ã®ãããªããšã
ãã£ãŠãããå°è±¡ãæã¡ãŸããã
ãã®ç¶æ ã«ãªããšãTX-BåŽã¯ããŒã«ããã¯ãããããããŸããã
-- TX-B mydatabase=!> select * from mytable where id = 'id1'; ERROR: current transaction is aborted, commands ignored until end of transaction block
ã¡ãªã¿ã«ãã³ãããããŠãããŒã«ããã¯ãããã§ããã
-- TX-B mydatabase=!> commit; ROLLBACK
æçµçµæã
mydatabase=> select * from mytable where id = 'id1'; id | message | version -----+-----------------+--------- id1 | Hello from TX-A | 2 (1 è¡)
ã¡ãªã¿ã«ãMySQLãšéã£ãŠããã¯ä»ãã§èªã¿åããããŠãä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã³ãããããããŒã¿ãèŠãããšããããšã¯ãããŸããã
-- TX-B mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+-----------------+--------- id1 | Hello from TX-A | 2 (1 è¡) mydatabase=*> select * from mytable where id = 'id1' for update; id | message | version -----+-----------------+--------- id1 | Hello from TX-A | 2 (1 è¡)
SERIALIZABLE
SERIALIZABLEã
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-AïŒã
-- TX-A mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level serializable; SET
ãã©ã³ã¶ã¯ã·ã§ã³éå§ïŒTX-BïŒã
-- TX-B mydatabase=> start transaction; START TRANSACTION mydatabase=*> set transaction isolation level serializable; SET
ããŒã¿èªã¿åãïŒTX-AïŒã
-- TX-A mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿èªã¿åãïŒTX-BïŒã
-- TX-B mydatabase=*> select * from mytable where id = 'id1'; id | message | version -----+---------+--------- id1 | Hello | 1 (1 è¡)
ããŒã¿æžã蟌ã¿ïŒTX-AïŒã
-- TX-A mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1; UPDATE 1
ããŒã¿æžã蟌ã¿ïŒTX-BïŒã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ïŒåŸ æ©ïŒ
ã³ãããïŒTX-AïŒã
-- TX-A mydatabase=*> commit; COMMIT
TX-BåŽã¯å€±æããŸãã
-- TX-B mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1; ERROR: could not serialize access due to concurrent update
ããŒã«ããã¯ïŒTX-BïŒã
-- TX-B mydatabase=!> rollback; ROLLBACK
æçµçµæã
mydatabase=> select * from mytable where id = 'id1'; id | message | version -----+-----------------+--------- id1 | Hello from TX-A | 2 (1 è¡)
ãããã«
ãããã楜芳çããã¯ãããŒãã«ãMySQLãšPostgreSQLã§åãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã§ã©ã®ãããªåäœã«ãªããã確èªããŠã¿ãŸããã
MySQLã®REPEATABLE READã§ã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã®ããŠã³ã°ã¬ãŒãããªããªãè¡æçã§ããã®åé¢ã¬ãã«ã§æåŸ
ãã
èªã¿åãäžè²«æ§ãçªãç Žã£ãŠããŠããªãé©ããŸããã
äžæ¹ã§ãPostgreSQLã¯æŽæ°ãæ¢ããŠããŸããã ãªããšã
ããããRDBMSã®ãã¡ãMySQLã®ã¿ããã©ã«ããREPEATABLE READã§ãããäžè²«æ§ã欲ãããšããã§ããã¯ä»ããèªã¿åããããããããš
ãããäžè²«æ§ãæãªãããšã«ãªããããªã®ã§æ°ãã€ããæ¹ããããã ãªãšæããŸããã
ãããå匷ã«ãªããŸãããâŠã
ã¢ãããªãŒã®è©±ããããªã®ã§ãããããŒã¿ããŒã¹ã«é¢ããç¥èãæµ ããªãšæã£ãã®ã§ããã¡ãã£ãšå匷ããããšæããŸãã