***幻读ï¼phantom readï¼ ********ï¼æ¯æå¨ä¸ä¸ªäºå¡ä¸åå两次ç¸åçæ¥è¯¢äº§çä¸åçç»æéï¼åä¸æ¬¡æ¥è¯¢çå°äºåä¸æ¬¡æ¥è¯¢æ²¡æçå°çè®°å½è¡ã
MySQL InnoDBé»è®¤çäºå¡é离级å«æ¯å¯éå¤è¯»ï¼å¯éå¤è¯»çè¦æ¨å¨äºåä¸æ°æ®è¡è®°å½å¨ä¸ä¸ªäºå¡å æ 论ä½æ¶æ¥è¯¢ç»æé½æ¯ä¸æ ·çã
ä»å®ä¹å¯ä»¥ç¥éï¼å¯éå¤è¯»è§£å³çé®é¢å幻读é®é¢æå®è´¨æ§çåºå«ï¼ä¸ä¸ªé对åä¸è¡è®°å½ï¼ä¸ä¸ªè¯´çæ¯æ°æ®è¡æ°ï¼é£ä¹ï¼MySQLåæ¯æä¹è§£å³å¹»è¯»é®é¢çå¢ï¼ä»å¤©å°±æ¥ä¸æ¢ç©¶ç«ï¼å ä¸ä¸ä¸ªç®å½ï¼
ä¸ãMySQLå¦ä½è§£å³å¹»è¯»
1.1 å¿«ç §è¯»åå½å读
1.2 å¿«ç §è¯»å¦ä½è§£å³å¹»è¯»
1.3 å½å读å¦ä½è§£å³å¹»è¯»
äºãå¯éå¤è¯»å®å ¨è§£å³å¹»è¯»äºä¹ï¼
2.1 é²ä¸ºäººç¥ç幻读
ä¸ãç»è¯
ä¸ãMySQLå¦ä½è§£å³å¹»è¯»
é¦å ï¼æ们çåææ¯å¨MySQLæ°æ®åºå ï¼ä½¿ç¨çå¼ææ¯InnoDBå¼æï¼ä¸äºå¡çé离级å«æ¯å¯éå¤è¯»ã
åé¢æç« æ讲è¿ï¼MySQL InnoDBä¾é MVCCå®ç°äºå¡é离级å«ãMVCCå称å¤çæ¬å¹¶åæ§å¶ï¼å®çå ¨ç§°æ¯Multi-Version Concurrency Controlï¼ç´ç½è¯´å°±æ¯å¨åä¸æ¶å»åä¸æ¡è®°å½å¨ç³»ç»ä¸å¯ä»¥åå¨å¤ä¸ªçæ¬ã
å¦æä¸è®°å¾MVCCï¼å¯ä»¥ç¹å»çè¿éï¼ç¾å¢é¢è¯å®ï¼å¯éå¤è¯»é离级å«å®ç°åçæ¯ä»ä¹ï¼ï¼ä¸æææMVCCæºå¶ï¼
1.1 å¿«ç §è¯»åå½å读
å½åè¯»ï¼ MySQLçMVCCå³å®äºåä¸æ°æ®è¡å¯è½ä¼åæ¶åå¨å¤ä¸ªçæ¬çæ åµï¼å½å读表示读åçè®°å½æ¯ææ°çæ¬çï¼ä¸è¯»åçæ¶åï¼å¦ææå ¶ä»å¹¶åäºå¡è¦ä¿®æ¹åä¸æ°æ®è¡ï¼å½åäºå¡ä¼éè¿å éè®©å ¶ä»äºå¡é»å¡çå¾ ã
æ¯å¦select lock in share mode(å ±äº«é)ãselect for update ãupdateãinsert ãdelete(æä»é)çæä½é½æ¯ä¸ç§å½å读ï¼è¿äºæä½ä¼å¯¹è¯»åçè®°å½è¿è¡å éã
å¿«ç §è¯»ï¼ è¡¨ç¤ºä¸å éçéé»å¡è¯»ï¼åæ®éçselectæä½å°±æ¯å¿«ç §è¯»ãå¿«ç §è¯»çå®ç°åºäºMVCCï¼å®å®ç°äºäºå¡å ä»»ä½æ¶å»è¯»åçæ°æ®é½æ¯åå²æ个çæ¬çæ°æ®ï¼ä¸ä¸å®æ¯å½åæ¶å»ææ°çæ°æ®ã
MVCCè¿ç§å®ç°æ¹å¼ä¹æ¯ä¸ç§éçåç§ï¼ä½å®é¿å¼äºå éæä½ï¼å¤§å¤§éä½ç³»ç»çå¼éï¼ä»èæé«ç³»ç»çæ§è½ã
éè¦ç¹å«æ³¨æçæ¯ï¼å¿«ç §è¯»å¨MySQLç串è¡é离级å«ä¸ä¼ä¸å为å½å读ï¼å³ä½¿æ¯selectæä½ä¹ä¼å éã
1.2 å¿«ç §è¯»å¦ä½è§£å³å¹»è¯»
åå¦æ们æä¸å¼ è´¦æ·ä½é¢è¡¨bank_balanceï¼å ¶ç»æå¦ä¸ï¼éé¢çåå§æ°æ®è¡æ9è¡ã
CREATE TABLE bank_balance (
id int NOT NULL AUTO_INCREMENT,
user_name varchar(45) NOT NULL COMMENT 'ç¨æ·å',
balance int NOT NULL DEFAULT '0' COMMENT 'ä½é¢ï¼åä½ï¼äººæ°å¸åï¼æ¯å¦100表示人æ°å¸1å ï¼é»è®¤æ¯0',
wealth tinyint NOT NULL DEFAULT '0' COMMENT 'å¯æç¨åº¦ï¼0ï¼è´«ç©·ï¼1ï¼å¯æ',
PRIMARY KEY (id),
UNIQUE KEY idx_bank_balance_user_name (user_name)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
åå§æ°æ®è¡ï¼
mysql> select *from bank_balance; +----+-----------+-----------+--------+ | id | user_name | balance | wealth | +----+-----------+-----------+--------+ | 1 | å°å | 0 | 0 | | 2 | å°å | 300000000 | 0 | | 3 | Tom | 500 | 0 | | 4 | Eric | 100 | 0 | | 5 | AI | 0 | 0 | | 6 | Alex | 100 | 0 | | 7 | Max | 100 | 0 | | 8 | Mike | 100 | 0 | | 9 | Lyn | 200 | 0 | +----+-----------+-----------+--------+ 9 rows in set (0.01 sec)
å设ç°å¨æ两个äºå¡ï¼äºå¡Aåäºå¡Bï¼åæ¶æä½è¿å¼ ä½é¢è¡¨ï¼ä¸¤ä¸ªäºå¡çæä½æ¶é´çº¿å¦ä¸ï¼
äºå¡Aæ两次æ¥è¯¢ï¼åå«å¨â¢åâ¤ï¼é½æ¯éç¨ç¸åçSQLè¯å¥ï¼select * from bank_balance where balance > 0ï¼æ®éselectæ¯ä¸ç§å¿«ç §è¯»ï¼ï¼ç®çé½æ¯æ¥è¯¢ææbalance > 0çrowsã
- â åâ¡ï¼å¼å¯äºå¡ã
- â¢ï¼äºå¡Aéè¿select * from where balance > 0å¾å°çç»ææ¯7 Rowsï¼å¦ä¸ï¼
â
- â£ï¼äºå¡Bæå ¥ä¸è¡è®°å½ (10, 'Loop', 100,0)ã
- â¤ï¼äºå¡Aéè¿select * from bank_balance where balance>0å次æ¥è¯¢å¾å°çç»æï¼åæ ·è¿æ¯7 Rowsã
- â¥åâ¦ï¼æ交äºå¡ã
为ä»ä¹ç¬¬â¤å¤æ¥è¯¢æ¶ç»æè¿æ¯7 Rowså¢ï¼å¤§å®¶åºè¯¥é½è¿è®°å¾MVCCï¼äºå¡Aå¨ç¬¬â¢å¤å°±ä¼çæä¸ä¸ªReadViewè®°å½å½åçæ´»è·äºå¡ï¼äºå¡Bå°±å¨æ´»è·äºå¡èå´å ï¼å¨ç¬¬â¤å¤äºå¡B insertçè®°å½éèåäºå¡idä¸æ»¡è¶³äºå¡A读åï¼äºå¡Aä¼é¡ºçundo logççæ¬é¾æ¥å°æ»¡è¶³çè®°å½ä¸ºæ¢ï¼å½ç¶ï¼è¯¥è®°å½æ¯äºå¡Bæ°å¢çï¼é¡ºççæ¬é¾æ¾æç»åªè½æ¾å°nullï¼æ以该记å½ä¸è¿åï¼ã
1.3 å½å读å¦ä½è§£å³å¹»è¯»
åæ ·æ¯ä¸é¢ç表åæ¥è¯¢æ¶é´çº¿ï¼åªæ¯æ¥è¯¢è¯å¥æ¢æäºå½å读çæ¥è¯¢select * from bank_balance where balance > 0 for updateï¼å设没æéï¼é£ä¹å°±ä¼åç幻读ç°è±¡ï¼å¦ä¸ï¼
â
- â åâ¡ï¼å¼å¯äºå¡ã
- â¢ï¼äºå¡Aéè¿select * from bank_balance where balance>0 for updateå¾å°çç»ææ¯7 Rowsï¼å¦ä¸ï¼
â
- â£ï¼äºå¡Bæå ¥ä¸è¡è®°å½ (10, 'Loop', 100,0)ã
- â¤ï¼äºå¡Aéè¿select * from bank_balance where balance > 0 for updateå次æ¥è¯¢å¾å°çç»ææ¯8 Rowsï¼å¦ä¸ï¼
â
- â¥åâ¦ï¼æ交äºå¡ã
第â¢å第â¤åæ ·æ¯æ¥è¯¢bank_balance > 0 çè®°å½ä½å¾å°çç»æå´ä¸ä¸æ ·ï¼è¿å°±æ¯å¹»è¯»ç°è±¡ã
为äºè§£å³å¹»è¯»é®é¢ï¼MySQL InnoDB å¼æå¼å ¥äºnext-key lockï¼å ¶çåäºé´éé+è®°å½éçç»åã
è®°å½éï¼é¡¾åæä¹ï¼å°±æ¯ç»æ°æ®è¡å çéï¼é£ä½ä¸ºé´ééï¼
å设ï¼bank_balance表ä¸åªåå¨ä½é¢balance>0ä¸ä¸»é®id 为4å6çè®°å½ï¼é£ä¹å½ä¸ä¸ªäºå¡ä½¿ç¨select * from where balance>0 for updateæ¥è¯¢æ¶ï¼å ¶ä»äºå¡å°±æ æ³æå ¥ id = 5çè®°å½ï¼å°±åæ¯äºå¡Aæ(4,6)è¿ä¸ªèå´éä½äºï¼è¿å°±æ¯é´ééã
å¦æåæid=4å6çè®°å½ä¹åæ¶ä¸èµ·éäºï¼åèµ·æ¥åæä¸ä¸ªéåºé´[4, 6]ï¼é£ä¹æ´ä¸ªåºé´éä¹å«next-key lockã
è¿æ¯ä»¥ä¸çä¾åï¼äºå¡Bå¨äºå¡Aæ¥è¯¢åè¿è¡insertæä½ï¼
â
äºå¡ A å¨â¢å¤æ§è¡äºselect * from bank_balance where balance > 0 for updateè¿æ¡éå®è¯»è¯å¥åï¼å°±ä¼ææ´ä¸ªè¡¨ææè®°å½éä¸(å 为balanceå段æ ç´¢å¼)ï¼å¹¶æ ¹æ®ä¸»é®idå表记å½å½¢æå¤ä¸ªnext-key lockï¼åå«æ¯ï¼(-â, 1]ã(1, 2]ã(2, 3]ã(3, 4]ã(4, 5]ã(5, 6]ã(6, 7]ã(7, 8]ã(8, 9]ã(9, +â]ï¼æ¯ä¸ªnext-key locké½æ¯åå¼åéåºé´ã
ç¶åï¼äºå¡ B å¨â£å¤æ§è¡æå ¥è¯å¥ï¼åç°id=10被äºå¡ A å äº next-key lockï¼äºæ¯äºç© B ä¼çæä¸ä¸ªåéï¼å¼å§é»å¡çå¾ ï¼ç´å°äºå¡ A æ交äºäºå¡æä¼æ§è¡ãè¿å°±é¿å äºä¸è¿°æ说ç幻读é®é¢ã
以ä¸çä¾åæ¯è¾ç¹æ®ï¼å¦ææ们ç表ä¸åªæ两æ¡è®°å½ï¼åå«æ¯(4, 'Eric', 100,0)ã(10, 'Loop', 100,0)ï¼é£ä¹å½æ们æ§è¡select *from bank_balance where id > 8 for updateæ¶ï¼å°±åªä¼å½¢æ两个next-key lockï¼å®å°±æ¯(4, 10]ï¼(10, +â]ï¼å¦ææ们æ§è¡insert into bank_balance values(5,'MALL',100,0)å°ä¼è¢«é»å¡ï¼ä½æ¯æ们æ§è¡insert into bank_balance values(2,'MALL',100,0)å°±ä¸ä¼è¢«é»å¡ï¼å 为id=2没æ被éä½ã
ç¹å«è¯´æä¸ä¸ï¼next-key lockåºäºè®°å½å½¢æï¼ä¸æ¯åºäºæ¥è¯¢æ¡ä»¶å½¢æï¼æäºåå¦é®å°ä¸æçä¾åä¸ä¸¤ä¸ªnext-key lock为ä»ä¹ä¸æ¯(8, 10]ã(10, +â]ï¼å°±æ¯è¿ä¸ªåå ã
äºãå¯éå¤è¯»å®å ¨è§£å³å¹»è¯»äºä¹
2.1 é²ä¸ºäººç¥ç幻读
MySQL InnoDBé»è®¤çå¯éå¤è¯»é离级å«å ä¸next-key lockä¸å®ç¨åº¦ä¸è§£å³äºå¹»è¯»é®é¢ï¼ä½ä¾ç¶åå¨ç¹æ®çæ åµä¸äº§ç幻读é®é¢ã
*第ä¸ç§æ åµï¼ *å å¯å¨çäºå¡A使ç¨å¿«ç §è¯»ï¼åå¯å¨çäºå¡Bæå ¥æ°çæ°æ®è¡å¹¶æ交ï¼ç¶åäºå¡Aåæ´æ°ï¼å ¶åAçæ¥è¯¢é½è½æ¥äºå¡Bæ°å¢çæ°æ®è¡ã
â
â¢ï¼è¡¨ä¸æ²¡æid=5çè®°å½è¡ï¼æ以äºå¡Aæ¥è¯¢çç»ææ¯0Rowsã
â£-â¥ï¼äºå¡Bå¯å¨ï¼å¹¶æå ¥ä¸æ¡id=5çè®°å½ï¼åæ交äºå¡ã
â¦ï¼äºå¡Aæ´æ°id=5çè®°å½ã
â§ï¼äºå¡Aæ¥è¯¢id=5çè®°å½ï¼ç»æRows=1ï¼äº§çäºå¹»è¯»ã
æMVCCçåçï¼ç¬¬â§å¤äºå¡Aæ¥è¯¢ç»æä¸åºè¯¥è¿åid=5çè®°å½ï¼ä½å 为æupdateå¨å ï¼æ以该记å½èæ¥è¯¢äºåºæ¥ãï¼æ¤å¤å¾ç»ï¼éè¦è®¤ççä¸çè¿ä¸ªæç« æè½ç解ï¼ç¾å¢é¢è¯å®ï¼å¯éå¤è¯»é离级å«å®ç°åçæ¯ä»ä¹ï¼ï¼ä¸æææMVCCæºå¶ï¼ï¼
å¿«ç §è¯»ä¸ä¼å éï¼å¯¼è´äºå¡Bå¯ä»¥insertæåï¼èupdateè¯å¥åæ¯å½å读ï¼è½å¤æ´æ°id=5çæ°æ®ï¼æ以ï¼å½æ§è¡â§æ¶ï¼å¿«ç §è¯»ä¹å°±è½å¤æ¥è¯¢åºæ¥id=5çè®°å½äºã
*第äºç§æ åµï¼ *å¦æäºå¡ä¸å¼å§æ²¡æ使ç¨å½å读ï¼å½å ¶ä»äºå¡æå ¥æ°æ®å¹¶æ交åå使ç¨å½å读就ä¼åç幻读ç°è±¡ã
â
â¢ï¼è¡¨ä¸æ²¡æid=5çè®°å½è¡ï¼æ以äºå¡Aéç¨å¿«ç §è¯»æ¹å¼æ¥è¯¢çç»ææ¯0Rowsã
â£-â¥ï¼äºå¡Bå¯å¨ï¼å¹¶æå ¥ä¸æ¡id=5çè®°å½ï¼åæ交äºå¡ã
â¦ï¼äºå¡Aéç¨å½å读çæ¹å¼æ¥è¯¢id=5çè¡ï¼ç»æRows为1ï¼äº§çäºå¹»è¯»ã
è¿ç§æ åµæ¯å ä¸ºå¿«ç §è¯»ä¸çænext-key lock导è´ï¼å ¶ä»äºå¡å¯ä»¥æå ¥æ¬äºå¡æ¥è¯¢èå´å çè®°å½è¡ï¼æ以ï¼å½å ¶ä»äºå¡æå ¥æ°æ®ååæ§è¡å½å读ï¼å°±è½æ¥å°æ°çè®°å½ï¼ä»è产ç幻读é®é¢ã
ä¸è¬å¨å¼åè¿ç¨ä¸å»ºè®®å¼å¯ä¸ä¸ªäºå¡æ¶å°½å¿«éç¨for updateçæ¥è¯¢æ¹å¼ï¼ä»¥çænext-key lockï¼é¿å 幻读é®é¢ã