åå¨åé¢
æ°æ®åºæ¬è´¨ä¸æ¯ä¸ç§
å
±äº«èµæº
ï¼å æ¤å¨æ大ç¨åº¦æä¾å¹¶å访é®æ§è½çåæ¶ï¼ä»éè¦ç¡®ä¿æ¯ä¸ªç¨æ·è½ä»¥ä¸è´çæ¹å¼è¯»ååä¿®æ¹æ°æ®ãéæºå¶
ï¼Lockingï¼
å°±æ¯è§£å³è¿ç±»é®é¢çæ好æ¦å¨ã
é¦å æ°å»ºè¡¨
test
ï¼å ¶ä¸
id
为主é®ï¼
name
ä¸ºè¾ å©ç´¢å¼ï¼
address
为å¯ä¸ç´¢å¼ã
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(11) NOT NULL,
`address` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idex_unique` (`address`),
KEY `idx_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
INSERT æ¹æ³ä¸çè¡é
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicGcq5yakRnY3BTY3EjMqhWc1YHexQHewcWM5xWN5gjYlJ2Mj9CXldmchx2Lc52YucWbpFmbpNnLxc3dvw1LcpDc0RHaiojIsJye.jpg)
å¯è§ï¼å¦æ两个äºå¡å å对主é®ç¸åçè¡è®°å½æ§è¡
INSERT
æä½ï¼å 为äºå¡
A
å æ¿å°äºè¡éï¼äºå¡
B
åªè½çå¾ ç´å°äºå¡
A
æ交åè¡é被éæ¾ãåçï¼å¦æé对å¯ä¸ç´¢å¼å段
address
è¿è¡æå ¥æä½ï¼ä¹éè¦è·åè¡éï¼å¾å主é®æå ¥è¿ç¨ç±»ä¼¼ï¼ä¸åéå¤ã
ä½æ¯ï¼å¦æ两个äºå¡é½éå¯¹è¾ å©ç´¢å¼å段 name è¿è¡æå ¥ï¼ä¸éè¦çå¾ è·åéï¼å ä¸ºè¾ å©ç´¢å¼å段å³ä½¿å¼ç¸åï¼å¨æ°æ®åºä¸ä¹æ¯æä½ä¸åçè®°å½è¡ï¼ä¸ä¼å²çªã
Update
æ¹æ³ä¸
Insert
æ¹æ³ç»æ类似ã
SELECT FOR UPDATE ä¸ç表éä¸è¡é
äºå¡
A
SELECT FOR UPDATE
è¯å¥ä¼æ¿å°è¡¨
test
ç
Table Lock
ï¼æ¤æ¶äºå¡
B
å»æ§è¡æå ¥æä½ä¼é»å¡ï¼ç´å°äºå¡
A
æ交éæ¾è¡¨éåï¼äºå¡
B
æè½è·å对åºçè¡éæ§è¡æå ¥æä½ã
ä½æ¯å¦æäºå¡ A ç SELECT FOR UPDATE è¯å¥ç´§è· WHERE id = 1 çè¯ï¼é£ä¹è¿æ¡è¯å¥åªä¼è·åè¡éï¼ä¸ä¼æ¯è¡¨éï¼æ¤æ¶ä¸é»å¡äºå¡ B 对äºå ¶ä»ä¸»é®çä¿®æ¹æä½
è¾ å©ç´¢å¼ä¸çé´éé
å çä¸
test
表ä¸çæ°æ®æ åµï¼
mysql> select * from test;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 3 | 1 | 3 |
| 6 | 1 | 2 |
| 7 | 2 | 4 |
| 8 | 10 | 5 |
+----+------+---------+
4 rows in set (0.00 sec)
é´ééå¯ä»¥è¯´æ¯è¡éçä¸ç§ï¼ä¸åçæ¯å®éä½çæ¯ä¸ä¸ªèå´å çè®°å½ï¼ä½ç¨æ¯é¿å 幻读ï¼å³åºé´æ°æ®æ¡ç®ççªç¶å¢åã解å³åæ³ä¸»è¦æ¯ï¼
- é²æ¢é´éå ææ°æ°æ®è¢«æå ¥ï¼å æ¤å«é´éé
- é²æ¢å·²åå¨çæ°æ®ï¼å¨æ´æ°æä½åæ为é´éå
çæ°æ®ï¼ä¾å¦æ´æ°
çid = 7
å段为name
ï¼é£ä¹1
çæ¡æ°å°±ä»name = 1
å为2
ï¼3
InnoDB
èªå¨ä½¿ç¨é´ééçæ¡ä»¶ä¸ºï¼
-
é离级å«ï¼è¿æ¯Repeatable Read
çé»è®¤å·¥ä½çº§å«MySQL
- æ£ç´¢æ¡ä»¶å¿ é¡»æç´¢å¼ï¼æ²¡æç´¢å¼çè¯ä¼èµ°å ¨è¡¨æ«æï¼é£æ ·ä¼éå®æ´å¼ 表ææçè®°å½ï¼
å½
InnoDB
æ«æç´¢å¼è®°å½çæ¶åï¼ä¼é¦å 对éä¸çç´¢å¼è¡è®°å½å ä¸è¡éï¼å对索å¼è®°å½ä¸¤è¾¹çé´éï¼åå·¦æ«ææ«å°ç¬¬ä¸ä¸ªæ¯ç»å®åæ°å°çå¼ï¼ åå³æ«ææ«æå°ç¬¬ä¸ä¸ªæ¯ç»å®åæ°å¤§çå¼ï¼ 以æ¤æ建ä¸ä¸ªåºé´ï¼å ä¸é´ééãå¦æä¸ä¸ªé´é被äºå¡
A
å äºéï¼äºå¡
B
æ¯ä¸è½å¨è¿ä¸ªé´éæå ¥è®°å½çã
æ们è¿éæ说ç âé´ééâ å ¶å®ä¸æ¯ GAP LOCKï¼èæ¯ RECORD LOCK + GAP LOCKï¼InnoDB ä¸ç§°ä¹ä¸º NEXT_KEY LOCK
ä¸é¢ç个ä¾åï¼æ们建表æ¶æå®
name
åä¸ºè¾ å©ç´¢å¼ï¼ç®åè¿åçåå¼æ [1,2,10]ãé´éèå´æ (-â, 1]ã[1,1]ã[1,2]ã[2,10]ã[10, +â)
Round 1:
- äºå¡ A SELECT ⦠WHERE name = 1 FOR UPDATE;
- 对 (-â, 2) å¢å é´éé
- äºå¡ B INSERT ⦠name = 1 é»å¡
- äºå¡ B INSERT ⦠name = -100 é»å¡
- äºå¡ B INSERT ⦠name = 2 æå
- äºå¡ B INSERT ⦠name = 3 æå
Round 2:
- äºå¡ A SELECT ⦠WHERE name = 2 FOR UPDATE;
- 对 [1, 10) å¢å é´éé
- äºå¡ B INSERT ⦠name = 1 é»å¡
- äºå¡ B INSERT ⦠name = 9 é»å¡
- äºå¡ B INSERT ⦠name = 10 æå
- äºå¡ B INSERT ⦠name = 0 æå
Round 3:
- äºå¡ A SELECT ⦠WHERE name <= 2 FOR UPDATE;
- 对 (-â, +â) å¢å é´éé
- äºå¡ B INSERT ⦠name = 3 é»å¡
- äºå¡ B INSERT ⦠name = 300 é»å¡
- äºå¡ B INSERT ⦠name = -300 é»å¡
InnoDB éæºå¶æ»ç»
欢è¿å ³æ³¨æçå ¬ä¼å·ï¼
åèèµæ
- ãMySQL ææ¯å å¹ InnoDB åå¨å¼æã第äºç å§æ¿å°§è
- About MySQL InnoDBâs Lock
许å¯åè®®
- æ¬æéµå®åä½å ±äº« CC BY-NC-SA 3.0åè®®