æ¬æç¼èµ·äºä¸ä½è¯»è çæé®ï¼æå ¥ä¸æ¡è®°å½ï¼å¯¼è´å¯ä¸ç´¢å¼å²çªï¼ä¸ºä»ä¹ä¼å¯¹ä¸»é®ç supremum è®°å½å next-key æä»éï¼
1. åå¤å·¥ä½
å建æµè¯è¡¨ï¼
CREATE TABLE `t6` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`i1` int unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
æå ¥æµè¯æ°æ®ï¼
INSERT INTO `t6`(i1) VALUES
(1001), (1002), (1003),
(1004), (1005), (1006);
设置äºå¡é离级å«ï¼
å¨ my.cnf ä¸ï¼æç³»ç»åé transaction_isolation 设置为 REPEATABLE-READã
2. é®é¢å¤ç°
æå ¥ä¸æ¡ä¼å¯¼è´å¯ä¸ç´¢å¼å²çªçè®°å½ï¼
BEGIN;
INSERT INTO `t6`(i1) VALUES(1001);
éè¿ BEGIN æ¾å¼å¼å¯äºå¡ï¼INSERT æ§è¡å®æä¹åï¼æ们å¯ä»¥éè¿ä»¥ä¸ SQL æ¥çå éæ åµï¼
SELECT
OBJECT_NAME, INDEX_NAME, LOCK_TYPE,
LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM `performance_schema`.`data_locks`;
ç»æå¦ä¸ï¼
å¯ä¸ç´¢å¼ï¼uniq_i1ï¼ï¼id = 1ï¼i1 = 1001 çè®°å½ï¼å next-key å ±äº«éã
主é®ç´¢å¼ï¼PRIMARYï¼ï¼supremum è®°å½ï¼å next-key æä»éã
3. åç½®ç¥è¯ç¹ï¼éå¼é
æå ¥è®°å½æ¶ï¼éå¼éæ¯ä¸ªæ¯è¾éè¦çæ¦å¿µï¼å®åå¨çç®çæ¯ï¼åå°æå ¥è®°å½æ¶ä¸å¿ è¦çå éï¼æå MySQL ç并åè½åã
æ们å æ¥çä¸ä¸éå¼éçå®ä¹ï¼
äºå¡ T è¦æå ¥ä¸æ¡è®°å½ Rï¼åªè¦å³å°æå ¥è®°å½çç®æ ä½ç½®æ²¡æè¢«å ¶å®äºå¡ä¸éï¼äºå¡ T å°±ä¸éè¦ç³è¯·å¯¹ç®æ ä½ç½®å éï¼å¯ä»¥ç´æ¥æå ¥è®°å½ã
äºå¡ T æ交ä¹åï¼å¦æå ¶å®äºå¡åºç°ä»¥ä¸ 2 ç§æ åµï¼é½å¿ 须帮å©äºå¡ T ç»è®°å½ R å ä¸æä»éï¼
- å ¶å®äºå¡æ§è¡ UPDATEãDELETE è¯å¥æ¶æ«æå°äºè®°å½ Rã
- å ¶å®äºå¡æå ¥çè®°å½å R åå¨ä¸»é®æå¯ä¸ç´¢å¼å²çªã
æªæ交äºå¡ T æå ¥çè®°å½ä¸ï¼è¿ç§éæ§çãç±å ¶å®äºå¡å¨éè¦æ¶å¸®å¿å建çéï¼å°±æ¯éå¼éã
éå¼éï¼å°±åç¥è¯çµè§å§éçç»çã没æ触碰å°å®æ¶ï¼çä¸è§ï¼å°±åä¸åå¨ä¸æ ·ï¼ä¸æ¦è§¦ç¢°å°ï¼å®å°±æ¾ç°åºæ¥äºã
éå¼éå¯è½åºç°äºå¤ç§åºæ¯ï¼æ们æ¥çç主é®ç´¢å¼ç 2 ç§éå¼éåºæ¯ï¼
åææ¡ä»¶ï¼
äºå¡ T1 æå ¥ä¸æ¡è®°å½ R1ï¼å³å°æå ¥ R1 çç®æ ä½ç½®æ²¡æè¢«å ¶å®äºå¡ä¸éï¼äºå¡ T1 å¯ä»¥ç´æ¥æå ¥ R1ã
åºæ¯ 1ï¼
äºå¡ T1 æå ¥ R1 ä¹åï¼æ交äºå¡ä¹åï¼äºå¡ T2 è¯å¾æå ¥ä¸æ¡è®°å½ R2ï¼ä¸»é®å段å¼å R1 ç¸åï¼ã
äºå¡ T2 ç» R2 寻æ¾æå ¥ä½ç½®çè¿ç¨ä¸ï¼å°±ä¼åç° R2 å R1 å²çªï¼å¹¶ä¸æå ¥ R1 çäºå¡ T1 è¿æ²¡ææ交ï¼è¿å°±è§¦åäº R1 çéå¼éé»è¾ã
äºå¡ T2 ä¼å¸®å© T1 ç» R1 å ä¸æä»éï¼ç¶åï¼å®èªå·±ä¼ç³è¯·å¯¹ R1 å å ±äº«éï¼å¹¶çå¾ äºå¡ T1 éæ¾ R1 ä¸çæä»éã
äºå¡ T1 éæ¾ R1 çéä¹åï¼å¦æäºå¡ T2 没æéçå¾ è¶ æ¶ï¼å®è·åå° R1 ä¸çéä¹åï¼å°±å¯ä»¥ç»§ç»è¿è¡ä¸»é®å²çªçåç»å¤çé»è¾äºã
åºæ¯ 2ï¼
äºå¡ T1 æå ¥ R1 ä¹åï¼æ交äºå¡ä¹åï¼äºå¡ T3 æ§è¡ UPDATE æ DELETE è¯å¥æ¶æ«æå°äº R1ï¼åç°æå ¥ R1 çäºå¡ T1 è¿æ²¡ææ交ï¼åæ ·è§¦åäº R1 çéå¼éé»è¾ã
äºå¡ T3 ä¼å¸®å© T1 ç» R1 å ä¸æä»éï¼ç¶åï¼å®èªå·±ä¼ç³è¯·å¯¹ R1 å æä»éï¼å¹¶çå¾ äºå¡ T1 éæ¾ R1 ä¸çæä»éã
äºå¡ T1 æ交并éæ¾ R1 çéä¹åï¼å¦æäºå¡ T3 没æéçå¾ è¶ æ¶ï¼å®è·åå° R1 ä¸çéä¹åï¼å°±å¯ä»¥ç»§ç»å¯¹ R1 è¿è¡ä¿®æ¹æå é¤æä½äºã
对éå¼éæäºå¤§æ¦äºè§£ä¹åï¼æ¥ä¸æ¥ï¼æ们åå°æ¬æ主é¢ï¼æ¥çç INSERT æ§è¡è¿ç¨ä¸çå éæµç¨ã
4. æµç¨åæ
æ们å æ¥çä¸ä¸ä¸»è¦å æ ï¼æ¥ä¸æ¥çæµç¨åæå´ç»è¿ä¸ªå æ è¿è¡ï¼
| > row_insert_for_mysql_using_ins_graph() storage/innobase/row/row0mysql.cc:1585
| + > row_ins_step(que_thr_t*) storage/innobase/row/row0ins.cc:3677
| + - > row_ins(ins_node_t*, que_thr_t*) storage/innobase/row/row0ins.cc:3559
| + - x > row_ins_index_entry_step(ins_node_t*, que_thr_t*) storage/innobase/row/row0ins.cc:3435
| + - x = > row_ins_index_entry() storage/innobase/row/row0ins.cc:3303
| + - x = | > row_ins_sec_index_entry() storage/innobase/row/row0ins.cc:3203
| + - x = | + > row_ins_sec_index_entry_low() storage/innobase/row/row0ins.cc:2926
| + - x = | + - > row_ins_scan_sec_index_for_duplicate() storage/innobase/row/row0ins.cc:1894
| + > row_mysql_handle_errors() storage/innobase/row/row0mysql.cc:701
è¿ä¸ªå æ çå ³é®æ¥éª¤æ 2 个ï¼
- row_ins_step()ï¼æå ¥è®°å½å°ä¸»é®ãå¯ä¸ç´¢å¼ã
- row_mysql_handle_errors()ï¼æå ¥å¤±è´¥ä¹åï¼è¿è¡é误å¤çã
4.1 æå ¥è®°å½å°ä¸»é®ãå¯ä¸ç´¢å¼
// storage/innobase/row/row0mysql.cc
static dberr_t row_insert_for_mysql_using_ins_graph(...) {
...
// 主è¦æé ç¨äºæ§è¡æå
¥æä½ç 2 个对象ï¼
// 1. ins_node_t 对象ï¼ä¿åå¨ prebuilt->ins_node ä¸
// 2. que_fork_t 对象ï¼ä¿åå¨ prebuilt->ins_graph ä¸
row_get_prebuilt_insert_row(prebuilt);
node = prebuilt->ins_node;
// æ server å±çè®°å½æ ¼å¼è½¬æ¢ä¸º InnoDB çè®°å½æ ¼å¼
row_mysql_convert_row_to_innobase(node->row, prebuilt, mysql_rec, &temp_heap);
...
// æ§è¡æå
¥æä½
row_ins_step(thr);
...
if (err != DB_SUCCESS) {
error_exit:
que_thr_stop_for_mysql(thr);
...
// é误å¤ç
auto was_lock_wait = row_mysql_handle_errors(&err, trx, thr, &savept);
...
return (err);
}
...
}
è¿ä¸ªæ¹æ³ç主è¦é»è¾ï¼
- è°ç¨ row_get_prebuilt_insert_row()ï¼æé å å«æå ¥æ°æ®ç ins_node_t 对象ãæ¥è¯¢æ§è¡å¾ que_fork_t 对象ï¼åå«ä¿åå° prebuilt ç ins_nodeãins_graph å±æ§ä¸ã
- æ server å±çè®°å½æ ¼å¼è½¬æ¢ä¸º InnoDB çè®°å½æ ¼å¼ã
- è°ç¨ row_ins_step()ï¼æå ¥è®°å½å°ä¸»é®ç´¢å¼ãäºçº§ç´¢å¼ï¼å å«å¯ä¸ç´¢å¼ãéå¯ä¸ç´¢å¼ï¼ã
// storage/innobase/row/row0ins.cc
que_thr_t *row_ins_step(que_thr_t *thr)
{
...
// éç½® node->trx_id_buf æéæåçå
ååºå
memset(node->trx_id_buf, 0, DATA_TRX_ID_LEN);
// æå½åäºå¡ ID æ·è´å° node->trx_id_buf æéæåçå
ååºå
trx_write_trx_id(node->trx_id_buf, trx->id);
if (node->state == INS_NODE_SET_IX_LOCK) {
...
// ç»è¡¨å ä¸æåé
err = lock_table(0, node->table, LOCK_IX, thr);
...
}
...
err = row_ins(node, thr);
...
return (thr);
}
row_ins_step() è°ç¨ row_ins() æå ¥è®°å½å°ä¸»é®ç´¢å¼ãäºçº§ç´¢å¼ã
// storage/innobase/row/row0ins.cc
[[nodiscard]] static dberr_t row_ins(...)
{
...
// è¿ä»£è¡¨ä¸çç´¢å¼ï¼æå
¥è®°å½å°ç´¢å¼ä¸
while (node->index != nullptr) {
// åªè¦ä¸æ¯å
¨æç´¢å¼
if (node->index->type != DICT_FTS) {
// è°ç¨ row_ins_index_entry_step()
// æå
¥è®°å½å°å½åè¿ä»£çç´¢å¼ä¸
err = row_ins_index_entry_step(node, thr);
switch (err) {
// æ§è¡æåï¼è·³åº switch
// ä¼æ¥çè¿è¡ä¸ä¸è½®è¿ä»£
case DB_SUCCESS:
break;
// åå¨ä¸»é®ç´¢å¼æå¯ä¸ç´¢å¼å²çª
case DB_DUPLICATE_KEY:
thr_get_trx(thr)->error_state = DB_DUPLICATE_KEY;
thr_get_trx(thr)->error_index = node->index;
// è´¯ç©¿å° default åæ¯
[[fallthrough]];
default:
// è¿åé误ç DB_DUPLICATE_KEY
return err;
}
}
// æå
¥è®°å½å°ä¸»é®ç´¢å¼æäºçº§ç´¢å¼æå
// node->indexãentry æå表ä¸çä¸ä¸ä¸ªç´¢å¼
node->index = node->index->next();
node->entry = UT_LIST_GET_NEXT(tuple_list, node->entry);
...
}
...
}
row_ins() ç主è¦é»è¾æ¯ä¸ª while 循ç¯ï¼é个è¿ä»£è¡¨ä¸çç´¢å¼ï¼æ¯è¿ä»£ä¸ä¸ªç´¢å¼ï¼é½ææé 好çè®°å½æå ¥å°ç´¢å¼ä¸ãè¿ä»£å®å ¨é¨ç´¢å¼ä¹åï¼æå ¥ä¸æ¡è®°å½å°è¡¨ä¸çæä½å°±å®æäºã
æ¥ä¸æ¥ï¼æ们éè¿ç¤ºä¾ SQL æ¥çç row_ins() çå ·ä½æ§è¡æµç¨ã
-- 为äºæ¹ä¾¿ï¼è¿éåå±ç¤ºä¸æµè¯è¡¨åç¤ºä¾ SQL
CREATE TABLE `t6` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`i1` int unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO `t6`(i1) VALUES(1001);
æµè¯è¡¨ t6 æ两个索å¼ï¼ä¸»é®ç´¢å¼ãuniq_i1ï¼å¯ä¸ç´¢å¼ï¼ï¼å¯¹äºç¤ºä¾ SQLï¼ä¸é¢ä»£ç ä¸ç while ä¼è¿è¡ 2 è½®è¿ä»£ï¼
第 1 è½®ï¼è°ç¨ row_ins_index_entry_step()ï¼æå ¥è®°å½å°ä¸»é®ç´¢å¼ãç¤ºä¾ SQL 没ææå®ä¸»é®å段å¼ï¼ä¸»é®å段ä¼ä½¿ç¨èªå¢å¼ï¼ä¸ä¼å表ä¸åæè®°å½å²çªï¼æå ¥æä½è½æ§è¡æåã
第 2 è½®ï¼è°ç¨ row_ins_index_entry_step()ï¼æå ¥è®°å½å° uniq_i1ãæ°æå ¥è®°å½ç i1 å段å¼ä¸º 1001ï¼å表ä¸åæè®°å½ï¼id = 1ï¼ç i1 å段å¼ç¸åï¼ä¼å¯¼è´å¯ä¸ç´¢å¼å²çªã
row_ins_index_entry_step() æå ¥è®°å½å° uniq_i1ï¼å¯¼è´å¯ä¸ç´¢å¼å²çªï¼å®ä¼è¿åé误ç DB_DUPLICATE_KEY ç» row_ins()ã
row_ins() æ¿å°é误ç ä¹åï¼å®çæ§è¡æµç¨å°æ¤ç»æï¼æé误ç è¿åç»è°ç¨è ã
å½æ§è¡æµç¨å¸¦çé误ç ï¼DB_DUPLICATE_KEYï¼ä¸è·¯è¿åå° row_insert_for_mysql_using_ins_graph()ï¼æ¥ä¸æ¥ä¼è°ç¨ row_mysql_handle_errors() å¤çå¯ä¸ç´¢å¼å²çªçååé»è¾ï¼è¿é¨åçå° 4.3 åæ»è¯å¥åèï¼ã
ä»ç»å¯ä¸ç´¢å¼å²çªçååé»è¾ä¹åï¼æ们以 row_ins_sec_index_entry_low() ä¸ºå ¥å£ï¼ä¸è·¯è·éæ§è¡æµç¨è¿å ¥ row_ins_sec_index_entry_low()ï¼æ¥ççç»å¯ä¸ç´¢å¼ä¸å²çªè®°å½å next-key å ±äº«éçæµç¨ã
è¿éç next-key å ±äº«éï¼å°±æ¯ä¸å¾ä¸ LOCK_DATA = 1001,1 对åºçéã
4.2 å¯ä¸ç´¢å¼è®°å½å é
// storage/innobase/row/row0ins.cc
dberr_t row_ins_sec_index_entry_low(...) {
...
if (dict_index_is_spatial(index)) {
// å¤ç空é´ç´¢å¼çé»è¾
...
} else {
if (index->table->is_intrinsic()) {
// MySQL å
é¨ä¸´æ¶è¡¨
...
} else {
// æ¾å°è®°å½å°è¦æå
¥å°åªä¸ªä½ç½®
btr_cur_search_to_nth_level(index, 0, entry, PAGE_CUR_LE, search_mode,
&cursor, 0, __FILE__, __LINE__, &mtr);
}
}
...
// ç´¢å¼ä¸éè¦ç¨å 个ï¼n_uniqueï¼å段
// æè½å¯ä¸æ è¯ä¸æ¡è®°å½
n_unique = dict_index_get_n_unique(index);
// å¦ææ¯ä¸»é®ç´¢å¼æå¯ä¸ç´¢å¼
if (dict_index_is_unique(index) &&
// 并ä¸å³å°æå
¥çè®°å½
// åç´¢å¼ä¸çè®°å½ç¸å
(cursor.low_match >= n_unique || cursor.up_match >= n_unique)) {
...
// å¤ææ°æå
¥è®°å½æ¯å¦ä¼å¯¼è´å²çª
// å¦æä¼å¯¼è´å²çªï¼ä¼å¯¹å²çªè®°å½å é
err = row_ins_scan_sec_index_for_duplicate(flags, index, entry, thr, check,
&mtr, offsets_heap);
...
}
...
}
row_ins_sec_index_entry_low() æ¾å°æå ¥è®°å½çç®æ ä½ç½®ä¹åï¼å¦æåç°è¿ä¸ªä½ç½®å·²ç»æä¸æ¡ç¸åçè®°å½äºï¼è¯´ææå¯è½å¯¼è´å¯ä¸ç´¢å¼å²çªï¼è°ç¨ row_ins_scan_sec_index_for_duplicate() 确认æ¯å¦å²çªï¼å¹¶æ ¹æ®æ åµè¿è¡å éå¤çã
// storage/innobase/row/row0ins.cc
[[nodiscard]] static dberr_t row_ins_scan_sec_index_for_duplicate(...)
{
...
// SQL è¯å¥æ¯å¦å
å«è§£å³ä¸»é®ãå¯ä¸ç´¢å¼å²çªçé»è¾
allow_duplicates = row_allow_duplicates(thr);
...
do {
...
if (flags & BTR_NO_LOCKING_FLAG) {
/* Set no locks when applying log in online table rebuild. */
} else if (allow_duplicates) {
...
// å¦æ SQL è¯å¥å
å«è§£å³ä¸»é®ãå¯ä¸ç´¢å¼å²çªçé»è¾
// ç»å²çªè®°å½å æä»éï¼LOCK_Xï¼
err = row_ins_set_rec_lock(LOCK_X, lock_type, block, rec, index, offsets,
thr);
} else /* else_1 */ {
if (skip_gap_locks) {
// å¦ææ¯æ°æ®åå
¸è¡¨ãSDI 表
// å³å®å ä»ä¹éï¼å¿½ç¥
...
} else if (is_supremum) {
/* We use next key lock to possibly combine the locks in bitmap.
Equivalent to LOCK_GAP. */
// next-key é
lock_type = LOCK_ORDINARY;
} else if (is_next) {
/* Only gap lock is required on next record. */
// gap é
lock_type = LOCK_GAP;
} else /* else_2 */ {
/* Next key lock for all equal keys. */
// next-key é
lock_type = LOCK_ORDINARY;
}
...
// SQL è¯å¥ãä¸å
å«ã解å³ä¸»é®ãå¯ä¸ç´¢å¼å²çªçé»è¾
// ç»å²çªè®°å½å å
±äº«éï¼LOCK_Sï¼
err = row_ins_set_rec_lock(LOCK_S, lock_type, block, rec, index, offsets,
thr);
}
...
if (is_supremum) {
continue;
}
// !index->allow_duplicates = true
// å³ index->allow_duplicates = false
// 表示ä¸å
许索å¼ä¸åå¨éå¤è®°å½
// è°ç¨ row_ins_dupl_error_with_rec()
// ç¡®å®æ°æå
¥è®°å½æ¯å¦ä¼å¯¼è´ç´¢å¼å²çª
if (!is_next && !index->allow_duplicates) {
if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) {
// è¿å trueï¼è¯´æä¼å¯¼è´ç´¢å¼å²çª
// æé误ç èµå¼ç» err åé
// ä½ä¸ºæ¹æ³çè¿åå¼
err = DB_DUPLICATE_KEY;
...
goto end_scan;
}
} else /* else_3 */ {
ut_a(is_next || index->allow_duplicates);
goto end_scan;
}
} while (pcur.move_to_next(mtr));
end_scan:
/* Restore old value */
dtuple_set_n_fields_cmp(entry, n_fields_cmp);
return err;
}
ä»¥ä¸ 3 ç§ SQLï¼allow_duplicates = trueï¼è¡¨ç¤º SQL å å«è§£å³ä¸»é®ãå¯ä¸ç´¢å¼å²çªçé»è¾ï¼
- load datafile replace
- replace into
- insert ... on duplicate key update
解å³å²çªçæ¹å¼ï¼
- load datafile replaceãreplace intoï¼å é¤è¡¨ä¸çå²çªè®°å½ï¼æå ¥æ°è®°å½ã
- insert ... on duplicate key updateï¼ç¨ update åé¢çåå段å¼æ´æ°è¡¨ä¸å²çªè®°å½å¯¹åºçå段ã
å¦æ SQL å å«è§£å³ä¸»é®ãå¯ä¸ç´¢å¼å²çªçé»è¾ï¼ä¼æ´æ°æå é¤å²çªè®°å½ï¼æ以éè¦å æä»éï¼LOCK_Xï¼ã
对äºç¤ºä¾ SQLï¼allow_duplicates = falseï¼æ§è¡æµç¨ä¼è¿å ¥ else_1 åæ¯ã
å ä¸ºç¤ºä¾ SQL ä¸å å«è§£å³ä¸»é®ãå¯ä¸ç´¢å¼å²çªçé»è¾ï¼ä¸ä¼æ´æ°ãå é¤å²çªè®°å½ï¼æ以ï¼åªéè¦å¯¹å²çªè®°å½å å ±äº«éï¼LOCK_Sï¼ï¼å éç精确模å¼ä¸º next-key éï¼å¯¹åº else_2 åæ¯ï¼ã
ååé allow_duplicates çå«ä¹ä¸åï¼if (!is_next && !index->allow_duplicates) ä¸ç index->allow_duplicates 表示å¯ä¸ç´¢å¼æ¯å¦å 许åå¨éå¤è®°å½ï¼
- å¯¹äº MySQL å é¨ä¸´æ¶è¡¨çäºçº§ç´¢å¼ï¼index->allow_duplicates = trueã
- 对äºå ¶å®è¡¨ï¼index->allow_duplicates = falseã
对äºç¤ºä¾ SQLï¼if (!is_next && !index->allow_duplicates) æ¡ä»¶æç«ï¼è°ç¨ row_ins_dupl_error_with_rec() å¾å°è¿åå¼ trueï¼è¯´ææ°æå ¥è®°å½åå¯ä¸ç´¢å¼ä¸çåæè®°å½å²çªã
æ§è¡æµç¨è¿å ¥ if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) åæ¯ï¼è®¾ç½®åé err çå¼ä¸º DB_DUPLICATE_KEYã
é£ä¹ï¼é®é¢æ¥äºï¼æå ¥è®°å½å°å¯ä¸ç´¢å¼æ¶ï¼åç°æå ¥ç®æ ä½ç½®å·²ç»æä¸æ¡ç¸åçè®°å½äºï¼è¿ä¸è½è¯´ææ°æå ¥è®°å½åå¯ä¸ç´¢å¼ä¸åæè®°å½å²çªåï¼
è¿çä¸è½ï¼å 为å¯ä¸ç´¢å¼æ个ç¹æ®åºæ¯è¦å¤çï¼é£å°±æ¯ NULL å¼ã
InnoDB 认为 NULL 表示æªç¥ï¼NULL å NULL ä¹æ¯ä¸ç¸ççï¼æ以ï¼å¯ä¸ç´¢å¼ä¸å¯ä»¥å å«å¤æ¡å段å¼ä¸º NULL çè®°å½ã
æ¬æä¸ï¼å¯ä¸ç´¢å¼é½æ¯æçäºçº§ç´¢å¼ãInnoDB 主é®çå段å¼æ¯ä¸å 许为 NULL çã
举个ä¾åï¼å¯¹äºæµè¯è¡¨ t6ï¼å设ææ¡è®°å½ç i1 å段å¼ä¸º NULLï¼æ°è®°å½ç i1 å段å¼ä¹ä¸º NULLï¼å°±å¯ä»¥æå ¥æåï¼èä¸ä¼æ¥ Duplicate key é误ã
4.3 åæ»è¯å¥
row_ins_step() æ§è¡ç»æä¹åï¼row_insert_for_mysql_using_ins_graph() ä» trx->error_state ä¸å¾å°é误ç DB_DUPLICATE_KEYï¼è¯´ææ°æå ¥è®°å½å¯¼è´å¯ä¸ç´¢å¼å²çªï¼è°ç¨ row_mysql_handle_errors() å¤çå²çªçååé»è¾ï¼å æ å¦ä¸ï¼
| > row_mysql_handle_errors(...) storage/innobase/row/row0mysql.cc:701
| + > // æå
¥è®°å½å¯¼è´å¯ä¸ç´¢å¼å²çªï¼éè¦åæ»
| + > trx_rollback_to_savepoint(trx_t*, trx_savept_t*) storage/innobase/trx/trx0roll.cc:151
| + - > trx_rollback_to_savepoint_low(trx_t*, trx_savept_t*) storage/innobase/trx/trx0roll.cc:114
| + - x > que_run_threads(que_thr_t*) storage/innobase/que/que0que.cc:1001
| + - x = > que_run_threads_low(que_thr_t*) storage/innobase/que/que0que.cc:966
| + - x = | > que_thr_step(que_thr_t*) storage/innobase/que/que0que.cc:913
| + - x = | + > row_undo_step(que_thr_t*) storage/innobase/row/row0undo.cc:362
| + - x = | + - > row_undo(undo_node_t*, que_thr_t*) storage/innobase/row/row0undo.cc:296
| + - x = | + - x > row_undo_ins(undo_node_t*, que_thr_t*) storage/innobase/row/row0uins.cc:500
| + - x = | + - x = > row_undo_ins_remove_clust_rec(undo_node_t*) storage/innobase/row/row0uins.cc:118
| + - x = | + - x = | > row_convert_impl_to_expl_if_needed(btr_cur_t*, undo_node_t*) storage/innobase/row/row0undo.cc:338
| + - x = | + - x = | + > // æ主é®ç´¢å¼è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼é
| + - x = | + - x = | + > lock_rec_convert_impl_to_expl(...) storage/innobase/lock/lock0lock.cc:5544
| + - x = | + - x = | + - > lock_rec_convert_impl_to_expl_for_trx(...) storage/innobase/lock/lock0lock.cc:5496
| + - x = | + - x = | + - x > lock_rec_add_to_queue(...) storage/innobase/lock/lock0lock.cc:1613
| + - x = | + - x = | + - x = > lock_rec_other_has_expl_req(...) storage/innobase/lock/lock0lock.cc:900
| + - x = | + - x = | + - x = > // å建éç»æ
| + - x = | + - x = | + - x = > RecLock::create(trx_t*, lock_prdt const*) storage/innobase/lock/lock0lock.cc:1356
| + - x = | + - x = | > // å
è¿è¡ä¹è§å é¤ï¼å¦æä¹è§å é¤å¤±è´¥ï¼åé¢ä¼è¿è¡æ²è§å é¤
| + - x = | + - x = | > btr_cur_optimistic_delete(...) storage/innobase/include/btr0cur.h:466
| + - x = | + - x = | + > btr_cur_optimistic_delete_func(...) storage/innobase/btr/btr0cur.cc:4562
| + - x = | + - x = | + - > lock_update_delete(...) storage/innobase/lock/lock0lock.cc:3350
| + - x = | + - x = | + - x > // ååæå
¥çè®°å½ï¼å 为å¯ä¸ç´¢å¼å²çªéè¦å é¤ï¼è®©å®çä¸ä¸æ¡è®°å½ç»§æ¿ GAP é
| + - x = | + - x = | + - x > lock_rec_inherit_to_gap(...) storage/innobase/lock/lock0lock.cc:2588
| + - x = | + - x = | + - x = > lock_rec_add_to_queue(...) storage/innobase/lock/lock0lock.cc:1681
| + - x = | + - x = | + - x = | > // 为被å é¤ç主é®è®°å½çä¸ä¸æ¡è®°å½å建éç»æ
| + - x = | + - x = | + - x = | > RecLock::create(trx_t*, lock_prdt const*) storage/innobase/lock/lock0lock.cc:1356
row_mysql_handle_errors() çæ ¸å¿é»è¾æ¯ä¸ª switchï¼æ ¹æ®ä¸åçé误ç è¿è¡ç¸åºçå¤çã
// storage/innobase/row/row0mysql.cc
bool row_mysql_handle_errors(...)
{
...
switch (err) {
...
case DB_DUPLICATE_KEY:
...
if (savept) {
/* Roll back the latest, possibly incomplete insertion
or update */
trx_rollback_to_savepoint(trx, savept);
}
/* MySQL will roll back the latest SQL statement */
break;
...
}
...
}
对äºé误ç DB_DUPLICATE_KEYï¼row_mysql_handle_errors() ä¼è°ç¨ trx_rollback_to_savepoint() åæ»ç¤ºä¾ SQL 对äºä¸»é®ç´¢å¼æåçæå ¥è®°å½æä½ã
savept æ¯è°ç¨ row_ins_step() æå ¥è®°å½å°ä¸»é®ãå¯ä¸ç´¢å¼ä¹åçä¿åç¹ï¼trx_rollback_to_savepoint() å¯ä»¥å©ç¨ savept ä¸çä¿åç¹ï¼å é¤ row_ins_step() ååæå ¥å°ä¸»é®ç´¢å¼ä¸çè®°å½ï¼è®©ä¸»é®ç´¢å¼åå° row_ins_step() æ§è¡ä¹åçç¶æã
对äºç¤ºä¾ SQLï¼trx_rollback_to_savepoint() ç»è¿å¤çº§ä¹åï¼è°ç¨ row_undo_ins_remove_clust_rec() å é¤å·²æå ¥å°ä¸»é®ç´¢å¼çè®°å½ã
// storage/innobase/row/row0uins.cc
[[nodiscard]] static dberr_t row_undo_ins_remove_clust_rec(
undo_node_t *node) /*!< in: undo node */
{
...
// ææ°æå
¥å°ä¸»é®ç´¢å¼ä¸çè®°å½ä¸çéå¼é
// 转æ¢ä¸ºæ¾å¼é
row_convert_impl_to_expl_if_needed(btr_cur, node);
// å
è¿è¡ä¹è§å é¤
if (btr_cur_optimistic_delete(btr_cur, 0, &mtr)) {
err = DB_SUCCESS;
goto func_exit;
}
...
// å¦æä¹è§å é¤å¤±è´¥ï¼åè¿è¡æ²è§å é¤
btr_cur_pessimistic_delete(&err, false, btr_cur, 0, true, node->trx->id,
node->undo_no, node->rec_type, &mtr, &node->pcur,
nullptr);
}
å é¤ä¸»é®ç´¢å¼è®°å½ä¹åï¼éè¦ç»å®å éãå 为æå ¥æä½å å«éå¼éçé»è¾ï¼æ以è¿éçå éæä½æ¯æå³å°è¢«å é¤è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éã
å½ç¶ï¼éè¦æ»¡è¶³ä¸å®çæ¡ä»¶ï¼row_convert_impl_to_expl_if_needed() æä¼æ主é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éã
// storage/innobase/row/row0undo.cc
void row_convert_impl_to_expl_if_needed(btr_cur_t *cursor, undo_node_t *node) {
...
// æ»¡è¶³ä»¥ä¸ 3 ç§æ¡ä»¶ä¹ä¸ï¼ä¸éè¦æéå¼é转æ¢ä¸ºæ¾å¼éï¼
// 1. !node->partial = trueï¼å³ node->partial = false
// 表示æ´ä¸ªäºå¡åæ»
// 2. node->trx == nullptr
// 3. node->trx->isolation_level < trx_t::REPEATABLE_READ
// äºå¡é离级å«ä¸ºï¼è¯»æªæ交ï¼RUï¼ã读已æ交ï¼RCï¼
if (!node->partial || (node->trx == nullptr) ||
node->trx->isolation_level < trx_t::REPEATABLE_READ) {
return;
}
...
// æ»¡è¶³ä»¥ä¸ 4 ç§æ¡ä»¶ï¼éè¦æéå¼é转æ¢æ¾å¼éï¼
// 1. heap_no 对åºçè®°å½ä¸æ¯ supremum
// 2. å½åç´¢å¼ä¸æ¯ç©ºé´ç´¢å¼
// 3. ä¸æ¯ç¨æ·ä¸´æ¶è¡¨
// 4. ä¸æ¯ MySQL å
é¨ä¸´æ¶è¡¨
if (/* 1 */ heap_no != PAGE_HEAP_NO_SUPREMUM &&
/* 2 */ !dict_index_is_spatial(index) &&
/* 3 */ !index->table->is_temporary() &&
/* 4 */ !index->table->is_intrinsic()) {
lock_rec_convert_impl_to_expl(block, rec, index,
Rec_offsets().compute(rec, index));
}
}
对äºç¤ºä¾ SQLï¼ç¬¬ 1 个 if æ¡ä»¶ä¸æç«ï¼æ以ä¸ä¼æ§è¡ returnï¼èæ¯ä¼ç»§ç»å¤æ第 2 个 if æ¡ä»¶ã
第 2 个 if æ¡ä»¶æç«ï¼æ§è¡æµç¨è¿å ¥ if åæ¯ï¼è°ç¨ lock_rec_convert_impl_to_expl() æéå¼é转æ¢ä¸ºæ¾å¼éã
æ§è¡æµç¨åå° row_undo_ins_remove_clust_rec()ï¼è°ç¨ row_convert_impl_to_expl_if_needed() æ主é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éä¹åï¼æ¥ä¸å°±æ¯å é¤è®°å½äºã
å è°ç¨ btr_cur_optimistic_delete() è¿è¡ä¹è§å é¤ã
ä¹è§å é¤æçæ¯å é¤æ°æ®é¡µä¸çè®°å½ä¹åï¼ä¸ä¼å 为æ°æ®é¡µä¸çè®°å½æ°éè¿å°è触åç¸é»çæ°æ®é¡µå并ã
å¦æä¹è§å é¤æåï¼ç´æ¥è¿å DB_SUCCESSã
å¦æä¹è§å é¤å¤±è´¥ï¼åè°ç¨ btr_cur_pessimistic_delete() è¿è¡æ²è§å é¤ã
æ²è§å é¤æçæ¯å é¤æ°æ®é¡µä¸çè®°å½ä¹åï¼å 为æ°æ®é¡µä¸çè®°å½æ°éè¿å°ï¼ä¼è§¦ç¸é»çæ°æ®é¡µå并ã
4.4 主é®ç´¢å¼è®°å½çéå¼é转æ¢
ä¸ä¸å°èä¸ï¼æ们没ææ·±å ¥ä»ç»ä¸»é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éçé»è¾ï¼æ¥ä¸æ¥ï¼æ们æ¥ççè¿ä¸ªé»è¾ã
// storage/innobase/lock/lock0lock.cc
void lock_rec_convert_impl_to_expl(...) {
trx_t *trx;
...
// 主é®ç´¢å¼
if (index->is_clustered()) {
trx_id_t trx_id;
// è·å rec è®°å½ä¸ DB_TRX_ID å段çå¼
// æ¿å°æå
¥ rec è®°å½çäºå¡ ID
trx_id = lock_clust_rec_some_has_impl(rec, index, offsets);
// å¤æäºå¡æ¯å¦å¤äºæ´»è·ç¶æ
// å¦æäºå¡æ¯æ´»è·ç¶æï¼è¿åäºå¡ç trx_t 对象
// å¦æäºå¡å·²æ交ï¼è¿å nullptr
trx = trx_rw_is_active(trx_id, true);
} else { // äºçº§ç´¢å¼
...
}
if (trx != nullptr) {
ulint heap_no = page_rec_get_heap_no(rec);
...
// å¦æäºå¡æ¯æ´»è·ç¶æ
// æ rec è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼é
lock_rec_convert_impl_to_expl_for_trx(block, rec, index, offsets, trx,
heap_no);
}
}
InnoDB 主é®ç´¢å¼çè®°å½ä¸ï¼é½æä¸ä¸ªéèå段 DB_TRX_IDã
lock_rec_convert_impl_to_expl() å è°ç¨ lock_clust_rec_some_has_impl() 读å主é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ç DB_TRX_ID å段ã
ç¶åè°ç¨ trx_rw_is_active() å¤æ DB_TRX_ID 对åºçäºå¡æ¯å¦å¤äºæ´»è·ç¶æï¼äºå¡æªæ交ï¼ã
å¦æäºå¡å¤äºæ´»è·ç¶æï¼è°ç¨ lock_rec_convert_impl_to_expl_for_trx() æ rec è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éã
// storage/innobase/lock/lock0lock.cc
static void lock_rec_convert_impl_to_expl_for_trx(...)
{
...
{
locksys::Shard_latch_guard guard{UT_LOCATION_HERE, block->get_page_id()};
...
trx_mutex_enter(trx);
...
// å¤æäºå¡çç¶æä¸æ¯ TRX_STATE_COMMITTED_IN_MEMORY
if (!trx_state_eq(trx, TRX_STATE_COMMITTED_IN_MEMORY) &&
// heap_no 对åºè®°å½ä¸æ²¡ææ¾å¼çæä»é
!lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP, block, heap_no, trx)) {
ulint type_mode;
// å éç²åº¦ï¼è®°å½ï¼LOCK_RECï¼
// å é模å¼ï¼åéï¼LOCK_Xï¼
// å éç精确模å¼ï¼è®°å½ï¼LOCK_REC_NOT_GAPï¼
type_mode = (LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP);
lock_rec_add_to_queue(type_mode, block, heap_no, index, trx, true);
}
trx_mutex_exit(trx);
}
trx_release_reference(trx);
...
}
lock_rec_convert_impl_to_expl_for_trx() ä¹ä¸ä¼ç §åå ¨æ¶ï¼å®è¿ä¼è¿ä¸æ¥å¤æï¼
- äºå¡ç¶æä¸æ¯ TRX_STATE_COMMITTED_IN_MEMORYï¼å 为å¤äºè¿ä¸ªç¶æçäºå¡å°±ç®æ¯å·²ç»æ交æåäºï¼å·²æ交æåçäºå¡ä¿®æ¹çè®°å½ä¸å å«éèå¼éé»è¾ï¼ä¹å°±ä¸éè¦æéå¼é转æ¢ä¸ºæ¾å¼éäºã
- è®°å½ä¸æ²¡ææ¾å¼çæä»éã
满足ä¸é¢ 2 个æ¡ä»¶ä¹åï¼æä¼è°ç¨ lock_rec_add_to_queue() å建é对象ï¼RecLockï¼å¹¶å å ¥å°å ¨å±é对象ç hash 表ä¸ï¼è¿å°±æç»å®æäºæ主é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éã
4.5 主é®ç´¢å¼è®°å½çé转移
主é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çæ¾å¼éï¼åªæ¯ä¸ªè¿æ¸¡ï¼å®æ¯ç¨æ¥ä¸ºé转移ååå¤çã
ä¸ç®¡æ¯ä¹è§å é¤ï¼è¿æ¯æ²è§å é¤ï¼å é¤åæå ¥å°ä¸»é®ç´¢å¼çè®°å½ä¹åï¼éè¦æ该记å½ä¸çé转移å°å®çä¸ä¸æ¡è®°å½ä¸ï¼è½¬ç§»æä½ç± lock_update_delete() å®æã
// storage/innobase/lock/lock0lock.cc
void lock_update_delete(const buf_block_t *block, const rec_t *rec) {
...
if (page_is_comp(page)) {
// è·åå³å°è¢«å é¤çè®°å½çç¼å·
heap_no = rec_get_heap_no_new(rec);
// è·åå³å°è¢«å é¤è®°å½çä¸ä¸æ¡è®°å½çç¼å·
next_heap_no = rec_get_heap_no_new(page + rec_get_next_offs(rec, true));
} else {
...
}
...
/* Let the next record inherit the locks from rec, in gap mode */
// æå³å°è¢«å é¤è®°å½ä¸çé转移å°å®çä¸ä¸æ¡è®°å½ä¸
lock_rec_inherit_to_gap(block, block, next_heap_no, heap_no);
...
}
lock_update_delete() è°ç¨ rec_get_heap_no_new() è·åå³å°è¢«å é¤è®°å½çä¸ä¸æ¡è®°å½çç¼å·ï¼ç¶åè°ç¨ lock_rec_inherit_to_gap() æå³å°è¢«å é¤è®°å½ä¸çé转移å°å®çä¸ä¸æ¡è®°å½ä¸ã
// storage/innobase/lock/lock0lock.cc
static void lock_rec_inherit_to_gap(...)
{
lock_t *lock;
...
// heap_no æ¯ä¸»é®ç´¢å¼ä¸å³å°è¢«å é¤çè®°å½ç¼å·
for (lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no);
lock != nullptr; lock = lock_rec_get_next(heap_no, lock)) {
/* Skip inheriting lock if set */
if (lock->trx->skip_lock_inheritance) {
continue;
}
if (!lock_rec_get_insert_intention(lock) &&
!lock->index->table->skip_gap_locks() &&
(!lock->trx->skip_gap_locks() || lock->trx->lock.inherit_all.load())) {
lock_rec_add_to_queue(LOCK_REC | LOCK_GAP | lock_get_mode(lock),
heir_block, heir_heap_no, lock->index, lock->trx);
}
}
}
for 循ç¯ä¸ï¼lock_rec_get_first() è·å主é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çéã
è½å¦è·åå°éï¼åå³äºåé¢ç row_convert_impl_to_expl_if_needed() æ¯å¦å·²ç»æè®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éã
row_convert_impl_to_expl_if_needed() ä¼å¯¹å¤ä¸ªæ¡ä»¶è¿è¡å¤æï¼ä»¥å³å®æ¯å¦æè®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éãå ¶ä¸ï¼æ¯è¾éè¦çå¤ææ¡ä»¶æ¯äºå¡é离级å«ï¼
- å¦æäºå¡é离级å«æ¯ READ-COMMITTEDï¼éå¼é ä¸è½¬æ¢ä¸ºæ¾å¼éã
- å¦æäºå¡é离级å«æ¯ REPEATABLE-READï¼åç»åå ¶å®å¤ææ¡ä»¶ï¼å³å®æ¯å¦æéå¼é转æ¢ä¸ºæ¾å¼éã
æ们以æµè¯è¡¨åç¤ºä¾ SQL 为ä¾ï¼æ¥çç lock_rec_inherit_to_gap() çæ§è¡æµç¨ã
ç¤ºä¾ SQL æ§è¡äº REPEATABLE-READ é离级å«ä¹ä¸ï¼å¹¶ä¸æ»¡è¶³å ¶å®å¤ææ¡ä»¶ï¼row_convert_impl_to_expl_if_needed() ä¼æè®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼éã
æ以ï¼lock_rec_get_first() ä¼è·åå°ä¸»é®ç´¢å¼ä¸å³å°è¢«å é¤è®°å½ä¸çéï¼å¹¶ä¸ for 循ç¯ä¸ç第 2 个 if æ¡ä»¶æç«ï¼æ§è¡æµç¨è¿å ¥ if åæ¯ã
对äºç¤ºä¾ SQLï¼å³å°è¢«å é¤è®°å½çä¸ä¸æ¡è®°å½æ¯ supremumï¼è°ç¨ lock_rec_add_to_queue() æå³å°è¢«å é¤è®°å½ä¸çéè½¬ç§»å° supremum è®°å½ä¸ã
æ¥ä¸æ¥ï¼ä»ç» lock_rec_add_to_queue() 代ç ä¹åï¼æ们å çä¸ä¸ä¼ ç»è¯¥æ¹æ³ç第 1 个åæ°çå¼ã
lock_get_mode() ä¼è¿åå³å°è¢«å é¤è®°å½ä¸çéï¼LOCK_REC_NOT_GAP | LOCK_REC | LOCK_Xã
第 1 个åæ°çå¼ä¸ºï¼LOCK_REC | LOCK_GAP | lock_get_mode(lock)ã
æ lock_get_mode() çè¿åå¼ä»£å ¥å ¶ä¸ï¼å¾å°ï¼
LOCK_REC | LOCK_GAP | LOCK_REC_NOT_GAP | LOCK_REC | LOCK_Xã
å»éä¹åï¼å¾å°ä¼ ç» lock_rec_add_to_queue() ç第 1 个åæ°ï¼type_modeï¼çå¼ï¼LOCK_REC | LOCK_GAP | LOCK_REC_NOT_GAP | LOCK_Xã
// storage/innobase/lock/lock0lock.cc
static void lock_rec_add_to_queue(ulint type_mode, ...) {
...
// 对 supremum 伪记å½è¿è¡ç¹æ®å¤ç
if (heap_no == PAGE_HEAP_NO_SUPREMUM) {
...
// å»æ LOCK_GAPãLOCK_REC_NOT_GAP
type_mode &= ~(LOCK_GAP | LOCK_REC_NOT_GAP);
}
...
// å®ä¾åé对象
RecLock rec_lock(index, block, heap_no, type_mode);
...
// æé对象å å
¥å
¨å±é对象 hash 表
rec_lock.create(trx);
...
}
type_mode å°±æ¯ lock_rec_inherit_to_gap() å½æ°ä¸ä¼ è¿æ¥ç第 1 个åæ°ï¼å®çå¼ä¸ºï¼
LOCK_REC | LOCK_GAP | LOCK_REC_NOT_GAP | LOCK_Xã
对äºç¤ºä¾ SQLï¼å³å°è¢«å é¤è®°å½çä¸ä¸æ¡è®°å½æ¯ supremumï¼æ§è¡æµç¨ä¼å½ä¸ if (heap_no == PAGE_HEAP_NO_SUPREMUM) åæ¯ï¼æ§è¡ä»£ç ï¼type_mode &= ~(LOCK_GAP | LOCK_REC_NOT_GAP)ã
ä» type_mode ä¸å»æ LOCK_GAPãLOCK_REC_NOT_GAPï¼å¾å° LOCK_REC | LOCK_Xï¼è¡¨ç¤ºç» supremum å next-key æä»éã
5. æ»ç»
REPEATABLE-READ é离级å«ä¸ï¼å¦ææå ¥ä¸æ¡è®°å½ï¼å¯¼è´å¯ä¸ç´¢å¼å²çªï¼æ§è¡æµç¨å¦ä¸ï¼
- æå ¥è®°å½å°ä¸»é®ç´¢å¼ï¼æåã
- æå ¥è®°å½å°å¯ä¸ç´¢å¼ï¼å²çªï¼æå ¥å¤±è´¥ã
-
ç»å¯ä¸ç´¢å¼ä¸å²çªçè®°å½å éã
å¯¹äº load datafile replaceãreplace intoãinsert ... on duplicate key update è¯å¥ï¼å æä»éï¼LOCK_Xï¼ã
对äºå ¶å®è¯å¥ï¼å å ±äº«éï¼LOCK_Sï¼ã
- æ主é®ç´¢å¼ä¸å¯¹åºè®°å½ä¸çéå¼é转æ¢ä¸ºæ¾å¼é [Not RC]ã
- æ主é®ç´¢å¼è®°å½ä¸çæ¾å¼é转移å°å®çä¸ä¸æ¡è®°å½ä¸ [Not RC]ã
- å é¤ä¸»é®ç´¢å¼è®°å½ã
顺便说ä¸ä¸ï¼å¯¹äº READ-COMMITTED é离级å«ï¼å¤§ä½æµç¨ç¸åï¼ä¸åä¹å¤å¨äºï¼å®æ²¡æä¸é¢æµç¨ä¸æäº [Not RC] æ è®°ç两个æ¥éª¤ã
对äºç¤ºä¾ SQLï¼READ-COMMITTED é离级å«ä¸ï¼ä¸ä¼ç»ä¸»é®ç´¢å¼ç supremum è®°å½å éï¼å éæ åµå¦ä¸ï¼
æåï¼æç¤ºä¾ SQL å¨ REPEATABLE-READ é离级å«ä¸çå éæ åµæ¾å¨è¿éï¼ä½ä¸ªå¯¹æ¯ï¼
为帮å©å¼åè 们æåé¢è¯æè½ãææºä¼å ¥èBATJç大åå ¬å¸ï¼ç¹å«å¶ä½äºè¿ä¸ªä¸è¾ââè¿ä¸æ¬¡æ´ä½æ¾åºã
大è´å 容å æ¬äºï¼ Java éåãJVMãå¤çº¿ç¨ã并åç¼ç¨ã设计模å¼ãSpringå ¨å®¶æ¡¶ãJavaãMyBatisãZooKeeperãDubboãElasticsearchãMemcachedãMongoDBãRedisãMySQLãRabbitMQãKafkaãLinuxãNettyãTomcatç大åé¢è¯é¢çãçææ¯æ ï¼
欢è¿å¤§å®¶å ³æ³¨å ¬ä¼å·ãå泡Javaãï¼åå¤ã007ãï¼è·å以ä¸ææ°Javaå端æ¶æVIPå¦ä¹ èµæ以åè§é¢å¦ä¹ æç¨ï¼ç¶åä¸èµ·å¦ä¹ ï¼ä¸æå¨æï¼é¢è¯ææã
æ¯ä¸ä¸ªä¸æ é½æ¯å¤§å®¶éå¸¸å ³å¿ï¼åé常æä»·å¼çè¯é¢ï¼å¦ææçæç« å¯¹ä½ ææ帮å©ï¼è¿è¯·å¸®å¿ç¹èµã好è¯ã转åä¸ä¸ï¼ä½ çæ¯æä¼æ¿å±æè¾åºæ´é«è´¨éçæç« ï¼é常æè°¢ï¼