ãã©ã³ã¶ã¯ã·ã§ã³ã¯æ éã«ãã©ãããããããã§ããä»ãã©ã³ã¶ã¯ã·ã§ã³ãå®è£ ãã¦ãã¾ãã
ã¿ã¤ãã«ã¯é£ãã§ããMySQL ã®ããã©ã«ãã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã§ãã REPEATABLE READ
ã§ã®è©±ã§ããå¾ã確ããã訳ã§ã¯ãªãã§ãã MySQL ã«éã£ã話ã§ã¯ãªãã§ãã
ãã¦ããã©ã³ã¶ã¯ã·ã§ã³ã®ç¨èªã« Strictness (ST) 㨠Rigorous (RG) ã¨ãããã®ãããã¾ãã詳ããã¯ãã®è¨äºãèªãã¨ãã³ãã«ãªãããããã¾ããã
トランザクションの Strictness と Rigorousness の定義を再確認する - ぱと隊長日誌
ããã2ã¤ã®éãã¯ãããã©ã³ã¶ã¯ã·ã§ã³ãããã¬ã³ã¼ãã Read ããæã«ãStrictness ã¯å¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§ã ãã®ã¬ã³ã¼ãã« Write ã§ãã ã®ã«å¯¾ãã¦ãRigorous 㯠Read ãããã©ã³ã¶ã¯ã·ã§ã³ã Commit ã Abort ããã¾ã§ Write ããããã¯ããã ãã®éãã§ããRigorous ã®æ¹ã Strictness ãããå³å¯ã§ãã
ã¤ã¾ã 2PL ã®æèã§ããã°ãå é¨çã«åå¾ãã Read Lock ã Commit/Abort åã«è§£æ¾ãããã©ããã®éãã«ãªãã¾ãã
ä»åã¯ãããå®éã® MySQL ã§è©¦ãã¦ç¢ºèªããå®éã®ã¢ããªã±ã¼ã·ã§ã³ã§ã®ã¦ã¼ã¹ã±ã¼ã¹ã§æ°ãã¤ããã¹ãã¨ãããç´¹ä»ãã¾ãã
å®é¨ããã¦ã¿ã
ä»å試ããã®ã¯ãMySQL 8.0.15 ã§ããDocker ã§åããã¦ãã¾ãã
ãã¼ãã«ã¯ä»¥ä¸ã®æ§é ã§ã1ã¤ã ãã¬ã³ã¼ãã追å ãã¦ãã¾ãã
CREATE TABLE hoge ( id INT NOT NULL, value INT NOT NULL default 0, PRIMARY KEY (id) ); INSERT INTO hoge (id, value) VALUES (1, 1);
2 ã¤ã®ã³ãã¯ã·ã§ã³ãä½æãããããã§ãã©ã³ã¶ã¯ã·ã§ã³ãä½ã£ã¦ id = 1
ã®ã¬ã³ã¼ãã® value
ãã«ã¦ã³ãã¢ããããå®é¨ããã¦ããã¾ãã
æä½ã¯å ¨ã¦ mysql ã¯ã©ã¤ã¢ã³ãã³ãã³ãã§è¡ã£ã¦ãã¾ãã
æ®éã«å¤ãæ´æ°ã㦠Rigorous ã§ãªããã¨ã確ããã
2 ã¤ã®ã³ãã¯ã·ã§ã³ãããããã以ä¸ã® SQL ãåæã«ï¼è¡ãã¤å®è¡ãã¾ãã
BEGIN; SELECT * FROM hoge WHERE id = 1; UPDATE hoge SET value = 2 WHERE id = 1; SELECT * FROM hoge WHERE id = 1; COMMIT;
çæ¹ã§ BEGIN
ããå¾ã«ããçæ¹ã§ BEGIN
ãã㦠... ã¨è¡ã£ãå
·åã§ãã
ã¾ãã両æ¹ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§ id = 1
ã® value
㯠1 ã§ããã¨ãããã¾ãã
mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
ããã§ä¸¡æ¹ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§ãã®å¤ã« 1
ãå ãã 2
ãæ´æ°ãã¾ãã
mysql> UPDATE hoge SET value = 2 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 2 | +----+-------+ 1 row in set (0.00 sec)
çæ¹ã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯ãã®ããã«æ´æ°ã«æåãã¾ããå°ãªãã¨ã SELECT
ãããã©ã³ã¶ã¯ã·ã§ã³ãããä¸ã§ UPDATE
ãæåãã¦ãããããRigorous ã§ã¯ãªã ã¨ãããã¨ããããã¾ãã
æ´æ°ã®ããããã³ã°ã¨å¥å¦ãªæå
ãã¦ãçæ¹ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§ããããããã¯ããã¬ã¹ãã³ã¹ãè¿ã£ã¦ãã¾ããã
ããã¦ãæ´æ°ã«æåãããã©ã³ã¶ã¯ã·ã§ã³ã COMMIT
ããã¨ãããã¯ããããã©ã³ã¶ã¯ã·ã§ã³ãåãå§ãã¾ãã
mysql> UPDATE hoge SET value = 2 WHERE id = 1; Query OK, 0 rows affected (7.08 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
ããããå¤ã®æ´æ°ã«å¤±æ ãã¾ããChanged: 0
ã¨ãªã£ã¦ãããã¨ãããæ´æ°ã«å¤±æãã¦ãããã¨ããããã¾ãã
ããã§å¥ã®å¤ï¼ä¾ãã° 3
ã¨ãï¼ã«æ´æ°ããã¨æ´æ°ã«æåãã¾ãã
ãªãåãå¤ã ã¨æ´æ°ã«å¤±æãã¦éãå¤ã ã¨æ´æ°ã«æåããã®ãããããããã¾ãã ã両æ¹æåãã¦ããã¯ãã§ãã
ããã¯ããã¾ã§ãåã®æ³åã§ãããåãå¤ã«æ´æ°ããã¨ãããã¨ã¯åã®å¤ã«å¯¾ãã¦åãç¸å¯¾çãªæä½ï¼ãã®å ´åã¯ã«ã¦ã³ãã¢ããï¼ãè¡ã£ã¦ããå¯è½æ§ãé«ãã§ãããã®ãããæå³ãã¬ç«¶åç¶æ ã«ãããã¼ã¿ã®ä¸æ´åãçºçãã¦ããå¯è½æ§ãé«ãããã®ãã°ã«å©ç¨è ãæ°ä»ããããããããã«ããã¦æ´æ°ã失æããã¦ããã®ã§ã¯ãªããã¨æãã¾ããã
ããã«ã¦ã³ãã¢ãããªã©ã®ç¸å¯¾çãªæä½ã§ã¯ãªãã両æ¹ã¨ããã®å¤ã«ããã¨ãã絶対çãªæä½ã§ãã£ãå ´åã§ãæ´æ°ã«å¤±æãã¦ãæçµçãªå¤ã¯ãã®å¤ã«ãªã£ã¦ããããããã¼ã¿ãå£ããã¨ãããã¨ã¯ãªããã¡ãªããã¯å°ããã§ãã
ããããå©ç¨è ã®ãã¹ã«åªãã MySQL ã®è¦ªåè¨è¨ãªã®ã§ã¯ãªããã¨æ¨å¯ãã¾ããã
éã«ãçæ¹ã§ã¯ +1
ãã¦çæ¹ã§ã¯ +2
ããã¿ãããªã¦ã¼ã¹ã±ã¼ã¹ã§ã¯æ´æ°ã«å¤±æããªããã注æãå¿
è¦ã§ãã
æ£ããã«ã¦ã³ãã¢ãããæåããã
SELECT
ããå¤ã«æä½ãã㦠UPDATE
ãã¦ãæ´æ°ã«å¤±æãããã¨ããããã¾ãããã§ã¯ã©ãããã°æ£ããå¤ã®ã«ã¦ã³ãã¢ãããã§ããã®ã§ããããï¼
æ¹æ³ã¯2ã¤ããã¾ãã
- ããã¯ãã¨ã
UPDATE
æã工夫ãã
ï¼ã¤ã¯ SELECT
ããã¨ãã« ããã¯ãã¨ã æ¹æ³ã§ããMySQL ã§ã¯ãSELECT
æã®æ«å°¾ã« FOR UPDATE
ãã¤ãããã¨ã§ããã¯ãåå¾ã§ãã¾ããããã¯ãåå¾ãããã©ã³ã¶ã¯ã·ã§ã³ã Commit/Abort ããã¾ã§å¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ããã¯ã¯ãããã¯ãããããã¯ãç²å¾ã§ããæã«ã¯æ´æ°ãããå¤ãåå¾ãããã¨ãã§ãã¾ãã
SELECT * FROM hoge WHERE id = 1 FOR UPDATE;
ãã1ã¤ã¯ UPDATE
æã工夫ãã ã¨ãããã®ã§ããå¤ã®ç¸å¯¾çãªæä½ãã«ã¦ã³ãã¢ãããªã©åç´ãªå ´åã¯ãvalue = value + 1
ã¨ããã¨ç¸å¯¾çãªæä½ãããã¦æ´æ°ããã¾ãã
UPDATE hoge SET value = value + 1 WHERE id = 1;
å¥ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§æ´æ°ãããå¤ã«å¯¾ãã¦å ç®ãè¡ããããããMySQL ã® REPEATABLE READ ã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã§ãã£ã¦ãã以ä¸ã®ããã« 1
㨠SELECT
ãããå¤ã« 1
ã足ããã 3
ã«ãªã£ãã¿ãããªä¸è¦ç´æã«åããçµæã«ãªãã¾ãã
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec) mysql> UPDATE hoge SET value = value + 1 WHERE id = 1; Query OK, 1 row affected (1.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 3 | +----+-------+ 1 row in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
SERIALIZABLE ã§ã¯ã©ããªãã
MySQL 㯠Rigorous ã§ã¯ãªãã¨å¤§è¦å¾ãåã£ã¦ãã¾ãã¾ããããMySQL ã§ããã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã SERIALIZABLE
ã«ãã㨠Rigorous ã«ãªãã¾ãã
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2 ã¤ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§ SELECT
ããä¸ã§ UPDATE
ããã¨ãããã¯ããã¦å¦çãè¿ã£ã¦ãã¾ããã
ãã®ãã¨ã«ä¸¡æ¹ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§ UPDATE
ããã¨ãããããã¯ãã¦ãã¾ãã¾ãã
mysql> UPDATE hoge SET value = 14 WHERE id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
SERIALIZABLE
ã¯å³å¯ã«ã¯ãªãã¾ããé度ãé
ããªã£ã¦ãã¾ããã大éã®å¦çãæãã«ã¯åãã¦ããªãã§ãã
ã¾ã¨ã
MySQL ã® Rigorous ã®æåã調ã¹ãããã«è²ã è±ç·ãã¾ãããã¾ã¨ããã¨ä»¥ä¸ã®ããã«ãªãã¾ãã
- MySQL ã®ããã©ã«ãã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã§ãã
REPEATABLE READ
ã§ã¯ Rigorous ã§ã¯ãªã- ãã ãåãå¤ã«å¯¾ãã¦åãæä½ãããã¨æãããå ´åã¯è¦ªåã«ã MySQL ã¯æ´æ°ã失æããã
- ç¸å¯¾çãªå¤ã®æä½ãããå ´åã¯
SELECT ~~ FOR UPDATE
ãã¦ããã¯ãã¨ãããUPDATE ~~ value = value = 1
㨠SQL å ã«ç¸å¯¾çãªæä½ãè¨è¿°ããã SERIALIZABLE
㯠Rigorous ã«ãªããé ããã容æã«ãããããã¯ãã
ã¢ããªã±ã¼ã·ã§ã³ã®ã¦ã¼ã¹ã±ã¼ã¹ã¨ãã¦ã¯ããã¤ã³ãã®å ç®ãªã©å¤ã®ç¸å¯¾çãªæä½ããããã¨ã¯ããããã¨æãã¾ãã
ãã®éã¯å¿ ãããã¯ãåãã SQL å ã«ç¸å¯¾çãªæä½ãè¨è¿°ãããã¨ã§ã¨ã©ã¼ã®ãªãæ´åæ§ã®åãããã¼ã¿æ´æ°ãã§ããããã«ãªãã¾ãã