ç®ä»
MySQLçäºå¡é离级å«ä¸å ±æå个ï¼åå«æ¯è¯»æªæ交ã读已æ交ãå¯éå¤è¯»ä»¥åå¯ä¸²è¡åã
äºååå¤æ°æ®
mysql> create table city(
-> id int(10) auto_increment,
-> name varchar(30),
-> primary key (id)
-> )engine=innodb charset=utf8mb4;
insert into city(name) values('æ¦æ±å¸');
mysql> select * from city;
+----+-----------+
| id | name |
+----+-----------+
| 1 | æ¦æ±å¸ |
+----+-----------+
äºå¡å¹¶åå¯è½åºç°çæ åµ
è读ï¼Dirty Readï¼
ä¸ä¸ªäºå¡è¯»å°äºå¦ä¸ä¸ªæªæ交äºå¡ä¿®æ¹è¿çæ°æ®
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISM9AnYldnJwAzN9c3Pn5GcuQ0MlMWbidXNp5UeFR1T6lERPdXWU1EdZpXTxUEVNFTSE1EeVRUT4FEVNlXQq1EdrpWTycGRNVDOT10dFpWT3lkeMVTSq5ENBR1T2NmMiNnSywEd5ITW110MaZHetlVdO1GT3lERNl3YXJGc5kHT20ESjBjUIF2Lc12bj5SYphXa5VWen5WY35iclN3Ztl2Lc9CX6MHc0RHaiojIsJye.png)
ä¼è¯Bå¼å¯ä¸ä¸ªäºå¡ï¼æid=1çname为æ¦æ±å¸ä¿®æ¹æ温å·å¸ï¼æ¤æ¶å¦å¤ä¸ä¸ªä¼è¯Aä¹å¼å¯ä¸ä¸ªäºå¡ï¼è¯»åid=1çnameï¼æ¤æ¶çæ¥è¯¢ç»æ为温å·å¸ï¼ä¼è¯Bçäºå¡æååæ»äºåæä¿®æ¹çè®°å½ï¼è¿æ ·ä¼è¯A读å°çæ°æ®æ¯ä¸åå¨çï¼è¿ä¸ªç°è±¡å°±æ¯è读ãï¼è读åªå¨è¯»æªæ交é离级å«æä¼åºç°ï¼
ä¸å¯éå¤è¯»ï¼Non-Repeatable Readï¼
ä¸ä¸ªäºå¡åªè½è¯»å°å¦ä¸ä¸ªå·²ç»æ交çäºå¡ä¿®æ¹è¿çæ°æ®ï¼å¹¶ä¸å ¶ä»äºå¡æ¯å¯¹è¯¥æ°æ®è¿è¡ä¸æ¬¡ä¿®æ¹å¹¶æ交åï¼è¯¥äºå¡é½è½æ¥è¯¢å¾å°ææ°å¼ãï¼ä¸å¯éå¤è¯»å¨è¯»æªæ交å读已æ交é离级å«é½å¯è½ä¼åºç°ï¼
ä¼è¯Aå¼å¯ä¸ä¸ªäºå¡ï¼æ¥è¯¢id=1çç»æï¼æ¤æ¶æ¥è¯¢çç»æname为æ¦æ±å¸ãæ¥çä¼è¯Bæid=1çnameä¿®æ¹ä¸ºæ¸©å·å¸ï¼éå¼äºå¡ï¼å 为æ¤æ¶çautocommit为1ï¼æ¯æ¡SQLè¯å¥æ§è¡å®èªå¨æ交ï¼ï¼æ¤æ¶ä¼è¯Açäºå¡åä¸æ¬¡æ¥è¯¢id=1çç»æï¼è¯»åçç»æname为温å·å¸ãä¼è¯Båæ¤ä¿®æ¹id=1çname为æå·å¸ï¼ä¼è¯Açäºå¡å次æ¥è¯¢id=1ï¼ç»ænameçå¼ä¸ºæå·å¸ï¼è¿ç§ç°è±¡å°±æ¯ä¸å¯éå¤è¯»ã
幻读ï¼Phantomï¼
ä¸ä¸ªäºå¡å æ ¹æ®æäºæ¡ä»¶æ¥è¯¢åºä¸äºè®°å½ï¼ä¹åå¦ä¸ä¸ªäºå¡åå表ä¸æå ¥äºç¬¦åè¿äºæ¡ä»¶çè®°å½ï¼åå çäºå¡å次æç §è¯¥æ¡ä»¶æ¥è¯¢æ¶ï¼è½æå¦ä¸ä¸ªäºå¡æå ¥çè®°å½ä¹è¯»åºæ¥ãï¼å¹»è¯»å¨è¯»æªæ交ã读已æ交ãå¯éå¤è¯»é离级å«é½å¯è½ä¼åºç°ï¼
ä¼è¯Aå¼å¯ä¸ä¸ªäºå¡ï¼æ¥è¯¢id>0çè®°å½ï¼æ¤æ¶ä¼æ¥å°name=æ¦æ±å¸çè®°å½ãæ¥çä¼è¯Bæå ¥ä¸æ¡name=温å·å¸çæ°æ®ï¼éå¼äºå¡ï¼å 为æ¤æ¶çautocommit为1ï¼æ¯æ¡SQLè¯å¥æ§è¡å®èªå¨æ交ï¼ï¼è¿æ¶ä¼è¯Açäºå¡å以åæçæ¥è¯¢æ¡ä»¶ï¼id>0ï¼åä¸æ¬¡æ¥è¯¢ï¼æ¤æ¶ä¼åºç°ä¸¤æ¡è®°å½ï¼name为æ¦æ±å¸å温å·å¸çè®°å½ï¼ï¼è¿ç§ç°è±¡å°±æ¯å¹»è¯»ã
äºå¡çé离级å«
MySQLçäºå¡é离级å«ä¸å ±æå个ï¼åå«æ¯è¯»æªæ交ã读已æ交ãå¯éå¤è¯»ä»¥åå¯ä¸²è¡åã
MySQLçé离级å«çä½ç¨å°±æ¯è®©äºå¡ä¹é´äºç¸é离ï¼äºä¸å½±åï¼è¿æ ·å¯ä»¥ä¿è¯äºå¡çä¸è´æ§ã
é离级å«æ¯è¾ï¼å¯ä¸²è¡å>å¯éå¤è¯»>读已æ交>读æªæ交
é离级å«å¯¹æ§è½çå½±åæ¯è¾ï¼å¯ä¸²è¡å>å¯éå¤è¯»>读已æ交>读æªæ交
ç±æ¤çåºï¼é离级å«è¶é«ï¼æéè¦æ¶èçMySQLæ§è½è¶å¤§ï¼å¦äºå¡å¹¶å严éæ§ï¼ï¼ä¸ºäºå¹³è¡¡äºè ï¼ä¸è¬å»ºè®®è®¾ç½®çé离级å«ä¸ºå¯éå¤è¯»ï¼MySQLé»è®¤çé离级å«ä¹æ¯å¯éå¤è¯»ã
读æªæ交ï¼READ UNCOMMITTEDï¼
å¨è¯»æªæ交é离级å«ä¸ï¼äºå¡Aå¯ä»¥è¯»åå°äºå¡Bä¿®æ¹è¿ä½æªæ交çæ°æ®ã
å¯è½åçè读ãä¸å¯éå¤è¯»å幻读é®é¢ï¼ä¸è¬å¾å°ä½¿ç¨æ¤é离级å«ã
读已æ交ï¼READ COMMITTEDï¼
å¨è¯»å·²æ交é离级å«ä¸ï¼äºå¡Båªè½å¨äºå¡Aä¿®æ¹è¿å¹¶ä¸å·²æ交åæè½è¯»åå°äºå¡Bä¿®æ¹çæ°æ®ã
读已æ交é离级å«è§£å³äºè读çé®é¢ï¼ä½å¯è½åçä¸å¯éå¤è¯»å幻读é®é¢ï¼ä¸è¬å¾å°ä½¿ç¨æ¤é离级å«ã
å¯éå¤è¯»ï¼REPEATABLE READï¼
å¨å¯éå¤è¯»é离级å«ä¸ï¼äºå¡Båªè½å¨äºå¡Aä¿®æ¹è¿æ°æ®å¹¶æ交åï¼èªå·±ä¹æ交äºå¡åï¼æè½è¯»åå°äºå¡Bä¿®æ¹çæ°æ®ã
å¯éå¤è¯»é离级å«è§£å³äºè读åä¸å¯éå¤è¯»çé®é¢ï¼ä½å¯è½åç幻读é®é¢ã
æé®ï¼ä¸ºä»ä¹ä¸äºåéï¼åæä½ï¼ï¼å«çäºå¡è¿å¯ä»¥è¯»æä½ï¼
å 为InnoDBæMVCCæºå¶ï¼å¤çæ¬å¹¶åæ§å¶ï¼ï¼å¯ä»¥ä½¿ç¨å¿«ç §è¯»ï¼èä¸ä¼è¢«é»å¡ã
å¯ä¸²è¡åï¼SERIALIZABLEï¼
åç§é®é¢ï¼è读ãä¸å¯éå¤è¯»ã幻读ï¼é½ä¸ä¼åçï¼éè¿å éå®ç°ï¼è¯»éååéï¼ã
é离级å«çå®ç°åç
使ç¨MySQLçé»è®¤é离级å«ï¼å¯éå¤è¯»ï¼æ¥è¿è¡è¯´æã
æ¯æ¡è®°å½å¨æ´æ°çæ¶åé½ä¼åæ¶è®°å½ä¸æ¡åæ»æä½ï¼åæ»æä½æ¥å¿undo logï¼ãåä¸æ¡è®°å½å¨ç³»ç»ä¸å¯ä»¥åå¨å¤ä¸ªçæ¬ï¼è¿å°±æ¯æ°æ®åºçå¤çæ¬å¹¶åæ§å¶ï¼MVCCï¼ãå³éè¿åæ»ï¼rollbackæä½ï¼ï¼å¯ä»¥åå°åä¸ä¸ªç¶æçå¼ã
å设ä¸ä¸ªå¼ä» 1 被æ顺åºæ¹æäº 2ã3ã4ï¼å¨åæ»æ¥å¿éé¢å°±ä¼æ类似ä¸é¢çè®°å½ã
å½åå¼æ¯ 4ï¼ä½æ¯å¨æ¥è¯¢è¿æ¡è®°å½çæ¶åï¼ä¸åæ¶å»å¯å¨çäºå¡ä¼æä¸åç read-viewãå¦å¾ä¸çå°çï¼å¨è§å¾ AãBãC éé¢ï¼è¿ä¸ä¸ªè®°å½çå¼åå«æ¯ 1ã2ã4ï¼åä¸æ¡è®°å½å¨ç³»ç»ä¸å¯ä»¥åå¨å¤ä¸ªçæ¬ï¼å°±æ¯æ°æ®åºçå¤çæ¬å¹¶åæ§å¶ï¼MVCCï¼ãå¯¹äº read-view Aï¼è¦å¾å° 1ï¼å°±å¿ é¡»å°å½åå¼ä¾æ¬¡æ§è¡å¾ä¸ææçåæ»æä½å¾å°ã
åæ¶ä½ ä¼åç°ï¼å³ä½¿ç°å¨æå¦å¤ä¸ä¸ªäºå¡æ£å¨å° 4 æ¹æ 5ï¼è¿ä¸ªäºå¡è· read-view AãBãC 对åºçäºå¡æ¯ä¸ä¼å²çªçã
æé®ï¼åæ»æä½æ¥å¿ï¼undo logï¼ä»ä¹æ¶åå é¤ï¼
MySQLä¼å¤æå½æ²¡æäºå¡éè¦ç¨å°è¿äºåæ»æ¥å¿çæ¶åï¼åæ»æ¥å¿ä¼è¢«å é¤ã
æé®ï¼ä»ä¹æ¶åä¸éè¦äºï¼
å½ç³»ç»éä¹ææ¯è¿ä¸ªåæ»æ¥å¿æ´æ©çread-viewçæ¶åã
æ¥ç&设置äºå¡é离级å«
注ï¼ä¸åçæ¬å½ä»¤å¯è½ä¸åï¼æ¬æ以5.7.28çæ¬MySQL为ä¾ã
æ¥çé离级å«
- æ¥çå½åä¼è¯é离级å«
select @@tx_isolation;
- æ¥çç³»ç»å½åé离级å«
select @@global.tx_isolation;
设置é离级å«
æ¹å¼1ï¼éè¿setå½ä»¤
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
å ¶ä¸levelæ4ç§å¼ï¼
level: { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE }
å ³é®è¯ï¼GLOBAL
SET GLOBAL TRANSACTION ISOLATION LEVEL level;
- åªå¯¹æ§è¡å®è¯¥è¯å¥ä¹å产ççä¼è¯èµ·ä½ç¨
- å½åå·²ç»åå¨çä¼è¯æ æ
å ³é®è¯ï¼SESSION
SET SESSION TRANSACTION ISOLATION LEVEL level;
- 对å½åä¼è¯çææåç»çäºå¡ææ
- 该è¯å¥å¯ä»¥å¨å·²ç»å¼å¯çäºå¡ä¸é´æ§è¡ï¼ä½ä¸ä¼å½±åå½åæ£å¨æ§è¡çäºå¡
- å¦æå¨äºå¡ä¹é´æ§è¡ï¼å对åç»çäºå¡ææã
æ å ³é®è¯
SET TRANSACTION ISOLATION LEVEL level;
- åªå¯¹å½åä¼è¯ä¸ä¸ä¸ä¸ªå³å°å¼å¯çäºå¡ææ
- ä¸ä¸ä¸ªäºå¡æ§è¡å®åï¼åç»äºå¡å°æ¢å¤å°ä¹åçé离级å«
- 该è¯å¥ä¸è½å¨å·²ç»å¼å¯çäºå¡ä¸é´æ§è¡ï¼ä¼æ¥éç
æ¹å¼2ï¼éè¿æå¡å¯å¨é¡¹å½ä»¤
å¯ä»¥ä¿®æ¹å¯å¨åæ°transaction-isolationçå¼
æ¯æ¹è¯´æ们å¨å¯å¨æå¡å¨æ¶æå®äº--transaction-isolation=READ UNCOMMITTEDï¼é£ä¹äºå¡çé»è®¤é离级å«å°±ä»åæ¥çREPEATABLE READåæäºREAD UNCOMMITTEDã
ç»è¯
欢è¿å ³æ³¨å¾®ä¿¡å ¬ä¼å·ãç ä»zonEãï¼ä¸æ³¨äºå享Javaãäºè®¡ç®ç¸å ³å 容ï¼å æ¬SpringBootãSpringCloudãå¾®æå¡ãDockerãKubernetesãPythonçé¢åç¸å ³ææ¯å¹²è´§ï¼æå¾ ä¸æ¨ç¸éï¼