ç®å½
- 1.èæ¯
- 2.å®éªåæ
- 3.幻读åºæ¯1
- 4.幻读åºæ¯2
- 5.æ»ç»
- 6.åå åæ
1.èæ¯
çè¿ä¸äºå客åæç« ï¼æç说 MySQL Innodb RR 级å«ä¸ï¼è§£å³äºå¹»è¯»é®é¢ï¼ä¹æç说没æ解å³ï¼ä¹è¿æä¸äºè¯´è§£å³äºé¨å幻读é®é¢ï¼ä½æ¯é½æ²¡ææ³è¿å®éªå»è®ºè¯ï¼æ®ç严谨çæï¼ç¹æ¤å®éªä¸çªè®°å½ä¸ä¸
2.å®éªåæ
1.使ç¨
SELECT @@tx_isolation
æ¥è¯¢å½åçäºå¡é离级å«ï¼ç¡®ä¿æ¯
REPEATABLE-READ
2.æ§è¡ä»¥ä¸è¡¨ç»æè¯å¥å建表
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`pwd` varchar(20) NOT NULL,
`is_deleted` tinyint(4) NOT NULL,
`gmt_created` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `t_user_username_uindex` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
3.è¿ééè¦äºè§£å¿«ç §è¯»åå½å读两个æ¦å¿µã
3.幻读åºæ¯1
åºå· | äºå¡A | äºå¡B | ç°è±¡ | å½å读/å¿«ç §è¯» |
---|---|---|---|---|
1 | å¼å§äºå¡ï¼start transactionï¼ | å¼å§äºå¡ï¼start transactionï¼ | æ å¼å¸¸ | |
2 | æ¥è¯¢è¡¨æææ°æ®ï¼select * from t_userï¼ | äºå¡Aæ§è¡ç»æ为空 | å¿«ç §è¯» | |
3 | æå ¥ä¸æ¡æ°æ®ï¼insert into t_user(id, username, pwd, is_deleted, gmt_created, gmt_modified) values(1ï¼ âtestâ, âtestâ, 0, now(), now())ï¼ | äºå¡Bæ§è¡æå | å½å读 | |
4 | æ交äºå¡ï¼commitï¼ | äºå¡Bäºå¡æ交æå | ||
5 | å次æ¥è¯¢è¡¨æææ°æ®ï¼select * from t_userï¼ | äºå¡Aæ§è¡ç»æå第2æ¥çä¸è´ | å¿«ç §è¯» | |
6 | å次æ¥è¯¢è¡¨æææ°æ®ï¼select * from t_user for updateï¼ | äºå¡Aæ§è¡ç»æå第2æ¥çä¸ä¸è´ | å½å读 | |
7 | æ交äºå¡ï¼commitï¼ |
è¿ç§åºæ¯å¨å®é å·¥ä½ä¸æå°åºç°ï¼å¯ä»¥æ¥ççåºæ¯2
4.幻读åºæ¯2
åºå· | äºå¡A | äºå¡B | ç°è±¡ | å½å读/å¿«ç §è¯» |
---|---|---|---|---|
å¨ å段建ç«å¯ä¸ç´¢å¼ | ||||
1 | å¼å§äºå¡ï¼start transactionï¼ | å¼å§äºå¡ï¼start transactionï¼ | æ å¼å¸¸ | |
2 | æ¥è¯¢è¡¨æææ°æ®ï¼select * from t_user where username = âaaaâï¼ | äºå¡Aæ§è¡ç»æ为空 | å¿«ç §è¯» | |
3 | æå ¥ä¸æ¡æ°æ®ï¼insert into t_user(id, username, pwd, is_deleted, gmt_created, gmt_modified) values(2ï¼ âaaaâ, âaaaâ, 0, now(), now())ï¼ | äºå¡Bæ§è¡æå | å½å读 | |
4 | æ交äºå¡ï¼commitï¼ | äºå¡Bäºå¡æ交æå | ||
5 | æå ¥ä¸æ¡æ°æ®ï¼insert into t_user(id, username, pwd, is_deleted, gmt_created, gmt_modified) values(3ï¼ âaaaâ, âaaaâ, 0, now(), now())ï¼ | äºå¡Aè¯å¥æ§è¡æ¥éï¼è¯´username = âaaaâ å·²ç»åå¨ | å½å读 |
5.æ»ç»
MySQL ç Innodb å¨ REPEATABLE-READ çäºå¡é离级å«ä¸ï¼ä¼åç幻读ç°è±¡ã
6.åå åæ
å¨ä¸ä¸ªäºå¡ä¸ï¼å ä»å¿«ç §è¯»ä¸è¯»åæ°æ®ï¼åä»å½å读读åæ°æ®ï¼ä¼åºç°æ°æ®ä¸ä¸è´çæ åµï¼å æ¤åºç°å¹»è¯»ç°è±¡ãå½ä¸ä¸ªäºå¡ä¸ï¼å ¨ç¨é½æ¯å¿«ç §è¯»ï¼åä¸ä¼åºç°å¹»è¯»ç°è±¡ã