大家好ï¼ææ¯å°æã
æ¯ä¸æ¯å¾å¤äººé½å¯¹ MySQL å è¡çº§éçè§åæçè¿·è¿·ç³ç³ï¼ä¸ä¼æ¯ next-key éï¼ä¸ä¼æ¯é´ééï¼ä¸ä¼åæ¯è®°å½éã
å¦ç½è¯´ï¼ç¡®å®è¿æºå¤æçï¼ä½æ¯å¥½å¨ææ¾ç¹äºç¹è§å¾ï¼ä¹ç¥éå¦ä½å¦ä½ç¨å½ä»¤åæå äºä»ä¹ç±»åçè¡çº§éã
ä¹åæåè¿ä¸ç¯å ³äºãMySQL æ¯æä¹å è¡çº§éçï¼ãçæç« ï¼éçæå MySQL éç¸å ³çæç« è¶æ¥è¶å¤æ¶ï¼åæ¥åç°å½æ¶çæç« åçä¸å¤è¯¦ç»ã
为äºè®©å¤§å®¶å¾æ¸ æ¥çç¥é MySQL æ¯æä¹å è¡çº§éçï¼ä»¥åå¦ä½ç¨å½ä»¤åæå äºä»ä¹è¡çº§éï¼æ以æéåäºè¿ç¯æç« ã
æç« å 容æ¯è¾é¿ï¼å¤§å®¶å¯ä»¥èå¿çä¸å»ï¼ä¸å®ä¼ææ°çåç°ï¼
ä»ä¹ SQL è¯å¥ä¼å è¡çº§éï¼
InnoDB å¼ææ¯æ¯æè¡çº§éçï¼è MyISAM å¼æ并ä¸æ¯æè¡çº§éï¼æ以åé¢çå 容é½æ¯åºäº InnoDB å¼æ çã
æ®éç select è¯å¥æ¯ä¸ä¼å¯¹è®°å½å éçï¼å 为å®å±äºå¿«ç §è¯»ï¼æ¯éè¿ MVCCï¼å¤çæ¬å¹¶åæ§å¶ï¼å®ç°çã
å¦æè¦å¨æ¥è¯¢æ¶å¯¹è®°å½å è¡çº§éï¼å¯ä»¥ä½¿ç¨ä¸é¢è¿ä¸¤ä¸ªæ¹å¼ï¼è¿ä¸¤ç§æ¥è¯¢ä¼å éçè¯å¥ç§°ä¸ºéå®è¯»ã
sqlå¤å¶ä»£ç //对读åçè®°å½å å
±äº«é(Såé)
select ... lock in share mode;
//对读åçè®°å½å ç¬å é(Xåé)
select ... for update;
ä¸é¢è¿ä¸¤æ¡è¯å¥å¿ é¡»å¨ä¸ä¸ªäºå¡ä¸ï¼å 为å½äºå¡æ交äºï¼éå°±ä¼è¢«éæ¾ï¼æ以å¨ä½¿ç¨è¿ä¸¤æ¡è¯å¥çæ¶åï¼è¦å ä¸ begin æè start transaction å¼å¯äºå¡çè¯å¥ã
é¤äºä¸é¢è¿ä¸¤æ¡éå®è¯»è¯å¥ä¼å è¡çº§éä¹å¤ï¼update å delete æä½é½ä¼å è¡çº§éï¼ä¸éçç±»åé½æ¯ç¬å é(Xåé)ã
sqlå¤å¶ä»£ç //对æä½çè®°å½å ç¬å é(Xåé)
updaet table .... where id = 1;
//对æä½çè®°å½å ç¬å é(Xåé)
delete from table where id = 1;
å ±äº«éï¼Séï¼æ»¡è¶³è¯»è¯»å ±äº«ï¼è¯»åäºæ¥ãç¬å éï¼Xéï¼æ»¡è¶³ååäºæ¥ã读åäºæ¥ã
è¡çº§éæåªäºç§ç±»ï¼
ä¸åé离级å«ä¸ï¼è¡çº§éçç§ç±»æ¯ä¸åçã
å¨è¯»å·²æ交é离级å«ä¸ï¼è¡çº§éçç§ç±»åªæè®°å½éï¼ä¹å°±æ¯ä» ä» æä¸æ¡è®°å½éä¸ã
å¨å¯éå¤è¯»é离级å«ä¸ï¼è¡çº§éçç§ç±»é¤äºæè®°å½éï¼è¿æé´ééï¼ç®çæ¯ä¸ºäºé¿å 幻读ï¼ï¼æ以è¡çº§éçç§ç±»ä¸»è¦æä¸ç±»ï¼
- Record Lockï¼è®°å½éï¼ä¹å°±æ¯ä» ä» æä¸æ¡è®°å½éä¸ï¼
- Gap Lockï¼é´ééï¼éå®ä¸ä¸ªèå´ï¼ä½æ¯ä¸å å«è®°å½æ¬èº«ï¼
- Next-Key Lockï¼Record Lock + Gap Lock çç»åï¼éå®ä¸ä¸ªèå´ï¼å¹¶ä¸éå®è®°å½æ¬èº«ã
æ¥ä¸æ¥ï¼åå«ä»ç»è¿ä¸ç§è¡çº§éã
Record Lock
Record Lock 称为记å½éï¼éä½çæ¯ä¸æ¡è®°å½ãèä¸è®°å½éæ¯æ S éå X éä¹åçï¼
- å½ä¸ä¸ªäºå¡å¯¹ä¸æ¡è®°å½å äº S åè®°å½éåï¼å ¶ä»äºå¡ä¹å¯ä»¥ç»§ç»å¯¹è¯¥è®°å½å S åè®°å½éï¼S åä¸ S éå ¼å®¹ï¼ï¼ä½æ¯ä¸å¯ä»¥å¯¹è¯¥è®°å½å X åè®°å½éï¼S åä¸ X éä¸å ¼å®¹ï¼;
- å½ä¸ä¸ªäºå¡å¯¹ä¸æ¡è®°å½å äº X åè®°å½éåï¼å ¶ä»äºå¡æ¢ä¸å¯ä»¥å¯¹è¯¥è®°å½å S åè®°å½éï¼S åä¸ X éä¸å ¼å®¹ï¼ï¼ä¹ä¸å¯ä»¥å¯¹è¯¥è®°å½å X åè®°å½éï¼X åä¸ X éä¸å ¼å®¹ï¼ã
举个ä¾åï¼å½ä¸ä¸ªäºå¡æ§è¡äºä¸é¢è¿æ¡è¯å¥ï¼
sqlå¤å¶ä»£ç mysql > begin;
mysql > select * from t_test where id = 1 for update;
äºå¡ä¼å¯¹è¡¨ä¸ä¸»é® id = 1 çè¿æ¡è®°å½å ä¸ X åçè®°å½éï¼è¿æ ·å ¶ä»äºå¡å°±æ æ³å¯¹è¿æ¡è®°å½è¿è¡ä¿®æ¹åå é¤äºã
å½äºå¡æ§è¡ commit åï¼äºå¡è¿ç¨ä¸çæçéé½ä¼è¢«éæ¾ã
Gap Lock
Gap Lock 称为é´ééï¼åªåå¨äºå¯éå¤è¯»é离级å«ï¼ç®çæ¯ä¸ºäºè§£å³å¯éå¤è¯»é离级å«ä¸å¹»è¯»çç°è±¡ã
å设ï¼è¡¨ä¸æä¸ä¸ªèå´ id 为ï¼3ï¼5ï¼é´ééï¼é£ä¹å ¶ä»äºå¡å°±æ æ³æå ¥ id = 4 è¿æ¡è®°å½äºï¼è¿æ ·å°±ææçé²æ¢å¹»è¯»ç°è±¡çåçã
é´ééè½ç¶åå¨ X åé´ééå S åé´ééï¼ä½æ¯å¹¶æ²¡æä»ä¹åºå«ï¼é´ééä¹é´æ¯å ¼å®¹çï¼å³ä¸¤ä¸ªäºå¡å¯ä»¥åæ¶ææå å«å ±åé´éèå´çé´ééï¼å¹¶ä¸åå¨äºæ¥å ³ç³»ï¼å 为é´ééçç®çæ¯é²æ¢æå ¥å¹»å½±è®°å½èæåºçã
Next-Key Lock
Next-Key Lock 称为临é®éï¼æ¯ Record Lock + Gap Lock çç»åï¼éå®ä¸ä¸ªèå´ï¼å¹¶ä¸éå®è®°å½æ¬èº«ã
å设ï¼è¡¨ä¸æä¸ä¸ªèå´ id 为ï¼3ï¼5] ç next-key lockï¼é£ä¹å ¶ä»äºå¡å³ä¸è½æå ¥ id = 4 è®°å½ï¼ä¹ä¸è½ä¿®æ¹ id = 5 è¿æ¡è®°å½ã
æ以ï¼next-key lock å³è½ä¿æ¤è¯¥è®°å½ï¼åè½é»æ¢å ¶ä»äºå¡å°æ°è®°å½æå ¥å°è¢«ä¿æ¤è®°å½åé¢çé´éä¸ã
next-key lock æ¯å å«é´éé+è®°å½éçï¼å¦æä¸ä¸ªäºå¡è·åäº X åç next-key lockï¼é£ä¹å¦å¤ä¸ä¸ªäºå¡å¨è·åç¸åèå´ç X åç next-key lock æ¶ï¼æ¯ä¼è¢«é»å¡çã
æ¯å¦ï¼ä¸ä¸ªäºå¡ææäºèå´ä¸º (1, 10] ç X åç next-key lockï¼é£ä¹å¦å¤ä¸ä¸ªäºå¡å¨è·åç¸åèå´ç X åç next-key lock æ¶ï¼å°±ä¼è¢«é»å¡ã
è½ç¶ç¸åèå´çé´ééæ¯å¤ä¸ªäºå¡ç¸äºå ¼å®¹çï¼ä½å¯¹äºè®°å½éï¼æ们æ¯è¦èè X åä¸ S åå ³ç³»ï¼X åçè®°å½éä¸ X åçè®°å½éæ¯å²çªçã
MySQL æ¯æä¹å è¡çº§éçï¼
è¡çº§éå éè§åæ¯è¾å¤æï¼ä¸åçåºæ¯ï¼å éçå½¢å¼æ¯ä¸åçã
å éç对象æ¯ç´¢å¼ï¼å éçåºæ¬åä½æ¯ next-key lockï¼å®æ¯ç±è®°å½éåé´ééç»åèæçï¼next-key lock æ¯åå¼åéåºé´ï¼èé´ééæ¯åå¼åå¼åºé´ã
ä½æ¯ï¼next-key lock å¨ä¸äºåºæ¯ä¸ä¼éåæè®°å½éæé´ééã
é£å°åºæ¯ä»ä¹åºæ¯å¢ï¼
è¿æ¬¡ä¼ä»¥ä¸é¢è¿ä¸ªè¡¨ç»ææ¥è¿è¡å®éªè¯´æï¼
sqlå¤å¶ä»£ç CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`),
KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
å ¶ä¸ï¼id æ¯ä¸»é®ç´¢å¼ï¼å¯ä¸ç´¢å¼ï¼ï¼age æ¯æ®éç´¢å¼ï¼éå¯ä¸ç´¢å¼ï¼ï¼name æ¯æ®éçåã
表ä¸çæè¿äºè¡è®°å½ï¼
è¿æ¬¡å®éªç¯å¢ç MySQL çæ¬æ¯ 8.0.26ï¼é离级å«æ¯ãå¯éå¤è¯»ãã
ä¸åçæ¬çå éè§åå¯è½æ¯ä¸åçï¼ä½æ¯å¤§ä½ä¸æ¯ç¸åçã
å¯ä¸ç´¢å¼çå¼æ¥è¯¢
å½æ们ç¨å¯ä¸ç´¢å¼è¿è¡çå¼æ¥è¯¢çæ¶åï¼æ¥è¯¢çè®°å½åä¸åå¨ï¼å éçè§åä¹ä¼ä¸åï¼
- å½æ¥è¯¢çè®°å½æ¯ãåå¨ãçï¼å¨ç´¢å¼æ ä¸å®ä½å°è¿ä¸æ¡è®°å½åï¼å°è¯¥è®°å½çç´¢å¼ä¸ç next-key lock ä¼éåæãè®°å½éãã
- å½æ¥è¯¢çè®°å½æ¯ãä¸åå¨ãçï¼åä¼å¨ç´¢å¼æ æ¾å°ç¬¬ä¸æ¡å¤§äºè¯¥æ¥è¯¢è®°å½çè®°å½ï¼ç¶åå°è¯¥è®°å½çç´¢å¼ä¸ç next-key lock ä¼éåæãé´ééãã
æ¥ä¸éç¨ä¸¤ä¸ªæ¡ä¾æ¥è¯´æã
1ãè®°å½åå¨çæ åµ
å设äºå¡ A æ§è¡äºè¿æ¡çå¼æ¥è¯¢è¯å¥ï¼æ¥è¯¢çè®°å½æ¯ãåå¨ãäºè¡¨ä¸çã
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | è·¯é£ | 19 |
+----+--------+-----+
1 row in set (0.02 sec)
é£ä¹ï¼äºå¡ A ä¼ä¸º id 为 1 çè¿æ¡è®°å½å°±ä¼å ä¸ X åçè®°å½éã
æ¥ä¸æ¥ï¼å¦ææå ¶ä»äºå¡ï¼å¯¹ id 为 1 çè®°å½è¿è¡æ´æ°æè å é¤æä½çè¯ï¼è¿äºæä½é½ä¼è¢«é»å¡ï¼å 为æ´æ°æè å é¤æä½ä¹ä¼å¯¹è®°å½å X åçè®°å½éï¼è X éå X éä¹é´æ¯äºæ¥å ³ç³»ã
æ¯å¦ï¼ä¸é¢è¿ä¸ªä¾åï¼
å 为äºå¡ A 对 id = 1çè®°å½å äº X åçè®°å½éï¼æ以äºå¡ B å¨ä¿®æ¹ id=1 çè®°å½æ¶ä¼è¢«é»å¡ï¼äºå¡ C å¨å é¤ id=1 çè®°å½æ¶ä¹ä¼è¢«é»å¡ã
æä»ä¹å½ä»¤å¯ä»¥åæå äºä»ä¹éï¼
æ们å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥ï¼æ¥çäºå¡æ§è¡ SQL è¿ç¨ä¸å äºä»ä¹éã
æ们以åé¢çäºå¡ A ä½ä¸ºä¾åï¼åæä¸ä¸å®å äºä»ä¹éã
ä»ä¸å¾å¯ä»¥çå°ï¼å ±å äºä¸¤ä¸ªéï¼åå«æ¯ï¼
- 表éï¼X ç±»åçæåéï¼
- è¡éï¼X ç±»åçè®°å½éï¼
è¿éæ们éç¹å ³æ³¨è¡çº§éï¼å¾ä¸ LOCK_TYPE ä¸ç RECORD 表示è¡çº§éï¼èä¸æ¯è®°å½éçææã
éè¿ LOCK_MODE å¯ä»¥ç¡®è®¤æ¯ next-key éï¼è¿æ¯é´ééï¼è¿æ¯è®°å½éï¼
- å¦æ LOCK_MODE 为 Xï¼è¯´ææ¯ next-key éï¼
- å¦æ LOCK_MODE 为 X, REC_NOT_GAPï¼è¯´ææ¯è®°å½éï¼
- å¦æ LOCK_MODE 为 X, GAPï¼è¯´ææ¯é´ééï¼
å æ¤ï¼**æ¤æ¶äºå¡ A å¨ id = 1 è®°å½ç主é®ç´¢å¼ä¸å çæ¯è®°å½éï¼éä½çèå´æ¯ id 为 1 çè¿æ¡è®°å½ã**è¿æ ·å ¶ä»äºå¡å°±æ æ³å¯¹ id 为 1 çè¿æ¡è®°å½è¿è¡æ´æ°åå é¤æä½äºã
ä»è¿éæ们ä¹å¯ä»¥å¾ç¥ï¼å éç对象æ¯é对索å¼ï¼å 为è¿éæ¥è¯¢è¯å¥æ«æç B+ æ æ¯èç°ç´¢å¼æ ï¼å³ä¸»é®ç´¢å¼æ ï¼æ以æ¯å¯¹ä¸»é®ç´¢å¼å éãå°å¯¹åºè®°å½ç主é®ç´¢å¼å è®°å½éåï¼å°±æå³çå ¶ä»äºå¡æ æ³å¯¹è¯¥è®°å½è¿è¡æ´æ°åå é¤æä½äºã
2ãè®°å½ä¸åå¨çæ åµ
å设äºå¡ A æ§è¡äºè¿æ¡çå¼æ¥è¯¢è¯å¥ï¼æ¥è¯¢çè®°å½æ¯ãä¸åå¨ãäºè¡¨ä¸çã
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 2 for update;
Empty set (0.03 sec)
æ¥ä¸æ¥ï¼éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥ï¼æ¥çäºå¡æ§è¡ SQL è¿ç¨ä¸å äºä»ä¹éã
ä»ä¸å¾å¯ä»¥çå°ï¼å ±å äºä¸¤ä¸ªéï¼åå«æ¯ï¼
- 表éï¼X ç±»åçæåéï¼
- è¡éï¼X ç±»åçé´ééï¼
å æ¤ï¼æ¤æ¶äºå¡ A å¨ id = 5 è®°å½ç主é®ç´¢å¼ä¸å çæ¯é´ééï¼éä½çèå´æ¯ (1, 5)ã
æ¥ä¸æ¥ï¼å¦ææå ¶ä»äºå¡æå ¥ id å¼ä¸º 2ã3ã4 è¿ä¸äºè®°å½çè¯ï¼è¿äºæå ¥è¯å¥é½ä¼åçé»å¡ã
注æï¼å¦æå ¶ä»äºå¡æå ¥ç id = 1 æè id = 5 çè®°å½è¯ï¼å¹¶ä¸ä¼åçé»å¡ï¼èæ¯æ¥ä¸»é®å²çªçé误ï¼å 为表ä¸å·²ç»åå¨ id = 1 å id = 5 çè®°å½äºã
æ¯å¦ï¼ä¸é¢è¿ä¸ªä¾åï¼
å 为äºå¡ A å¨ id = 5 è®°å½ç主é®ç´¢å¼ä¸å äºèå´ä¸º (1, 5) ç X åé´ééï¼æ以äºå¡ B å¨æå ¥ä¸æ¡ id 为 3 çè®°å½æ¶ä¼è¢«é»å¡ä½ï¼å³æ æ³æå ¥ id = 3 çè®°å½ã
é´ééçèå´(1, 5) ï¼æ¯æä¹ç¡®å®çï¼
æ ¹æ®æçç»éªï¼å¦æ LOCK_MODE æ¯ next-key éæè é´ééï¼é£ä¹ LOCK_DATA 就表示éçèå´ãå³è¾¹çãï¼æ¤æ¬¡çäºå¡ A ç LOCK_DATA æ¯ 5ã
ç¶åéèå´çã左边çãæ¯è¡¨ä¸ id 为 5 çä¸ä¸æ¡è®°å½ç id å¼ï¼å³ 1ã
å æ¤ï¼é´ééçèå´(1, 5)ã
å¯ä¸ç´¢å¼èå´æ¥è¯¢
èå´æ¥è¯¢åçå¼æ¥è¯¢çå éè§åæ¯ä¸åçã
å½å¯ä¸ç´¢å¼è¿è¡èå´æ¥è¯¢æ¶ï¼ä¼å¯¹æ¯ä¸ä¸ªæ«æå°çç´¢å¼å next-key éï¼ç¶åå¦æéå°ä¸é¢è¿äºæ åµï¼ä¼éåæè®°å½éæè é´ééï¼
- æ åµä¸ï¼é对ã大äºçäºãçèå´æ¥è¯¢ï¼å 为åå¨çå¼æ¥è¯¢çæ¡ä»¶ï¼é£ä¹å¦æçå¼æ¥è¯¢çè®°å½æ¯åå¨äºè¡¨ä¸ï¼é£ä¹è¯¥è®°å½çç´¢å¼ä¸ç next-key éä¼éåæè®°å½éã
- æ åµäºï¼é对ãå°äºæè å°äºçäºãçèå´æ¥è¯¢ï¼è¦çæ¡ä»¶å¼çè®°å½æ¯å¦åå¨äºè¡¨ä¸ï¼å½æ¡ä»¶å¼çè®°å½ä¸å¨è¡¨ä¸ï¼é£ä¹ä¸ç®¡æ¯ãå°äºãè¿æ¯ãå°äºçäºãæ¡ä»¶çèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½çç´¢å¼ç next-key éä¼éåæé´ééï¼å ¶ä»æ«æå°çè®°å½ï¼é½æ¯å¨è¿äºè®°å½çç´¢å¼ä¸å next-key éãå½æ¡ä»¶å¼çè®°å½å¨è¡¨ä¸ï¼å¦ææ¯ãå°äºãæ¡ä»¶çèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½çç´¢å¼ç next-key éä¼éåæé´ééï¼å ¶ä»æ«æå°çè®°å½ï¼é½æ¯å¨è¿äºè®°å½çç´¢å¼ä¸å next-key éï¼å¦æãå°äºçäºãæ¡ä»¶çèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½çç´¢å¼ next-key éä¸ä¼éåæé´ééãå ¶ä»æ«æå°çè®°å½ï¼é½æ¯å¨è¿äºè®°å½çç´¢å¼ä¸å next-key éã
æ¥ä¸æ¥ï¼éè¿å 个å®éªï¼æéªè¯æä¸é¢è¯´çç»è®ºã
1ãé对ã大äºæè 大äºçäºãçèå´æ¥è¯¢
å®éªä¸ï¼é对ã大äºãçèå´æ¥è¯¢çæ åµã
å设äºå¡ A æ§è¡äºè¿æ¡èå´æ¥è¯¢è¯å¥ï¼
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id > 15 for update;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 20 | é¦å
æ¯ | 39 |
+----+-----------+-----+
1 row in set (0.01 sec)
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- æå¼å§è¦æ¾ç第ä¸è¡æ¯ id = 20ï¼ç±äºæ¥è¯¢è¯¥è®°å½ä¸æ¯ä¸ä¸ªçå¼æ¥è¯¢ï¼ä¸æ¯å¤§äºçäºæ¡ä»¶æ¥è¯¢ï¼ï¼æ以对该主é®ç´¢å¼å çæ¯èå´ä¸º (15, 20] ç next-key éï¼
- ç±äºæ¯èå´æ¥æ¾ï¼å°±ä¼ç»§ç»å¾åæ¾åå¨çè®°å½ï¼è½ç¶æ们çè§è¡¨ä¸æåä¸æ¡è®°å½æ¯ id = 20 çè®°å½ï¼ä½æ¯å®é å¨ Innodb åå¨å¼æä¸ï¼ä¼ç¨ä¸ä¸ªç¹æ®çè®°å½æ¥æ è¯æåä¸æ¡è®°å½ï¼è¯¥ç¹æ®çè®°å½çååå« supremum pseudo-record ï¼æ以æ«æ第äºè¡çæ¶åï¼ä¹å°±æ«æå°äºè¿ä¸ªç¹æ®è®°å½çæ¶åï¼ä¼å¯¹è¯¥ä¸»é®ç´¢å¼å çæ¯èå´ä¸º (20, +â] ç next-key éã
- åæ¢æ«æã
å¯ä»¥å¾ç¥ï¼äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äºä¸¤ä¸ª X å ç next-key éï¼
- å¨ id = 20 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (15, 20] ç next-key éï¼æå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 20 çè®°å½ï¼åæ¶æ æ³æå ¥ id å¼ä¸º 16ã17ã18ã19 çè¿ä¸äºæ°è®°å½ã
- å¨ç¹æ®è®°å½ï¼ supremum pseudo-recordï¼ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (20, +â] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æå ¥ id å¼å¤§äº 20 çè¿ä¸äºæ°è®°å½ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
ä»ä¸å¾ä¸çåæä¸ï¼ä¹å¯ä»¥å¾å°äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äºä¸¤ä¸ª X å çnext-key éï¼
- å¨ id = 20 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (15, 20] ç next-key éï¼æå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 20 çè®°å½ï¼åæ¶æ æ³æå ¥ id å¼ä¸º 16ã17ã18ã19 çè¿ä¸äºæ°è®°å½ã
- å¨ç¹æ®è®°å½ï¼ supremum pseudo-recordï¼ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (20, +â] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æå ¥ id å¼å¤§äº 20 çè¿ä¸äºæ°è®°å½ã
å®éªäºï¼é对ã大äºçäºãçèå´æ¥è¯¢çæ åµã
å设äºå¡ A æ§è¡äºè¿æ¡èå´æ¥è¯¢è¯å¥ï¼
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id >= 15 for update;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 15 | ä¹ç´¢æ® | 20 |
| 20 | é¦å
æ¯ | 39 |
+----+-----------+-----+
2 rows in set (0.00 sec)
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- æå¼å§è¦æ¾ç第ä¸è¡æ¯ id = 15ï¼ç±äºæ¥è¯¢è¯¥è®°å½æ¯ä¸ä¸ªçå¼æ¥è¯¢ï¼çäº 15ï¼ï¼æ以该主é®ç´¢å¼ç next-key éä¼éåæè®°å½éï¼ä¹å°±æ¯ä» éä½ id = 15 è¿ä¸è¡è®°å½ã
- ç±äºæ¯èå´æ¥æ¾ï¼å°±ä¼ç»§ç»å¾åæ¾åå¨çè®°å½ï¼æ«æå°ç第äºè¡æ¯ id = 20ï¼äºæ¯å¯¹è¯¥ä¸»é®ç´¢å¼å çæ¯èå´ä¸º (15, 20] ç next-key éï¼
- æ¥çæ«æå°ç¬¬ä¸è¡çæ¶åï¼æ«æå°äºç¹æ®è®°å½ï¼ supremum pseudo-recordï¼ï¼äºæ¯å¯¹è¯¥ä¸»é®ç´¢å¼å çæ¯èå´ä¸º (20, +â] ç next-key éã
- åæ¢æ«æã
å¯ä»¥å¾ç¥ï¼äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äºä¸ä¸ª X å çéï¼åå«æ¯ï¼
- å¨ id = 15 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºè®°å½éï¼èå´æ¯ id = 15 è¿ä¸è¡è®°å½ï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ id = 15 çè¿ä¸æ¡è®°å½ï¼
- å¨ id = 20 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äº next-key éï¼èå´æ¯ (15, 20] ãæå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 20 çè®°å½ï¼åæ¶æ æ³æå ¥ id å¼ä¸º 16ã17ã18ã19 çè¿ä¸äºæ°è®°å½ã
- å¨ç¹æ®è®°å½ï¼ supremum pseudo-recordï¼ç主é®ç´¢å¼ä¸ï¼å äº next-key éï¼èå´æ¯ (20, +â] ãæå³çå ¶ä»äºå¡æ æ³æå ¥ id å¼å¤§äº 20 çè¿ä¸äºæ°è®°å½ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
éè¿åé¢è¿ä¸ªå®éªï¼æ们è¯æäºï¼
- é对ã大äºçäºãæ¡ä»¶çå¯ä¸ç´¢å¼èå´æ¥è¯¢çæ åµä¸ï¼ å¦ææ¡ä»¶å¼çè®°å½åå¨äºè¡¨ä¸ï¼é£ä¹ç±äºæ¥è¯¢è¯¥æ¡ä»¶å¼çè®°å½æ¯å å«ä¸ä¸ªçå¼æ¥è¯¢çæä½ï¼æ以该记å½çç´¢å¼ä¸ç next-key éä¼éåæè®°å½éã
2ãé对ãå°äºæè å°äºçäºãçèå´æ¥è¯¢
å®éªä¸ï¼é对ãå°äºãçèå´æ¥è¯¢æ¶ï¼æ¥è¯¢æ¡ä»¶å¼çè®°å½ãä¸åå¨ã表ä¸çæ åµã
å设äºå¡ A æ§è¡äºè¿æ¡èå´æ¥è¯¢è¯å¥ï¼æ³¨ææ¥è¯¢æ¡ä»¶å¼çè®°å½ï¼id 为 6ï¼å¹¶ä¸åå¨äºè¡¨ä¸ã
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id < 6 for update;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | è·¯é£ | 19 |
| 5 | ç´¢é | 21 |
+----+--------+-----+
3 rows in set (0.00 sec)
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- æå¼å§è¦æ¾ç第ä¸è¡æ¯ id = 1ï¼äºæ¯å¯¹è¯¥ä¸»é®ç´¢å¼å çæ¯èå´ä¸º (-â, 1] ç next-key éï¼
- ç±äºæ¯èå´æ¥æ¾ï¼å°±ä¼ç»§ç»å¾åæ¾åå¨çè®°å½ï¼æ«æå°ç第äºè¡æ¯ id = 5ï¼æ以对该主é®ç´¢å¼å çæ¯èå´ä¸º (1, 5] ç next-key éï¼
- ç±äºæ«æå°ç第äºè¡è®°å½ï¼id = 5ï¼ï¼æ»¡è¶³ id < 6 æ¡ä»¶ï¼èä¸ä¹æ²¡æè¾¾å°ç»æ¢æ«æçæ¡ä»¶ï¼æ¥çä¼ç»§ç»æ«æã
- æ«æå°ç第ä¸è¡æ¯ id = 10ï¼è¯¥è®°å½ä¸æ»¡è¶³ id < 6 æ¡ä»¶çè®°å½ï¼æ以 id = 10 è¿ä¸è¡è®°å½çéä¼éåæé´ééï¼äºæ¯å¯¹è¯¥ä¸»é®ç´¢å¼å çæ¯èå´ä¸º (5, 10) çé´ééã
- ç±äºæ«æå°ç第ä¸è¡è®°å½ï¼id = 10ï¼ï¼ä¸æ»¡è¶³ id < 6 æ¡ä»¶ï¼è¾¾å°äºç»æ¢æ«æçæ¡ä»¶ï¼äºæ¯åæ¢æ«æã
ä»ä¸é¢çåæä¸ï¼å¯ä»¥å¾ç¥äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äºä¸ä¸ª X åçéï¼
- å¨ id = 1 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (-â, 1] ç next-key éï¼æå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 1 çè¿ä¸æ¡è®°å½ï¼åæ¶ä¹æ æ³æå ¥ id å°äº 1 çè¿ä¸äºæ°è®°å½ã
- å¨ id = 5 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (1, 5] ç next-key éï¼æå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 5 çè¿ä¸æ¡è®°å½ï¼åæ¶ä¹æ æ³æå ¥ id å¼ä¸º 2ã3ã4 çè¿ä¸äºæ°è®°å½ã
- å¨ id = 10 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (5, 10) çé´ééï¼æå³çå ¶ä»äºå¡æ æ³æå ¥ id å¼ä¸º 6ã7ã8ã9 çè¿ä¸äºæ°è®°å½ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
ä»ä¸å¾ä¸çåæä¸ï¼ä¹å¯ä»¥å¾ç¥äºå¡ A å¨ä¸»é®ç´¢å¼å çä¸ä¸ªéï¼å°±æ¯æ们åé¢åæåºé£ä¸ä¸ªéã
è½ç¶è¿æ¬¡èå´æ¥è¯¢çæ¡ä»¶æ¯ãå°äºãï¼ä½æ¯æ¥è¯¢æ¡ä»¶å¼çè®°å½ä¸åå¨äºè¡¨ä¸ï¼ id 为 6 çè®°å½ä¸å¨è¡¨ä¸ï¼ï¼æ以å¦æäºå¡ A çèå´æ¥è¯¢çæ¡ä»¶æ¹æ <= 6 çè¯ï¼å çéè¿æ¯åèå´æ¥è¯¢æ¡ä»¶ä¸º < 6 æ¯ä¸æ ·çã 大家èªå·±ä¹éªè¯ä¸è¿ä¸ªç»è®ºã
å æ¤ï¼é对ãå°äºæè å°äºçäºãçå¯ä¸ç´¢å¼èå´æ¥è¯¢ï¼å¦ææ¡ä»¶å¼çè®°å½ä¸å¨è¡¨ä¸ï¼é£ä¹ä¸ç®¡æ¯ãå°äºãè¿æ¯ãå°äºçäºãçèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½ä¸ç´¢å¼ç next-key éä¼éåæé´ééï¼å ¶ä»æ«æçè®°å½ï¼åæ¯å¨è¿äºè®°å½çç´¢å¼ä¸å next-key éã
å®éªäºï¼é对ãå°äºçäºãçèå´æ¥è¯¢æ¶ï¼æ¥è¯¢æ¡ä»¶å¼çè®°å½ãåå¨ã表ä¸çæ åµã
å设äºå¡ A æ§è¡äºè¿æ¡èå´æ¥è¯¢è¯å¥ï¼æ³¨ææ¥è¯¢æ¡ä»¶å¼çè®°å½ï¼id 为 5ï¼åå¨äºè¡¨ä¸ã
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id <= 5 for update;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | è·¯é£ | 19 |
| 5 | ç´¢é | 21 |
+----+--------+-----+
2 rows in set (0.00 sec)
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- æå¼å§è¦æ¾ç第ä¸è¡æ¯ id = 1ï¼äºæ¯å¯¹è¯¥è®°å½å çæ¯èå´ä¸º (-â, 1] ç next-key éï¼
- ç±äºæ¯èå´æ¥æ¾ï¼å°±ä¼ç»§ç»å¾åæ¾åå¨çè®°å½ï¼æ«æå°ç第äºè¡æ¯ id = 5ï¼äºæ¯å¯¹è¯¥è®°å½å çæ¯èå´ä¸º (1, 5] ç next-key éã
- ç±äºä¸»é®ç´¢å¼å ·æå¯ä¸æ§ï¼ä¸ä¼åå¨ä¸¤ä¸ª id = 5 çè®°å½ï¼æ以ä¸ä¼å继ç»æ«æï¼äºæ¯åæ¢æ«æã
ä»ä¸é¢çåæä¸ï¼å¯ä»¥å¾å°äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äº 2 个 X åçéï¼
- å¨ id = 1 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (-â, 1] ç next-key éãæå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 1 çè¿ä¸æ¡è®°å½ï¼åæ¶ä¹æ æ³æå ¥ id å°äº 1 çè¿ä¸äºæ°è®°å½ã
- å¨ id = 5 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (1, 5] ç next-key éãæå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 5 çè¿ä¸æ¡è®°å½ï¼åæ¶ä¹æ æ³æå ¥ id å¼ä¸º 2ã3ã4 çè¿ä¸äºæ°è®°å½ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
ä»ä¸å¾ä¸çåæä¸ï¼å¯ä»¥å¾å°äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äºä¸¤ä¸ª X å next-key éï¼åå«æ¯ï¼
- å¨ id = 1 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (-â, 1] ç next-key éï¼
- å¨ id = 5 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º(1, 5 ] ç next-key éã
å®éªä¸ï¼åæ¥çé对ãå°äºãçèå´æ¥è¯¢æ¶ï¼æ¥è¯¢æ¡ä»¶å¼çè®°å½ãåå¨ã表ä¸çæ åµã
å¦æäºå¡ A çæ¥è¯¢è¯å¥æ¯å°äºçèå´æ¥è¯¢ï¼ä¸æ¥è¯¢æ¡ä»¶å¼çè®°å½ï¼id 为 5ï¼åå¨äºè¡¨ä¸ã
sqlå¤å¶ä»£ç select * from user where id < 5 for update;
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- æå¼å§è¦æ¾ç第ä¸è¡æ¯ id = 1ï¼äºæ¯å¯¹è¯¥è®°å½å çæ¯èå´ä¸º (-â, 1] ç next-key éï¼
- ç±äºæ¯èå´æ¥æ¾ï¼å°±ä¼ç»§ç»å¾åæ¾åå¨çè®°å½ï¼æ«æå°ç第äºè¡æ¯ id = 5ï¼è¯¥è®°å½æ¯ç¬¬ä¸æ¡ä¸æ»¡è¶³ id < 5 æ¡ä»¶çè®°å½ï¼äºæ¯è¯¥è®°å½çéä¼éå为é´ééï¼éèå´æ¯ (1,5)ã
- ç±äºæ¾å°äºç¬¬ä¸æ¡ä¸æ»¡è¶³ id < 5 æ¡ä»¶çè®°å½ï¼äºæ¯åæ¢æ«æã
å¯ä»¥å¾ç¥ï¼æ¤æ¶äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äºä¸¤ç§ X åéï¼
![](cdn.xiaolincoding.com/gh/xiaolinc⦠(1).png)
- å¨ id = 1 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (-â, 1] ç next-key éï¼æå³çå ¶ä»äºå¡å³æ æ³æ´æ°æè å é¤ id = 1 çè¿ä¸æ¡è®°å½ï¼åæ¶ä¹æ æ³æå ¥ id å°äº 1 çè¿ä¸äºæ°è®°å½ã
- å¨ id = 5 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºèå´ä¸º (1,5) çé´ééï¼æå³çå ¶ä»äºå¡æ æ³æå ¥ id å¼ä¸º 2ã3ã4 çè¿ä¸äºæ°è®°å½ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
ä»ä¸å¾ä¸çåæä¸ï¼å¯ä»¥å¾å°äºå¡ A å¨ä¸»é®ç´¢å¼ä¸å äº X åçèå´ä¸º (-â, 1] ç next-key éï¼å X åçèå´ä¸º (1, 5) çé´ééã
å æ¤ï¼éè¿åé¢è¿ä¸ä¸ªå®éªï¼å¯ä»¥å¾ç¥ã
å¨é对ãå°äºæè å°äºçäºãçå¯ä¸ç´¢å¼ï¼ä¸»é®ç´¢å¼ï¼èå´æ¥è¯¢æ¶ï¼åå¨è¿ä¸¤ç§æ åµä¼å°ç´¢å¼ç next-key éä¼éåæé´ééçï¼
- å½æ¡ä»¶å¼çè®°å½ãä¸å¨ã表ä¸æ¶ï¼é£ä¹ä¸ç®¡æ¯ãå°äºãè¿æ¯ãå°äºçäºãæ¡ä»¶çèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½ç主é®ç´¢å¼ä¸ç next-key éä¼éåæé´ééï¼å ¶ä»æ«æå°çè®°å½ï¼é½æ¯å¨è¿äºè®°å½ç主é®ç´¢å¼ä¸å next-key éã
- å½æ¡ä»¶å¼çè®°å½ãå¨ã表ä¸æ¶ï¼å¦ææ¯ãå°äºãæ¡ä»¶çèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½ç主é®ç´¢å¼ä¸ç next-key éä¼éåæé´ééï¼å ¶ä»æ«æå°çè®°å½ï¼é½æ¯å¨è¿äºè®°å½ç主é®ç´¢å¼ä¸ï¼å next-key éãå¦ææ¯ãå°äºçäºãæ¡ä»¶çèå´æ¥è¯¢ï¼æ«æå°ç»æ¢èå´æ¥è¯¢çè®°å½æ¶ï¼è¯¥è®°å½ç主é®ç´¢å¼ä¸ç next-key éãä¸ä¼ãéåæé´ééï¼å ¶ä»æ«æå°çè®°å½ï¼é½æ¯å¨è¿äºè®°å½ç主é®ç´¢å¼ä¸å next-key éã
éå¯ä¸ç´¢å¼çå¼æ¥è¯¢
å½æ们ç¨éå¯ä¸ç´¢å¼è¿è¡çå¼æ¥è¯¢çæ¶åï¼å 为åå¨ä¸¤ä¸ªç´¢å¼ï¼ä¸ä¸ªæ¯ä¸»é®ç´¢å¼ï¼ä¸ä¸ªæ¯éå¯ä¸ç´¢å¼ï¼äºçº§ç´¢å¼ï¼ï¼æ以å¨å éæ¶ï¼åæ¶ä¼å¯¹è¿ä¸¤ä¸ªç´¢å¼é½å éï¼ä½æ¯å¯¹ä¸»é®ç´¢å¼å éçæ¶åï¼åªæ满足æ¥è¯¢æ¡ä»¶çè®°å½æä¼å¯¹å®ä»¬ç主é®ç´¢å¼å éã
é对éå¯ä¸ç´¢å¼çå¼æ¥è¯¢æ¶ï¼æ¥è¯¢çè®°å½åä¸åå¨ï¼å éçè§åä¹ä¼ä¸åï¼
- å½æ¥è¯¢çè®°å½ãåå¨ãæ¶ï¼ç±äºä¸æ¯å¯ä¸ç´¢å¼ï¼æ以è¯å®åå¨ç´¢å¼å¼ç¸åçè®°å½ï¼äºæ¯éå¯ä¸ç´¢å¼çå¼æ¥è¯¢çè¿ç¨æ¯ä¸ä¸ªæ«æçè¿ç¨ï¼ç´å°æ«æå°ç¬¬ä¸ä¸ªä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½å°±åæ¢æ«æï¼ç¶åå¨æ«æçè¿ç¨ä¸ï¼å¯¹æ«æå°çäºçº§ç´¢å¼è®°å½å çæ¯ next-key éï¼è对äºç¬¬ä¸ä¸ªä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½ï¼è¯¥äºçº§ç´¢å¼ç next-key éä¼éåæé´ééãåæ¶ï¼å¨ç¬¦åæ¥è¯¢æ¡ä»¶çè®°å½ç主é®ç´¢å¼ä¸å è®°å½éã
- å½æ¥è¯¢çè®°å½ãä¸åå¨ãæ¶ï¼æ«æå°ç¬¬ä¸æ¡ä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½ï¼è¯¥äºçº§ç´¢å¼ç next-key éä¼éåæé´ééãå 为ä¸åå¨æ»¡è¶³æ¥è¯¢æ¡ä»¶çè®°å½ï¼æ以ä¸ä¼å¯¹ä¸»é®ç´¢å¼å éã
æ¥ä¸éç¨ä¸¤ä¸ªå®éªæ¥è¯´æã
1ãè®°å½ä¸åå¨çæ åµ
å®éªä¸ï¼é对éå¯ä¸ç´¢å¼çå¼æ¥è¯¢æ¶ï¼æ¥è¯¢çå¼ä¸åå¨çæ åµã
å æ¥è¯´è¯´éå¯ä¸ç´¢å¼çå¼æ¥è¯¢æ¶ï¼æ¥è¯¢çè®°å½ä¸åå¨çæ åµï¼å 为è¿ä¸ªæ¯è¾ç®åã
å设äºå¡ A 对éå¯ä¸ç´¢å¼ï¼ageï¼è¿è¡äºçå¼æ¥è¯¢ï¼ä¸è¡¨ä¸ä¸åå¨ age = 25 çè®°å½ã
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 25 for update;
Empty set (0.00 sec)
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- å®ä½å°ç¬¬ä¸æ¡ä¸ç¬¦åæ¥è¯¢æ¡ä»¶çäºçº§ç´¢å¼è®°å½ï¼å³æ«æå° age = 39ï¼äºæ¯è¯¥äºçº§ç´¢å¼ç next-key éä¼éåæé´ééï¼èå´æ¯ (22, 39)ã
- åæ¢æ¥è¯¢
äºå¡ A å¨ age = 39 è®°å½çäºçº§ç´¢å¼ä¸ï¼å äº X åçé´ééï¼èå´æ¯ (22, 39)ãæå³çå ¶ä»äºå¡æ æ³æå ¥ age å¼ä¸º 23ã24ã25ã26ã....ã38 è¿äºæ°è®°å½ãä¸è¿å¯¹äºæå ¥ age = 22 å age = 39 è®°å½çè¯å¥ï¼å¨ä¸äºæ åµæ¯å¯ä»¥æåæå ¥çï¼èä¸äºæ åµåæ æ³æåæå ¥ï¼å ·ä½åªäºæ åµï¼ä¼å¨åé¢è¯´ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
ä»ä¸å¾çåæï¼å¯ä»¥çå°ï¼äºå¡ A å¨ age = 39 è®°å½çäºçº§ç´¢å¼ä¸ï¼INDEX_NAME: index_age ï¼ï¼å äºèå´ä¸º (22, 39) ç X åé´ééã
æ¤æ¶ï¼å¦ææå ¶ä»äºå¡æå ¥äº age å¼ä¸º 23ã24ã25ã26ã....ã38 è¿äºæ°è®°å½ï¼é£ä¹è¿äºæå ¥è¯å¥é½ä¼åçé»å¡ãä¸è¿å¯¹äºæå ¥ age = 39 è®°å½çè¯å¥ï¼å¨ä¸äºæ åµæ¯å¯ä»¥æåæå ¥çï¼èä¸äºæ åµåæ æ³æåæå ¥ï¼å ·ä½åªäºæ åµï¼æ¥ä¸æ¥æ们就说ï¼
å½æä¸ä¸ªäºå¡ææäºçº§ç´¢å¼çé´éé (22, 39) æ¶ï¼ä»ä¹æ åµä¸ï¼å¯ä»¥è®©å ¶ä»äºå¡çæå ¥ age = 22 æè age = 39 è®°å½çè¯å¥æåï¼åæ¯ä»ä¹æ åµä¸ï¼æå ¥ age = 22 æè age = 39 è®°å½æ¶çè¯å¥ä¼è¢«é»å¡ï¼
æ们å è¦æ¸ æ¥ï¼ä»ä¹æ åµä¸æå ¥è¯å¥ä¼åçé»å¡ã
æå ¥è¯å¥å¨æå ¥ä¸æ¡è®°å½ä¹åï¼éè¦å å®ä½å°è¯¥è®°å½å¨ B+æ çä½ç½®ï¼å¦ææå ¥çä½ç½®çä¸ä¸æ¡è®°å½çç´¢å¼ä¸æé´ééï¼æä¼åçé»å¡ã
å¨åæäºçº§ç´¢å¼çé´ééæ¯å¦å¯ä»¥æåæå ¥è®°å½æ¶ï¼æ们è¦å è¦ç¥éäºçº§ç´¢å¼æ æ¯å¦ä½åæ¾è®°å½çï¼
äºçº§ç´¢å¼æ æ¯æç §äºçº§ç´¢å¼å¼ï¼ageåï¼æ顺åºåæ¾çï¼å¨ç¸åçäºçº§ç´¢å¼å¼æ åµä¸ï¼ åæä¸»é® id ç顺åºåæ¾ãç¥éäºè¿ä¸ªåæï¼æ们æè½ç¥éæ§è¡æå ¥è¯å¥çæ¶åï¼æå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯è°ã
åºäºåé¢çå®éªï¼äºå¡ A æ¯å¨ age = 39 è®°å½çäºçº§ç´¢å¼ä¸ï¼å äº X åçé´ééï¼èå´æ¯ (22, 39)ã
æå ¥ age = 22 è®°å½çæåå失败çæ åµåå«å¦ä¸ï¼
- å½å ¶ä»äºå¡æå ¥ä¸æ¡ age = 22ï¼id = 3 çè®°å½çæ¶åï¼å¨äºçº§ç´¢å¼æ ä¸å®ä½å°æå ¥çä½ç½®ï¼è该ä½ç½®çä¸ä¸æ¡æ¯ id = 10ãage = 22 çè®°å½ï¼è¯¥è®°å½çäºçº§ç´¢å¼ä¸æ²¡æé´ééï¼æ以è¿æ¡æå ¥è¯å¥å¯ä»¥æ§è¡æåã
- å½å ¶ä»äºå¡æå ¥ä¸æ¡ age = 22ï¼id = 12 çè®°å½çæ¶åï¼å¨äºçº§ç´¢å¼æ ä¸å®ä½å°æå ¥çä½ç½®ï¼è该ä½ç½®çä¸ä¸æ¡æ¯ id = 20ãage = 39 çè®°å½ï¼æ£å¥½è¯¥è®°å½çäºçº§ç´¢å¼ä¸æé´ééï¼æ以è¿æ¡æå ¥è¯å¥ä¼è¢«é»å¡ï¼æ æ³æå ¥æåã
æå ¥ age = 39 è®°å½çæåå失败çæ åµåå«å¦ä¸ï¼
- å½å ¶ä»äºå¡æå ¥ä¸æ¡ age = 39ï¼id = 3 çè®°å½çæ¶åï¼å¨äºçº§ç´¢å¼æ ä¸å®ä½å°æå ¥çä½ç½®ï¼è该ä½ç½®çä¸ä¸æ¡æ¯ id = 20ãage = 39 çè®°å½ï¼æ£å¥½è¯¥è®°å½çäºçº§ç´¢å¼ä¸æé´ééï¼æ以è¿æ¡æå ¥è¯å¥ä¼è¢«é»å¡ï¼æ æ³æå ¥æåã
- å½å ¶ä»äºå¡æå ¥ä¸æ¡ age = 39ï¼id = 21 çè®°å½çæ¶åï¼å¨äºçº§ç´¢å¼æ ä¸å®ä½å°æå ¥çä½ç½®ï¼è该ä½ç½®çä¸ä¸æ¡è®°å½ä¸åå¨ï¼ä¹å°±æ²¡æé´ééäºï¼æ以è¿æ¡æå ¥è¯å¥å¯ä»¥æå ¥æåã
æ以ï¼å½æä¸ä¸ªäºå¡ææäºçº§ç´¢å¼çé´éé (22, 39) æ¶ï¼æå ¥ age = 22 æè age = 39 è®°å½çè¯å¥æ¯å¦å¯ä»¥æ§è¡æåï¼å ³é®è¿è¦èèæå ¥è®°å½ç主é®å¼ï¼å 为ãäºçº§ç´¢å¼å¼ï¼ageåï¼+主é®å¼ï¼idåï¼ãæå¯ä»¥ç¡®å®æå ¥çä½ç½®ï¼ç¡®å®äºæå ¥ä½ç½®åï¼å°±è¦çæå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯å¦æé´ééï¼å¦ææé´ééï¼å°±ä¼åçé»å¡ï¼å¦æ没æé´ééï¼åå¯ä»¥æå ¥æåã
ç¥éäºè¿ä¸ªç»è®ºä¹åï¼æ们ååè¿å¤´çï¼éå¯ä¸ç´¢å¼çå¼æ¥è¯¢æ¶ï¼æ¥è¯¢çè®°å½ä¸åå¨æ¶ï¼æ§è¡select * from performance_schema.data_locks\G; è¾åºçç»æã
å¨åé¢åæè¾åºç»æçæ¶åï¼æ说çç»è®ºæ¯ï¼ãäºå¡ A å¨ age = 39 è®°å½çäºçº§ç´¢å¼ä¸ï¼INDEX_NAME: index_age ï¼ï¼å äºèå´ä¸º (22, 39) ç X åé´ééããè¿ä¸ªç»è®ºå ¶å®è¿ä¸å¤åç¡®ï¼å 为åªèèäº LOCK_DATA 第ä¸ä¸ªæ°å¼ï¼39ï¼ï¼æ²¡æèè LOCK_DATA 第äºä¸ªæ°å¼ï¼20ï¼ã
é£ LOCK_DATAï¼39ï¼20 æ¯ä»ä¹ææï¼
- LOCK_DATA 第ä¸ä¸ªæ°å¼ï¼ä¹å°±æ¯ 39ï¼ å®ä»£è¡¨çæ¯ age å¼ãä»åé¢æ们ä¹ç¥éäºï¼LOCK_DATA 第ä¸ä¸ªæ°å¼æ¯ next-key éåé´éééä½çèå´çå³è¾¹çå¼ã
- LOCK_DATA 第äºä¸ªæ°å¼ï¼ä¹å°±æ¯ 20ï¼ å®ä»£è¡¨çæ¯ id å¼ã
ä¹æ以 LOCK_DATA è¦å¤æ¾ç¤ºä¸ä¸ªæ°å¼ï¼IDå¼ï¼ï¼æ¯å 为é对ãå½æ个äºå¡ææéå¯ä¸ç´¢å¼ç (22, 39) é´ééçæ¶åï¼å ¶ä»äºå¡æ¯å¦å¯ä»¥æå ¥ age = 39 æ°è®°å½ãçé®é¢ï¼è¿éè¦èèæå ¥è®°å½ç id å¼ãè LOCK_DATA ç第äºä¸ªæ°å¼ï¼å°±æ¯è¯´æå¨æå ¥ age = 39 æ°è®°å½æ¶ï¼åªäºèå´ç id å¼æ¯ä¸å¯ä»¥æå ¥çã
å æ¤ï¼ LOCK_DATAï¼39ï¼20 + LOCK_MODE : X, GAP çæææ¯ï¼äºå¡ A å¨ age = 39 è®°å½çäºçº§ç´¢å¼ä¸ï¼INDEX_NAME: index_age ï¼ï¼å äº age å¼èå´ä¸º (22, 39) ç X åé´ééï¼**åæ¶éå¯¹å ¶ä»äºå¡æå ¥ age å¼ä¸º 39 çæ°è®°å½æ¶ï¼ä¸å 许æå ¥çæ°è®°å½ç id å¼å°äº 20 **ãå¦ææå ¥çæ°è®°å½ç id å¼å¤§äº 20ï¼åå¯ä»¥æå ¥æåã
ä½æ¯æ们æ æ³ä»select * from performance_schema.data_locks\G; è¾åºçç»æåæåºãå¨æå ¥ age =22 æ°è®°å½æ¶ï¼åªäºèå´ç id å¼æ¯å¯ä»¥æå ¥æåçãï¼è¿æ¶åå°±å¾èªå·±ç»åºäºçº§ç´¢å¼ç B+ æ çç»æï¼ç¶åç¡®å®æå ¥ä½ç½®åï¼çä¸è¯¥ä½ç½®çä¸ä¸æ¡è®°å½æ¯å¦åå¨é´ééï¼å¦æåå¨é´ééï¼åæ æ³æå ¥æåï¼å¦æä¸åå¨é´ééï¼åå¯ä»¥æå ¥æåã
2ãè®°å½åå¨çæ åµ
å®éªäºï¼é对éå¯ä¸ç´¢å¼çå¼æ¥è¯¢æ¶ï¼æ¥è¯¢çå¼åå¨çæ åµã
å设äºå¡ A 对éå¯ä¸ç´¢å¼ï¼ageï¼è¿è¡äºçå¼æ¥è¯¢ï¼ä¸è¡¨ä¸åå¨ age = 22 çè®°å½ã
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 22 for update;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 10 | 山治 | 22 |
+----+--------+-----+
1 row in set (0.00 sec)
äºå¡ A å éååè¿ç¨å¦ä¸ï¼
- ç±äºä¸æ¯å¯ä¸ç´¢å¼ï¼æ以è¯å®åå¨å¼ç¸åçè®°å½ï¼äºæ¯éå¯ä¸ç´¢å¼çå¼æ¥è¯¢çè¿ç¨æ¯ä¸ä¸ªæ«æçè¿ç¨ï¼æå¼å§è¦æ¾ç第ä¸è¡æ¯ age = 22ï¼äºæ¯å¯¹è¯¥äºçº§ç´¢å¼è®°å½å ä¸èå´ä¸º (21, 22] ç next-key éãåæ¶ï¼å 为 age = 22 符åæ¥è¯¢æ¡ä»¶ï¼äºæ¯å¯¹ age = 22 çè®°å½ç主é®ç´¢å¼å ä¸è®°å½éï¼å³å¯¹ id = 10 è¿ä¸è¡å è®°å½éã
- æ¥ç继ç»æ«æï¼æ«æå°ç第äºè¡æ¯ age = 39ï¼è¯¥è®°å½æ¯ç¬¬ä¸ä¸ªä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½ï¼æ以该äºçº§ç´¢å¼ç next-key éä¼éåæé´ééï¼èå´æ¯ (22, 39)ã
- åæ¢æ¥è¯¢ã
å¯ä»¥çå°ï¼äºå¡ A 对主é®ç´¢å¼åäºçº§ç´¢å¼é½å äº X åçéï¼
- 主é®ç´¢å¼ï¼å¨ id = 10 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºè®°å½éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ id = 10 çè¿ä¸è¡è®°å½ã
- äºçº§ç´¢å¼ï¼éå¯ä¸ç´¢å¼ï¼ï¼å¨ age = 22 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸ï¼å äºèå´ä¸º (21, 22] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ age = 22 çè¿ä¸äºæ°è®°å½ï¼ä¸è¿å¯¹äºæå ¥ age = 20 å age = 21 æ°è®°å½çè¯å¥ï¼å¨ä¸äºæ åµæ¯å¯ä»¥æåæå ¥çï¼èä¸äºæ åµåæ æ³æåæå ¥ï¼å ·ä½åªäºæ åµï¼ä¼å¨åé¢è¯´ãå¨ age = 39 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸ï¼å äºèå´ (22, 39) çé´ééãæå³çå ¶ä»äºå¡æ æ³æå ¥ age å¼ä¸º 23ã24ã..... ã38 çè¿ä¸äºæ°è®°å½ãä¸è¿å¯¹äºæå ¥ age = 22 å age = 39 è®°å½çè¯å¥ï¼å¨ä¸äºæ åµæ¯å¯ä»¥æåæå ¥çï¼èä¸äºæ åµåæ æ³æåæå ¥ï¼å ·ä½åªäºæ åµï¼ä¼å¨åé¢è¯´ã
æ们ä¹å¯ä»¥éè¿ select * from performance_schema.data_locks\G; è¿æ¡è¯å¥æ¥ççäºå¡ A å äºä»ä¹éã
è¾åºç»æå¦ä¸ï¼æè¿éåªæªåäºè¡çº§éçå 容ã
ä»ä¸å¾çåæï¼å¯ä»¥çå°ï¼äºå¡ A 对äºçº§ç´¢å¼ï¼INDEX_NAME: index_age ï¼å äºä¸¤ä¸ª X åéï¼åå«æ¯ï¼
- å¨ age = 22 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸ï¼å äºèå´ä¸º (21, 22] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ age = 22 çè¿ä¸äºæ°è®°å½ï¼é对æ¯å¦å¯ä»¥æå ¥ age = 21 å age = 22 çæ°è®°å½ï¼åæå¦ä¸ï¼æ¯å¦å¯ä»¥æå ¥ age = 21 çæ°è®°å½ï¼è¿è¦çæå ¥çæ°è®°å½ç id å¼ï¼å¦ææå ¥ age = 21 æ°è®°å½ç id å¼å°äº 5ï¼é£ä¹å°±å¯ä»¥æå ¥æåï¼å 为æ¤æ¶æå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯ id = 5ï¼age = 21 çè®°å½ï¼è¯¥è®°å½çäºçº§ç´¢å¼ä¸æ²¡æé´ééãå¦ææå ¥ age = 21 æ°è®°å½ç id å¼å¤§äº 5ï¼é£ä¹å°±æ æ³æå ¥æåï¼å 为æ¤æ¶æå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯ id = 20ï¼age = 39 çè®°å½ï¼è¯¥è®°å½çäºçº§ç´¢å¼ä¸æé´ééãæ¯å¦å¯ä»¥æå ¥ age = 22 çæ°è®°å½ï¼è¿è¦çæå ¥çæ°è®°å½ç id å¼ï¼ä» LOCK_DATA : 22, 10 å¯ä»¥å¾ç¥ï¼å ¶ä»äºå¡æå ¥ age å¼ä¸º 22 çæ°è®°å½æ¶ï¼å¦ææå ¥çæ°è®°å½ç id å¼å°äº 10ï¼é£ä¹æå ¥è¯å¥ä¼åçé»å¡ï¼å¦ææå ¥çæ°è®°å½ç id å¤§äº 10ï¼è¿è¦ç该æ°è®°å½æå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯å¦æé´ééï¼å¦æ没æé´ééåå¯ä»¥æå ¥æåï¼å¦ææé´ééï¼åæ æ³æå ¥æåã
- å¨ age = 39 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸ï¼å äºèå´ (22, 39) çé´ééãæå³çå ¶ä»äºå¡æ æ³æå ¥ age å¼ä¸º 23ã24ã..... ã38 çè¿ä¸äºæ°è®°å½ï¼é对æ¯å¦å¯ä»¥æå ¥ age = 22 å age = 39 çæ°è®°å½ï¼åæå¦ä¸ï¼æ¯å¦å¯ä»¥æå ¥ age = 22 çæ°è®°å½ï¼è¿è¦çæå ¥çæ°è®°å½ç id å¼ï¼å¦ææå ¥ age = 22 æ°è®°å½ç id å¼å°äº 10ï¼é£ä¹æå ¥è¯å¥ä¼è¢«é»å¡ï¼æ æ³æå ¥ï¼å 为æ¤æ¶æå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯ id = 10ï¼age = 21 çè®°å½ï¼è¯¥è®°å½çäºçº§ç´¢å¼ä¸æé´ééï¼ age = 22 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸æ next-key éï¼ãå¦ææå ¥ age = 21 æ°è®°å½ç id å¼å¤§äº 10ï¼ä¹æ æ³æå ¥ï¼å 为æ¤æ¶æå ¥çä½ç½®çä¸ä¸æ¡è®°å½æ¯ id = 20ï¼age = 39 çè®°å½ï¼è¯¥è®°å½çäºçº§ç´¢å¼ä¸æé´ééãæ¯å¦å¯ä»¥æå ¥ age = 39 çæ°è®°å½ï¼è¿è¦çæå ¥çæ°è®°å½ç id å¼ï¼ä» LOCK_DATA : 39, 20 å¯ä»¥å¾ç¥ï¼å ¶ä»äºå¡æå ¥ age å¼ä¸º 39 çæ°è®°å½æ¶ï¼å¦ææå ¥çæ°è®°å½ç id å¼å°äº 20ï¼é£ä¹æå ¥è¯å¥ä¼åçé»å¡ï¼å¦ææå ¥çæ°è®°å½ç id å¤§äº 20ï¼åå¯ä»¥æå ¥æåã
åæ¶ï¼äºå¡ A è¿å¯¹ä¸»é®ç´¢å¼ï¼INDEX_NAME: PRIMARY ï¼å äºX åçè®°å½éï¼
- å¨ id = 10 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºè®°å½éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ id = 10 çè¿ä¸è¡è®°å½ã
为ä»ä¹è¿ä¸ªå®éªæ¡ä¾ä¸ï¼éè¦å¨äºçº§ç´¢å¼ç´¢å¼ä¸å èå´ (22, 39) çé´ééï¼
è¦æ¾å°è¿ä¸ªé®é¢ççæ¡ï¼æ们è¦æç½ MySQL å¨å¯éå¤è¯»çé离级å«åºæ¯ä¸ï¼ä¸ºä»ä¹è¦å¼å ¥é´ééï¼å ¶å®æ¯ä¸ºäºé¿å 幻读ç°è±¡çåçã
å¦æè¿ä¸ªå®éªæ¡ä¾ä¸ï¼
sqlå¤å¶ä»£ç select * from user where age = 22 for update;
å¦æäºå¡ A ä¸å¨äºçº§ç´¢å¼ç´¢å¼ä¸å èå´ (22, 39) çé´ééï¼åªå¨äºçº§ç´¢å¼ç´¢å¼ä¸å èå´ä¸º (21, 22] ç next-key éçè¯ï¼é£ä¹å°±ä¼æ幻读çé®é¢ã
åé¢æä¹è¯´è¿ï¼é对èå´ä¸º (21, 22] ç next-key éï¼æ¯æ æ³å®å ¨éä½ age = 22 æ°è®°å½çæå ¥ï¼å 为对äºæ¯å¦å¯ä»¥æå ¥ age = 22 çæ°è®°å½ï¼è¿è¦çæå ¥çæ°è®°å½ç id å¼ï¼ä» LOCK_DATA : 22, 10 å¯ä»¥å¾ç¥ï¼å ¶ä»äºå¡æå ¥ age å¼ä¸º 22 çæ°è®°å½æ¶ï¼å¦ææå ¥çæ°è®°å½ç id å¼å°äº 10ï¼é£ä¹æå ¥è¯å¥ä¼åçé»å¡ï¼å¦ææå ¥çæ°è®°å½ç id å¼å¤§äº 10ï¼åå¯ä»¥æå ¥æåã
ä¹å°±æ¯è¯´ï¼åªå¨äºçº§ç´¢å¼ç´¢å¼ä¸å èå´ä¸º (21, 22] ç next-key éï¼å ¶ä»äºå¡æ¯æå¯è½æå ¥ age å¼ä¸º 22 çæ°è®°å½çï¼æ¯å¦æå ¥ä¸ä¸ª age = 22ï¼id = 12 çæ°è®°å½ï¼ï¼é£ä¹å¦æäºå¡ A åä¸æ¬¡æ¥è¯¢ age = 22 çè®°å½çæ¶åï¼åå两次æ¥è¯¢ age = 22 çç»æéå°±ä¸ä¸æ ·äºï¼è¿æ¶å°±åçäºå¹»è¯»çç°è±¡ã
é£ä¹å½å¨ age = 39 è¿æ¡è®°å½çäºçº§ç´¢å¼ç´¢å¼ä¸å äºèå´ä¸º (22, 39) çé´ééåï¼å ¶ä»äºå¡æ¯æ æ³æå ¥ä¸ä¸ª age = 22ï¼id = 12 çæ°è®°å½ï¼å 为å½å ¶ä»äºå¡æå ¥ä¸æ¡ age = 22ï¼id = 12 çæ°è®°å½çæ¶åï¼å¨äºçº§ç´¢å¼æ ä¸å®ä½å°æå ¥çä½ç½®ï¼è该ä½ç½®çä¸ä¸æ¡æ¯ id = 20ãage = 39 çè®°å½ï¼æ£å¥½è¯¥è®°å½çäºçº§ç´¢å¼ä¸æé´ééï¼æ以è¿æ¡æå ¥è¯å¥ä¼è¢«é»å¡ï¼æ æ³æå ¥æåï¼è¿æ ·å°±é¿å 幻读ç°è±¡çåçã
æ以ï¼ä¸ºäºé¿å 幻读ç°è±¡çåçï¼å°±éè¦å¨äºçº§ç´¢å¼ç´¢å¼ä¸å èå´ (22, 39) çé´ééã
éå¯ä¸ç´¢å¼èå´æ¥è¯¢
éå¯ä¸ç´¢å¼å主é®ç´¢å¼çèå´æ¥è¯¢çå éä¹ææä¸åï¼ä¸åä¹å¤å¨äºéå¯ä¸ç´¢å¼èå´æ¥è¯¢ï¼ç´¢å¼ç next-key lock ä¸ä¼æéå为é´ééåè®°å½éçæ åµï¼ä¹å°±æ¯éå¯ä¸ç´¢å¼è¿è¡èå´æ¥è¯¢æ¶ï¼å¯¹äºçº§ç´¢å¼è®°å½å éé½æ¯å next-key éã
就带大家ç®ååæä¸ä¸ï¼äºå¡ A çè¿æ¡èå´æ¥è¯¢è¯å¥ï¼
sqlå¤å¶ä»£ç mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age >= 22 for update;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 10 | 山治 | 22 |
| 20 | é¦å
æ¯ | 39 |
+----+-----------+-----+
2 rows in set (0.01 sec)
äºå¡ A çå éååï¼
- æå¼å§è¦æ¾ç第ä¸è¡æ¯ age = 22ï¼è½ç¶èå´æ¥è¯¢è¯å¥å å«çå¼æ¥è¯¢ï¼ä½æ¯è¿éä¸æ¯å¯ä¸ç´¢å¼èå´æ¥è¯¢ï¼æ以æ¯ä¸ä¼åçéåéçç°è±¡ï¼å æ¤å¯¹è¯¥äºçº§ç´¢å¼è®°å½å next-key éï¼èå´æ¯ (21, 22]ãåæ¶ï¼å¯¹ age = 22 è¿æ¡è®°å½ç主é®ç´¢å¼å è®°å½éï¼å³å¯¹ id = 10 è¿ä¸è¡è®°å½ç主é®ç´¢å¼å è®°å½éã
- ç±äºæ¯èå´æ¥è¯¢ï¼æ¥ç继ç»æ«æå·²ç»åå¨çäºçº§ç´¢å¼è®°å½ãæ«é¢ç第äºè¡æ¯ age = 39 çäºçº§ç´¢å¼è®°å½ï¼äºæ¯å¯¹è¯¥äºçº§ç´¢å¼è®°å½å next-key éï¼èå´æ¯ (22, 39]ï¼åæ¶ï¼å¯¹ age = 39 è¿æ¡è®°å½ç主é®ç´¢å¼å è®°å½éï¼å³å¯¹ id = 20 è¿ä¸è¡è®°å½ç主é®ç´¢å¼å è®°å½éã
- è½ç¶æ们çè§è¡¨ä¸æåä¸æ¡äºçº§ç´¢å¼è®°å½æ¯ age = 39 çè®°å½ï¼ä½æ¯å®é å¨ Innodb åå¨å¼æä¸ï¼ä¼ç¨ä¸ä¸ªç¹æ®çè®°å½æ¥æ è¯æåä¸æ¡è®°å½ï¼è¯¥ç¹æ®çè®°å½çååå« supremum pseudo-record ï¼æ以æ«æ第äºè¡çæ¶åï¼ä¹å°±æ«æå°äºè¿ä¸ªç¹æ®è®°å½çæ¶åï¼ä¼å¯¹è¯¥äºçº§ç´¢å¼è®°å½å çæ¯èå´ä¸º (39, +â] ç next-key éã
- åæ¢æ¥è¯¢
å¯ä»¥çå°ï¼äºå¡ A 对主é®ç´¢å¼åäºçº§ç´¢å¼é½å äº X åçéï¼
- 主é®ç´¢å¼ï¼id åï¼ï¼å¨ id = 10 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºè®°å½éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ id = 10 çè¿ä¸è¡è®°å½ãå¨ id = 20 è¿æ¡è®°å½ç主é®ç´¢å¼ä¸ï¼å äºè®°å½éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ id = 20 çè¿ä¸è¡è®°å½ã
- äºçº§ç´¢å¼ï¼age åï¼ï¼å¨ age = 22 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸ï¼å äºèå´ä¸º (21, 22] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ age = 22 çè¿ä¸äºæ°è®°å½ï¼ä¸è¿å¯¹äºæ¯å¦å¯ä»¥æå ¥ age = 21 å age = 22 çæ°è®°å½ï¼è¿éè¦çæ°è®°å½ç id å¼ï¼æäºæ åµæ¯å¯ä»¥æåæå ¥çï¼èä¸äºæ åµåæ æ³æå ¥ï¼å ·ä½åªäºæ åµï¼æ们åé¢ä¹è®²äºãå¨ age = 39 è¿æ¡è®°å½çäºçº§ç´¢å¼ä¸ï¼å äºèå´ä¸º (22, 39] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æ´æ°æè å é¤ age = 39 çè¿ä¸äºè®°å½ï¼ä¹æ æ³æå ¥ age å¼ä¸º 23ã24ã25ã...ã38 çè¿ä¸äºæ°è®°å½ãä¸è¿å¯¹äºæ¯å¦å¯ä»¥æå ¥ age = 22 å age = 39 çæ°è®°å½ï¼è¿éè¦çæ°è®°å½ç id å¼ï¼æäºæ åµæ¯å¯ä»¥æåæå ¥çï¼èä¸äºæ åµåæ æ³æå ¥ï¼å ·ä½åªäºæ åµï¼æ们åé¢ä¹è®²äºãå¨ç¹æ®çè®°å½ï¼supremum pseudo-recordï¼çäºçº§ç´¢å¼ä¸ï¼å äºèå´ä¸º (39, +â] ç next-key éï¼æå³çå ¶ä»äºå¡æ æ³æå ¥ age å¼å¤§äº 39 çè¿äºæ°è®°å½ã
没æå ç´¢å¼çæ¥è¯¢
åé¢çæ¡ä¾ï¼æ们çæ¥è¯¢è¯å¥é½æ使ç¨ç´¢å¼æ¥è¯¢ï¼ä¹å°±æ¯æ¥è¯¢è®°å½çæ¶åï¼æ¯éè¿ç´¢å¼æ«æçæ¹å¼æ¥è¯¢çï¼ç¶å对æ«æåºæ¥çè®°å½è¿è¡å éã
å¦æéå®è¯»æ¥è¯¢è¯å¥ï¼æ²¡æ使ç¨ç´¢å¼åä½ä¸ºæ¥è¯¢æ¡ä»¶ï¼æè æ¥è¯¢è¯å¥æ²¡æèµ°ç´¢å¼æ¥è¯¢ï¼å¯¼è´æ«ææ¯å ¨è¡¨æ«æãé£ä¹ï¼æ¯ä¸æ¡è®°å½çç´¢å¼ä¸é½ä¼å next-key éï¼è¿æ ·å°±ç¸å½äºéä½çå ¨è¡¨ï¼è¿æ¶å¦æå ¶ä»äºå¡å¯¹è¯¥è¡¨è¿è¡å¢ãå ãæ¹æä½çæ¶åï¼é½ä¼è¢«é»å¡ã
ä¸åªæ¯éå®è¯»æ¥è¯¢è¯å¥ä¸å ç´¢å¼æä¼å¯¼è´è¿ç§æ åµï¼update å delete è¯å¥å¦ææ¥è¯¢æ¡ä»¶ä¸å ç´¢å¼ï¼é£ä¹ç±äºæ«æçæ¹å¼æ¯å ¨è¡¨æ«æï¼äºæ¯å°±ä¼å¯¹æ¯ä¸æ¡è®°å½çç´¢å¼ä¸é½ä¼å next-key éï¼è¿æ ·å°±ç¸å½äºéä½çå ¨è¡¨ã
å æ¤ï¼å¨çº¿ä¸å¨æ§è¡ updateãdeleteãselect ... for update çå ·æå éæ§è´¨çè¯å¥ï¼ä¸å®è¦æ£æ¥è¯å¥æ¯å¦èµ°äºç´¢å¼ï¼å¦ææ¯å ¨è¡¨æ«æçè¯ï¼ä¼å¯¹æ¯ä¸ä¸ªç´¢å¼å next-key éï¼ç¸å½äºææ´ä¸ªè¡¨éä½äºï¼è¿æ¯æºä¸¥éçé®é¢ã
æ»ç»
è¿æ¬¡æ以 MySQL 8.0.26 çæ¬ï¼å¨å¯éå¤è¯»é离级å«ä¹ä¸ï¼åäºå 个å®éªï¼è®©å¤§å®¶äºè§£äºå¯ä¸ç´¢å¼åéå¯ä¸ç´¢å¼çè¡çº§éçå éè§åã
æè¿éæ»ç»ä¸ï¼ MySQL è¡çº§éçå éè§åã
å¯ä¸ç´¢å¼çå¼æ¥è¯¢ï¼
- å½æ¥è¯¢çè®°å½æ¯ãåå¨ãçï¼å¨ç´¢å¼æ ä¸å®ä½å°è¿ä¸æ¡è®°å½åï¼å°è¯¥è®°å½çç´¢å¼ä¸ç next-key lock ä¼éåæãè®°å½éãã
- å½æ¥è¯¢çè®°å½æ¯ãä¸åå¨ãçï¼åä¼å¨ç´¢å¼æ æ¾å°ç¬¬ä¸æ¡å¤§äºè¯¥æ¥è¯¢è®°å½çè®°å½ï¼ç¶åå°è¯¥è®°å½çç´¢å¼ä¸ç next-key lock ä¼éåæãé´ééãã
éå¯ä¸ç´¢å¼çå¼æ¥è¯¢ï¼
- å½æ¥è¯¢çè®°å½ãåå¨ãæ¶ï¼ç±äºä¸æ¯å¯ä¸ç´¢å¼ï¼æ以è¯å®åå¨ç´¢å¼å¼ç¸åçè®°å½ï¼äºæ¯éå¯ä¸ç´¢å¼çå¼æ¥è¯¢çè¿ç¨æ¯ä¸ä¸ªæ«æçè¿ç¨ï¼ç´å°æ«æå°ç¬¬ä¸ä¸ªä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½å°±åæ¢æ«æï¼ç¶åå¨æ«æçè¿ç¨ä¸ï¼å¯¹æ«æå°çäºçº§ç´¢å¼è®°å½å çæ¯ next-key éï¼è对äºç¬¬ä¸ä¸ªä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½ï¼è¯¥äºçº§ç´¢å¼ç next-key éä¼éåæé´ééãåæ¶ï¼å¨ç¬¦åæ¥è¯¢æ¡ä»¶çè®°å½ç主é®ç´¢å¼ä¸å è®°å½éã
- å½æ¥è¯¢çè®°å½ãä¸åå¨ãæ¶ï¼æ«æå°ç¬¬ä¸æ¡ä¸ç¬¦åæ¡ä»¶çäºçº§ç´¢å¼è®°å½ï¼è¯¥äºçº§ç´¢å¼ç next-key éä¼éåæé´ééãå 为ä¸åå¨æ»¡è¶³æ¥è¯¢æ¡ä»¶çè®°å½ï¼æ以ä¸ä¼å¯¹ä¸»é®ç´¢å¼å éã
éå¯ä¸ç´¢å¼å主é®ç´¢å¼çèå´æ¥è¯¢çå éè§åä¸åä¹å¤å¨äºï¼
- å¯ä¸ç´¢å¼å¨æ»¡è¶³ä¸äºæ¡ä»¶çæ¶åï¼ç´¢å¼ç next-key lock éå为é´ééæè è®°å½éã
- éå¯ä¸ç´¢å¼èå´æ¥è¯¢ï¼ç´¢å¼ç next-key lock ä¸ä¼éå为é´ééåè®°å½éã
è¿æä¸ä»¶å¾éè¦çäºæ ï¼å¨çº¿ä¸å¨æ§è¡ updateãdeleteãselect ... for update çå ·æå éæ§è´¨çè¯å¥ï¼ä¸å®è¦æ£æ¥è¯å¥æ¯å¦èµ°äºç´¢å¼ï¼å¦ææ¯å ¨è¡¨æ«æçè¯ï¼ä¼å¯¹æ¯ä¸ä¸ªç´¢å¼å next-key éï¼ç¸å½äºææ´ä¸ªè¡¨éä½äºï¼è¿æ¯æºä¸¥éçé®é¢ã
就说å°è¿å¦ï¼ æ们ä¸æ¬¡è§å¦ï¼
åæé¾æ¥ï¼https://juejin.cn/post/7165718793849667620