æ¬æ主è¦è®²è§£MySQLä¸åºç°æ»éçåºç¨æ¡ä¾ï¼ä»¥åç¸å ³çä¸å¡åºæ¯ï¼ä¸ä¼çº¯è®²ç论ï¼å¸æ对è¿åæå ´è¶£çæåå¯ä»¥ææ帮å©ã
ä¸ãä»ä¹æ¯æ»é
å¤ä¸ªçº¿ç¨å¨è®¿é®æäºèµæºçæ¶åï¼éè¦çå¾ å¯¹æ¹éæ¾å½¼æ¤æéèµæºï¼èè¿å ¥äºçå¾ äºæ¥çç¶æã
éä¿ä¸äºæ¥è¯´ï¼A线ç¨ææBéï¼ç¶åæ³è¦è®¿é®Aéï¼æ¤æ¶B线ç¨ææAéï¼æ³è¦è®¿é®Béï¼è¿ç§æ åµä¸å°±å®¹æåºç°æ»éã
äºãMySQLä¸éçç±»åæåªäºï¼
ä¸æ以ç¨æ·æ¶æ¯è¡¨æ¡ä¾æ¥è¿è¡è¯´æï¼
CREATE TABLE `t_user_message` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'åä¿¡æ¹id',
`object_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'æ¶ä¿¡æ¹id',
`relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'å
³èid',
`is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'æ¯å¦å·²è¯»ï¼0æªè¯»ï¼1已读ï¼',
`sid` int unsigned NOT NULL DEFAULT '0' COMMENT 'æ¶æ¯æ¡æ°',
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT 'ç¶æï¼0æ æ 1ææï¼',
`content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'æ¶æ¯å
容',
`type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'ç±»åï¼0ææ¬ï¼1è¯é³ï¼2å¾çï¼3è§é¢ï¼4表æ
ï¼5å享é¾æ¥ï¼',
`ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'æ©å±å段',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'å建æ¶é´',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'æ´æ°æ¶é´',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) USING BTREE COMMENT 'åä¿¡æ¹idç´¢å¼',
KEY `idx_object_id` (`object_id`) USING BTREE COMMENT 'æ¶ä¿¡æ¹idç´¢å¼',
KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT 'å
³èidç´¢å¼'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='ç¨æ·æ¶æ¯è¡¨';
æç §éçç²åº¦æ¥åºåï¼å¯ä»¥å为以ä¸ä¸¤ç§ï¼
- è¡éï¼åªä¼éä½ç¹å®çè¡è®°å½ï¼ä¾å¦ä¸è¾¹è¿æ¡sqlï¼
select * from t_user_message where user_id=1001 for update;
- 表éï¼ä¼ææ´ä¸ªè¡¨çæ°æ®ç»éä½ï¼æ§è½è¾å·®ï¼ä¾å¦ä¸è¾¹è¿æ¡sqlï¼
select * from t_user_message for update;
ä¸ãæå®éåå ±äº«éçåºå«
- å ±äº«éï¼å¨å¤ä¸ªäºå¡éé¢é½å¯ä»¥è¯»åå ±äº«éæéä½çå 容ã
- æå®éï¼åªè½å¨ä¸ä¸ªäºå¡é对åæ ·çæ°æ®è¿è¡å éï¼å设Aäºå¡å¯¹æè¡æ°æ®å å ¥äºæå®éä¹åï¼å ¶ä»äºå¡å°±æ æ³å对该è¡è®°å½å å ¥æå®éã
åãå ³äºæå®éåå ±äº«éç使ç¨
çå°è¿éï¼ä½ å¯è½å¯¹å ±äº«éåæå®é并ä¸æ¯ç解å¾å¾å½»åºï¼é£ä¹å å«çæ¥ï¼æ们å ä»å®ææ¥å æ·±ä¸ä½ 对å®çç解ã
- æå®é
å¨Innodbåå¨å¼æä¸ï¼å¸¸è§çupdateï¼insertï¼deleteè¿äºsqlé½ä¼é»è®¤å å ¥ä¸æä»éï¼èæ们çselectè¯å¥å¦æ没æå å ¥ç¹æ®å ³é®åï¼ä¸è¾¹ä¼è®²æ¯ä»ä¹æ ·çç¹æ®å ³é®åï¼ ï¼æ¯ä¸ä¼å å ¥æä»éçã
å¦æselectè¯å¥å¸æå å ¥æå®éï¼é£ä¹å¯ä»¥å°è¯ä»¥ä¸æ¹å¼ï¼ä½¿ç¨ for update å ³é®åã
select * from t_user_message for update;
- å ±äº«é
å¨æ£å¸¸çselectè¯å¥ä¸ï¼æ¯ä¸ä¼æå éçï¼ä¾å¦ä¸è¾¹è¿æ¡sqlï¼
select * from t_user_message;
è¿æ¡sqlå¨innodbä¸ï¼é»è®¤æ¯ä¸ä¼é表ï¼ä¹ä¸ä¼éè¡è®°å½ãå¦æä½ å¸æå ä¸ä¸æå ±äº«éï¼é£ä¹å¯ä»¥å°è¯ä»¥ä¸çè¿ç§åæ³ï¼ä½¿ç¨ lock in share mode å ³é®åã
select * from t_user_message lock in share mode;
äºãlock in share mode å for update使ç¨èµ·æ¥æä»ä¹åºå«ï¼
æ¥ççè¿ä¸ªæ¡ä¾ï¼æ们åå¤äºä¸¤ä¸ªMySQLçä¼è¯çªå£ã
1.lock in share mode æµè¯
å æ¥çä¼è¯Aï¼ä¼è¯Aä¸ï¼å ³éäºèªå¨æ交åè½ï¼ç¶åæ§è¡è¿ä¸ªlock in share modeçéï¼æ¤æ¶å®ä½¿ç¨äºå ±äº«ééä½äºå ¨è¡¨çå 容ã
åæ¥çä¼è¯Bï¼ä¼è¯Bä¸ä¹æ¯ç¸åçï¼å ³éèªå¨æ交åï¼æ§è¡lock in share modeçå ±äº«éï¼åç°ä¾ç¶å¯ä»¥æ£å¸¸æ¥è¯¢ï¼æ²¡æå µå¡è¡ä¸ºã
è¿æ¶åæ们å°ä¼è¯Bçå½åäºå¡å æ交ï¼ç¶åå¨ä¼è¯Bä¸ç»§ç»æ§è¡ä¸æ¡updateè¯å¥ï¼éäºå¡ç¶æä¸ï¼ ï¼è¦ç¥éupdateæ¯é»è®¤å¸¦äºæå®éçï¼æ¤æ¶å 为æ们çä¼è¯A没æcommitï¼æ以ä¼è¯Bçè¿æ¡updateæä½ä¼è¿å ¥å µå¡çç¶æï¼å¦ä¸å¾ï¼
åªæå½ä¼è¯Açäºå¡æ§è¡å®æ¯äºï¼å°lock in share modeçéç»éæ¾æï¼ä¼è¯Bæä¼ç»§ç»æ§è¡ã
2.for updateæµè¯
让æ们æ¥çç for update å éçå½±åï¼ä¼è¯Aå ³éäºèªå¨æ交ï¼ç¶åæ§è¡äºä¸æ¡for updateçsqlï¼ä½æ¯æ²¡æcommitï¼æ¤æ¶æ们çä¼è¯Bä¹å¼å§äºåæ ·çæ¥éª¤ï¼ä½æ¯å´å¡ä½äºã
åå¦äºå¡Aä¸ç´é½ä¸æ交çè¯ï¼é£ä¹äºå¡Bæç»ä¼æ¥åºä»¥ä¸å¼å¸¸ï¼
[æ¥è¯¢2ä¸åçé误] Lock wait timeout exceeded; try restarting transaction
åæ¥ççfor updateéä½çæ°æ®ï¼å¯¹äºå ¶ä»ä¼è¯çåæä½æä½å½±åã
å¦ä¸å¾æ示ï¼æ们çä¼è¯Aä¾æ§æ²¡æcommitï¼ä½æ¯æ¤æ¶ä¼è¯Bä¸å°è¯æ§è¡ä¸æ¬¡updateæä½ï¼ç±äºupdateé»è®¤å¸¦äºæä»éï¼è¿æ¡sqlä¼é表ï¼æ以åä¼è¯Aä¸çfor updateéåºç°äºå²çªï¼å¯¼è´ä¼è¯Bä¸ç´å¤äºå µå¡ç¶æã
3.å°æ»ç»
éè¿ä¸è¿°çå 个æµè¯ï¼å¤§å®¶åºè¯¥ä¹ææ·±å»çä½ä¼äºï¼é£ä¹æ们就æ¥è¿è¡ä¸æ»ç»ï¼å æ·±ä¸å°è±¡ã
çå°è¿éï¼ä½ åºè¯¥å¯¹lock in share mode å for update æä¸å®äºè§£äºå§ï¼ä½æ¯è¿ä¸¤ç§éï¼å äºè§£ç论ï¼å ¶å®è¿æ¯ä¸å¤çï¼éè¦æå®ææè½è®©ä½ 对å®ç解æ´å æ·±å»ï¼æ¥çä¸è¾¹çæ¡ä¾ã
å ãlock in share mode使ç¨ä¸å½ï¼å¯¼è´æ»é
æ¥çä¸è¾¹çè¿ä¸ªä¸å¡åºæ¯ï¼å设æ们æä¸ä¸ªè´¦æ·è¡¨ï¼è¡¨ç»æå¦ä¸ï¼
CREATE TABLE `t_account` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`coin` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
ç¶åå¨ä¸å¡æä½ä¸ï¼æ们çè´¦æ·æ£æ¬¾åå¢æ¬¾é»è¾ä¸ç设计å¦ä¸ï¼
//å¼å¯ä¸ä¸ªäºå¡æä½
set autocommit=0;
begin;
//å¦æè´¦æ·åå¨ï¼æè¿è¡updateï¼å¦æè´¦æ·ä¸åå¨ï¼å°±å¾å
insert
select * from t_account where user_id=111 lock in share mode;
//è¿éæ们å设账æ·æ¯åå¨çï¼é£ä¹å°±ç´æ¥éæ©æ款å
¥è´¦
update t_account set coin=coin+100 where user_id=111;
//è®°å½å°è´¦æ·æµæ°´è¡¨ä¸
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');
commit;
è¿é为äºä¿è¯å°è´¦æ·æµæ°´è®°å½åæ款两个æä½ä¿è¯ä¸è´æ§ï¼å¾å å ¥ä¸ä¸ªæ¬å°äºå¡å»ä¿®é¥°ãä½æ¯è¿æ®µä»£ç ä¸ä½¿ç¨äºä¸ä¸ªlock in share modeçå ³é®åï¼è¿ä¸ªå ³é®åæ¯ä¸ºäºé¿å å¨å¹¶åçæ åµä¸ï¼å¯¹è´¦æ·è®°å½è¿è¡è¯»çè¿ç¨ä¸ï¼æå ¶ä»å°æ¹å¯¹è´¦æ·çcoinå¼è¿è¡åçä¿®æ¹ã
ä¹æ以å¯è½ä¼æå ¶ä»å°æ¹å¯¹coinå¼è¿è¡é¢å¤çåæä½ï¼ä¸»è¦åå æ¯å 为系ç»ä¸å¡ä¸çèæ§ä»£ç åå¨ï¼éå¤é è½®åï¼æ¬æ¥Aæå¡ä¸åªæä¸å¤å°æ¹å¯¹è´¦æ·è¿è¡ä¿®æ¹æä½ï¼ç»æå¨Bæå¡éï¼ä¹æä¸æ®µç±»ä¼¼ç代ç ä¿®æ¹ï¼ç´æ¥æä½äºæ°æ®åºè¡¨ï¼ä½æ¯ç±äºä¸å¥½å»è°æ´é£ä¸ªæå¡ç代ç ï¼æ以ææ¶åªè½ç¨ lock in share mode æä½å»å éãç¸æ¯äºfor updateéæ¥è¯´ï¼ä½¿ç¨lock in share modeå éï¼å¯¹äºè¯»çå½±åä¸å¤§ï¼æ以æ©æ设计çæ¶åï¼æ²¡æèèé£ä¹å¤ï¼å°±ç´æ¥ç¨äºå®ä¸çº¿ã并ä¸ä¸çº¿ä¹å并å度ä¸é«ï¼ææ¶å°±æ²¡æåç°ä»ä¹é®é¢ã
çå°è¿éï¼ä½ å¯è½æè§ä¼¼ä¹è¿ç§è®¾è®¡æ²¡æä»ä¹é®é¢ï¼é£ä¹æ们æ¥ççä¸è¾¹çè¿ä¸ªåºæ¯ã
éç并å度çå¢å ï¼æ们å°ä¿®æ¹ä½é¢çè¿ä¸ªæä½ï¼å¨Aæå¡éé¢å°è£ æ为äºä¸ä¸ªæ¹æ³ï¼å¹¶ä¸ä¾å个å°æ¹è¿è¡è°ç¨ãä½æ¯æä¸å¤©ï¼åºç°äºè¿ä¹ä¸ä¸ªä¸å¡åºæ¯ï¼
å¨RocketMQçæ¶è´¹æ¹ï¼ä¼å¯¹ç¨æ·çè´¦æ·è¿è¡æ款æä½ãå¨è¿ä¸ªæ¶è´¹æ¹ç代ç ä¸ï¼åä¸ä¸ªuserIdçæ¶æ¯ä¼æ许å¤æ¡ï¼èä¸æ¯åä¸æ¶å»ç大é并åæ¶è´¹ï¼è¿å°±æå³çï¼åä¸æ¶å»ä¼æ大éç请æ±è°ç¨è¿ä¸ªæ款çæä½ï¼èä¸æ¯å¹¶åï¼åä¸ä¸ªuserIdãé£ä¹è¿ç§æ åµä¸ï¼æ们ç lock in share modeä¼åçä»ä¹æ ·çæ åµå¢ââæ»éã
æ¥çä¸å¾ï¼
ç±äºæ们ç线ç¨Aææäºéï¼çº¿ç¨Bä¹ææäºéï¼ä½æ¯å®ä»¬æ¥ä¸æ¥çupdateæä½ï¼é½æ¯å¾ç对æ¹å°å ±äº«ééæ¾åæå¯ä»¥ç»§ç»æ§è¡ï¼æ以就åçäºæ»éçåºæ¯ã
ä¸ãå¦ä½è§£å³ä¸è¿°çlock in share modeæ»é
é£ä¹æ们å¦ä½å´é¿å ä¸è¾¹çåºæ¯åçå¢ï¼è¿éæç»åºä»¥ä¸ä¸¤ç§æè·¯ã
1.ä¸ç¨éï¼æåäºå¡é离级å«ä¸ºè¯»å·²æ交
//å¼å¯ä¸ä¸ªäºå¡æä½
set autocommit=0;
//å¦æè´¦æ·åå¨ï¼æè¿è¡updateï¼å¦æè´¦æ·ä¸åå¨ï¼å°±å¾å
insert
select * from t_account where user_id=111;
//è¿éæ们å设账æ·æ¯åå¨çï¼é£ä¹å°±ç´æ¥éæ©æ款å
¥è´¦
update t_account set coin=coin+100,version=version+1 where user_id=111;
//è®°å½å°è´¦æ·æµæ°´è¡¨ä¸
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');
commit;
2.å»æ使ç¨lock in share modeï¼ä½¿ç¨ä¹è§é
ä¾å¦å å ¥ä¸ä¸ªversionå段ï¼é£ä¹æ们å¨æ§è¡è´¦æ·æ£æ¬¾çæ¶åï¼å å ¥versionçå¤æãä¾å¦ï¼
//å¼å¯ä¸ä¸ªäºå¡æä½
set autocommit=0;
//å¦æè´¦æ·åå¨ï¼æè¿è¡updateï¼å¦æè´¦æ·ä¸åå¨ï¼å°±å¾å
insert
select * from t_account where user_id=111 and version=#{version};
//è¿éæ们å设账æ·æ¯åå¨çï¼é£ä¹å°±ç´æ¥éæ©æ款å
¥è´¦
update t_account set coin=coin+100,version=version+1 where user_id=111 and version=#{version};
//è®°å½å°è´¦æ·æµæ°´è¡¨ä¸
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');
commit;
è¿éè¦æ³¨æï¼å½åæ¶ä¸¤ä¸ªä¼è¯é对åä¸è¡æ°æ®æ§è¡ä¸è¿°æ´æ°æä½çæ¶åï¼å¯è½ä¼å¯¼è´åä¸è¡çè®°å½è¢«éï¼æ以æ们å¨è¿è¡updateçæ¶åï¼å¯ä»¥ç¨ä¸ä¸ªversionå段å»ç®¡çãä½æ¯è¿ç§è®¾è®¡ï¼å¯è½ä¼å¯¼è´ä¸æ¬¡æ´æ°å¤±è´¥ï¼éè¦è¿è¡éè¯ï¼å æ¤å¹¶åéé«çæ åµä¸ï¼å®¹æ对MySQLé æè¾å¤§çååã
3.å¼å ¥åå¸å¼é
ç´æ¥å¨ä¸å¡å±å¼å ¥ä¸æåå¸å¼éï¼è¿ç§æè·¯æ¯è¾æ´åï¼ä½æ¯ç¡®å®ææã
å ¶å®åªè¦æ们çselectç±»åçsqlä¸è¿è¡æ¾ç¤ºå éï¼å°±æå¯è½ä¼ææ»éæ åµåçï¼æ以建议大家使ç¨çæ¶åè°¨æ ã
å «ãè¡éçå ç§ç±»å
- Record Lockï¼è®°å½éï¼ï¼å个è¡è®°å½ä¸çéãè¿ä¸ªä¹æ¯æ们æ¥å¸¸è®¤ä¸ºçè¡éã
- Gap Lockï¼é´ééï¼ï¼é´ééï¼éå®ä¸ä¸ªèå´ï¼ä½ä¸å æ¬è®°å½æ¬èº«ï¼åªä¸è¿å®çéç²åº¦æ¯è®°å½éçéæ´è¡æ´å¤§ä¸äºï¼ä»æ¯éä½äºæ个èå´å çå¤ä¸ªè¡ï¼å æ¬æ ¹æ¬ä¸åå¨çæ°æ®ï¼ãGAPéçç®çï¼æ¯ä¸ºäºé²æ¢åä¸äºå¡ç两次å½å读ï¼åºç°å¹»è¯»çæ åµã该éåªä¼å¨é离级å«æ¯RRæè 以ä¸ç级å«å åå¨ãé´ééçç®çæ¯ä¸ºäºè®©å ¶ä»äºå¡æ æ³å¨é´éä¸æ°å¢æ°æ®ã
- Next-Key Lockï¼ä¸´é®éï¼ï¼å®æ¯è®°å½éåé´ééçç»åï¼éå®ä¸ä¸ªèå´ï¼å¹¶ä¸éå®è®°å½æ¬èº«ã对äºè¡çæ¥è¯¢ï¼é½æ¯éç¨è¯¥æ¹æ³ï¼ä¸»è¦ç®çæ¯è§£å³å¹»è¯»çé®é¢ãnext-keyéæ¯InnoDBé»è®¤çéï¼è¯¥éä¹åªä¼å¨é离级å«æ¯RRæè 以ä¸ç级å«å åå¨ã
ä¹ãè¡éçäºæ æ¡ä¾
æ¶æ¯æ°æ®æ´æ°è®¾è®¡ä¸å½ï¼å¯¼è´åºç°Record Lockæ»éã
è¿éæ们éè¦å äºè§£ä¸æ¶æ¯è®°å½è¡¨çç»æï¼
CREATE TABLE `t_user_message` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'åä¿¡æ¹id',
`object_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'æ¶ä¿¡æ¹id',
`relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT 'å
³èid',
`is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'æ¯å¦å·²è¯»ï¼0æªè¯»ï¼1已读ï¼',
`sid` int unsigned NOT NULL DEFAULT '0' COMMENT 'æ¶æ¯æ¡æ°',
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT 'ç¶æï¼0æªå®¡æ ¸ 1å®¡æ ¸å¤±è´¥ 2å®¡æ ¸éè¿ï¼',
`content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'æ¶æ¯å
容',
`type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'ç±»åï¼0ææ¬ï¼1è¯é³ï¼2å¾çï¼3è§é¢ï¼4表æ
ï¼5å享é¾æ¥ï¼',
`ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'æ©å±å段',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'å建æ¶é´',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'æ´æ°æ¶é´',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) USING BTREE COMMENT 'åä¿¡æ¹idç´¢å¼',
KEY `idx_object_id` (`object_id`) USING BTREE COMMENT 'æ¶ä¿¡æ¹idç´¢å¼',
KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT 'å
³èidç´¢å¼'
) ENGINE=InnoDB AUTO_INCREMENT=100015 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='ç¨æ·æ¶æ¯è¡¨';
å设æ们çä¼è¯Aæ§è¡äºä»¥ä¸äºå¡æä½ï¼
START TRANSACTION;
//æ´æ°ç¨æ·çæ¶æ¯ç¶æï¼ä»æªè¯»å为已读
update t_user_message set is_read=1 where user_id=1003 and object_id=1004;
//...ä¸é´æäºå«çä¸å¡æä½
update t_user_message set is_read=1 where user_id=1001 and object_id=1002;
commit;
èæ¤æ¶æ们çä¼è¯Bå¨æ§è¡ä¸ä¸ªå¼æ¥çæ¶æ¯æ¯å¦åæ³çæ£æµå·¥ä½ï¼å ·ä½æä½å¦ä¸ï¼
set autocommit=0;
START TRANSACTION;
//å®æ¶ä»»å¡æ´æ°ç¨æ·çæ¶æ¯å®¡æ ¸ç¶æï¼ä»æªå®¡æ ¸åä¸ºå®¡æ ¸éè¿
update t_user_message set status=2 where user_id=1001 and object_id=1002;
//...ä¸é´æäºå«çä¸å¡æä½
update t_user_message set status=2 where user_id=1003 and object_id=1004;
commit;
è¿ä¸¤ä¸ªäºå¡å¦æ并åæ§è¡ï¼å¹¶å度é«çæ åµä¸ï¼å¯è½ä¼åºç°æ»éæ åµï¼æ»é产ççæ¥éª¤å¦ä¸å¾æ示ï¼
ä¸è¬éå°è¿ç±»æ åµï¼æ们é½ä¼æ¨èå¨è¿è¡æ´æ°çæ¶åï¼å°½å¯è½çé¿å æ»éæ¡ä»¶åçï¼ä¾å¦è°æ´sqlçæ§è¡é¡ºåºãä¾å¦åæ´ä¸ºå¦ä¸æä½ï¼
å¦å¤ï¼è°æ´é¡ºåºåï¼å°½éå°æ¬å°äºå¡çé¢ç²åº¦æ§å¶å°æå°ï¼åå°å 为å éå µå¡å¸¦æ¥çæ§è½é®é¢ã
åãé´ééå µå¡æ¡ä¾åæ
é¦å æ们è¦å°å½åä¼è¯çäºå¡é离级å«è®¾ç½®ä¸ºå¯éå¤è¯»ï¼
set SESSION transaction ISOLATION LEVEL REPEATABLE READ;
å¦æä½ æ³ç¡®è®¤å½åçä¼è¯çäºå¡é离级å«ï¼é£ä¹å¯ä»¥ä½¿ç¨ä»¥ä¸å½ä»¤å»æ¥è¯¢ï¼
SELECT @@transaction_isolation; ï¼mysql8.0è¯æ³ï¼
SELECT @@tx_isolation; ï¼mysql5.7è¯æ³ï¼
è¿æ¯é对æ们çæ¶æ¯è¡¨t_user_messageï¼å¨æäºé«å¹¶ååºæ¯ä¸ï¼å¦æ使ç¨å¯éå¤è¯»çè¯ï¼å°¤å ¶æ¯äºå¡åºæ¯ä¸ï¼åºç°æ»éçæ¦çä¼å 大ãä¾å¦ä¸è¾¹è¿ä¸ªåºæ¯ï¼
äºå¡1ä¸ï¼å¯¹æ¶æ¯è¡¨çå¯è¯»ç¶æè¿è¡ä¿®æ¹ï¼ä¿®æ¹çæ¯è®°å½è¡¨ä¸çå3æ¡æ°æ®ï¼ç±äºæ¯å¯éå¤è¯»ï¼ä»¥åéå¯ä¸ç´¢å¼user_idåobject_idæ以è¿éä¼éä½çæ¯(0,100011]è¿ä¸ªåºé´çidè®°å½ï¼ä¹å°±æ¯è¯´åªè¦æ们æ´æ°çè¡æ¯è¶ è¿äº100011 idçé½æ²¡é®é¢ã
ä½æ¯å设æ¤æ¶æ个æå ¥è¯·æ±ï¼æç®å¾100009ä¹ååå ¥ä¸æ¡è®°å½çè¯ï¼å°±ä¼åºç°é´ééå µå¡çé®é¢ï¼ä¾å¦ä¸å¾æ示ï¼
产çé´ééçåå ï¼
- 使ç¨äºupdateï¼deleteï¼selecct... for updateç¸å ³æä½ï¼
- 使ç¨äºå¯éå¤è¯»çé离级å«ï¼
- å¨æ§è¡update/delete/select ... for updateæä½ä¹åï¼å¨å¯¹åºçé´éä¸æå ¥äºæ°çæ°æ®ï¼æ³¨ææ¯insertäºæ°çæ°æ®æä¼æé´ééé®é¢äº§ç)ã
åä¸ãMySQLä¸çæ»éæ£æµ
å¨mysql5.7ãmysql5.8ç5ç³»çæ¬ä¸ï¼
æ¥çæ»é代ç æ¯ï¼
select * from information_schema.innodb_locks;
æ¥ççå¾ éç代ç ï¼
select * from information_schema.innodb_lock_waits
ä½æ¯è¦æ³¨æï¼å¨mysql 8.0ä¸æ¥çæ»é代ç åäºï¼å¦æ继ç»ç¨5.7ç代ç ä¼æ示æ¥éã
Unknown table âINNODB_LOCKSâ in information_schema
æ以å¨8.0使ç¨ä»¥ä¸ä»£ç ã
æ¥çæ»éï¼
select * from performance_schema.data_locks;
æ¥çæ»éçå¾ æ¶é´ï¼
select * from performance_schema.data_lock_waits;
ä½è 丨Danny idea
æ¥æºä¸¨å ¬ä¼å·ï¼Ideaçææ¯å享ï¼IDï¼it_learn_ideaï¼
dbaplus社群欢è¿å¹¿å¤§ææ¯äººåæ稿ï¼æ稿é®ç®±ï¼[email protected]
å ³äºæ们
dbaplus社群æ¯å´ç»DatabaseãBigDataãAIOpsçä¼ä¸çº§ä¸ä¸ç¤¾ç¾¤ãèµæ·±å¤§åãææ¯å¹²è´§ï¼æ¯å¤©ç²¾åååæç« æ¨éï¼æ¯å¨çº¿ä¸ææ¯å享ï¼æ¯æ线ä¸ææ¯æ²é¾ï¼æ¯å£åº¦Gdevops&DAMSè¡ä¸å¤§ä¼ã
å ³æ³¨å ¬ä¼å·ãdbaplus社群ãï¼è·åæ´å¤ååææ¯æç« åç²¾éå·¥å ·ä¸è½½