線上一個實例報錯The total number of locks exceeds the lock table size異常，導(dǎo)致復(fù)制中斷，而當(dāng)時的SQL是一條insert into select xxx語句，結(jié)果集比較大缘眶，從提示信息看是當(dāng)前事務(wù)的lock size超過了lock table size,那么當(dāng)前事務(wù)需要多大的lock size，而系統(tǒng)的lock table size又是多大呢。
從show innodb status 可以看出當(dāng)前事務(wù)的lock size大忻嬷场：
---TRANSACTION 65CEAB, ACTIVE 5492 sec, process no 10782, OS thread id 140449790605056 inserting
mysql tables in use 2, locked 2
9339476 lock struct(s), heap size 831633848, 49142856 row lock(s), undo log entries 12491866
MySQL thread id 910089, query id 14148540 Sending data
INSERT IGNORE INTO
? ? ? ? T_XXX_TMP(
? ? ? ? FID,
? ? ? ? FSOURCE_NAME,
? ? ? ? FUNIQUE,
? ? ? ? FTITLE,
? ? ? ? FCONTENT,
? ? ? ? FLINK,
? ? ? ? FSOURCE_IMG_URL,
? ? ? ? FIMG_URL,
? ? ? ? FPUSH_PATH,
? ? ? ? FPUTDATE,
? ? ? ? FSTATUS,
? ? ? ? FTYPE,
? ? ? ? FAUTHOR,
? ? ? ? FLMODIFY,
? ? ? ? FPOSTTIME,
? ? ? ? FKEYWORDS,
? ? ? ? FPV,
? ? ? ? FRECOMMEND,
? ? ? ? FRESOURCE_TYPE,
? ? ? ? FLABEL,
? ? ? ? FCOMMENT_COUNT,
? ? ? ? FSHARE_COUNT,
? ? ? ? FCOLLECT_COUNT,
? ? ? ? FBIG_IMG_URL,
? ? ? ? FCATEGORY,
? ? ? ? FCP_CHANNEL_ID,
? ? ? ? FPIC_SHOW_TYPE,
? ? ? ? FTHUMBNAILS,
heap size831633848 ,800M之多，而當(dāng)前實例的buffer pool大小只有1191M哭廉。還要分配給各個block list脊僚。buffer pool不僅僅包含各個block list（free list,lru list,flush list...），還有adaptive hash index ,lock heap 等都是從buffer pool這種分配的遵绰。在事務(wù)執(zhí)行insert update,delete操作時,會先執(zhí)行下面函數(shù)檢查buffer pool用于block list的空間是否足夠（也就是檢查AHI,heap locksize是否占用過多的buffer pool空間）
/******************************************************************//**
Returns TRUE if less than 25 % of the buffer pool in any instance is
available. This can be used in heuristics to prevent huge transactions
eating up the whole buffer pool for their locks.?
@return TRUE if less than 25 % of buffer pool left */ ?
UNIV_INTERN
ibool
buf_LRU_buf_pool_running_out(void) ? 檢查各個buffer pool中可用的空間小于25%
/*==============================*/
{
? ? ? ? ulint ? i;
? ? ? ? ibool ? ret = FALSE;
? ? ? ? for (i = 0; i < srv_buf_pool_instances && !ret; i++) {
? ? ? ? ? ? ? ? buf_pool_t* ? ? buf_pool;
? ? ? ? ? ? ? ? buf_pool = buf_pool_from_array(i);
? ? ? ? ? ? ? ? buf_pool_mutex_enter(buf_pool);
? ? ? ? ? ? ? ? if (!recv_recovery_on
? ? ? ? ? ? ? ? ? ? && UT_LIST_GET_LEN(buf_pool->free) ? free list 長度
? ? ? ? ? ? ? ? ? ? ? ?+ UT_LIST_GET_LEN(buf_pool->LRU) ?lru list長度
? ? ? ? ? ? ? ? ? ? ? ?< buf_pool->curr_size / 4) { ?這里是判斷free list + lru list的長度是否小于buffer pool size 的 25%
? ? ? ? ? ? ? ? ? ? ? ? ret = TRUE;
? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? buf_pool_mutex_exit(buf_pool);
? ? ? ? }
? ? ? ? return(ret);
}
更新辽幌，插入記錄到索引中，或者插入記錄到一個已刪除的記錄位置都會調(diào)用該函數(shù)檢查buffer pool空間椿访，如果不足25%,則返回DB_LOCK_TABLE_FULL 乌企，在mysql層會檢測到該返回值，然后返回給客戶端對應(yīng)的異常信息成玫。也就是標(biāo)題的異常加酵。
同樣select在一些情況下也會調(diào)用該函數(shù)，在查詢需要加鎖的情況下哭当，會調(diào)用該函數(shù)獲取buffer pool剩余空間：
Sets a lock on a record.
@return DB_SUCCESS, DB_SUCCESS_LOCKED_REC, or error code */
UNIV_INLINE
dberr_t
sel_set_rec_lock(
/*=============*/
? ? ? ? btr_pcur_t* ? ? ? ? ? ? pcur, ? /*!< in: cursor */
? ? ? ? const rec_t* ? ? ? ? ? ?rec, ? ?/*!< in: record */
? ? ? ? dict_index_t* ? ? ? ? ? index, ?/*!< in: index */
? ? ? ? const ulint* ? ? ? ? ? ?offsets,/*!< in: rec_get_offsets(rec, index) */
? ? ? ? ulint ? ? ? ? ? ? ? ? ? mode, ? /*!< in: lock mode */
? ? ? ? ulint ? ? ? ? ? ? ? ? ? type, ? /*!< in: LOCK_ORDINARY, LOCK_GAP, or
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? LOC_REC_NOT_GAP */
? ? ? ? que_thr_t* ? ? ? ? ? ? ?thr, ? ?/*!< in: query thread */
? ? ? ? mtr_t* ? ? ? ? ? ? ? ? ?mtr) ? ?/*!< in: mtr */
{
? ? ? ? trx_t* ? ? ? ? ? ? ? ? ?trx;
? ? ? ? dberr_t ? ? ? ? ? ? ? ? err = DB_SUCCESS;
? ? ? ? const buf_block_t* ? ? ?block;
? ? ? ? block = btr_pcur_get_block(pcur);
? ? ? ? trx = thr_get_trx(thr);
? ? ? ? if (UT_LIST_GET_LEN(trx->lock.trx_locks) > 10000) { ?如果該事務(wù)上鎖的數(shù)量比較多猪腕， 大于10000，則需要進(jìn)行判斷钦勘。
? ? ? ? ? ? ? ? if (buf_LRU_buf_pool_running_out()) { ?此時說明加鎖操作已經(jīng)消耗了很多buffer pool空間陋葡，剩余空間不足以支撐該事務(wù)繼續(xù)執(zhí)行下去。
? ? ? ? ? ? ? ? ? ? ? ? return(DB_LOCK_TABLE_FULL);
? ? ? ? ? ? ? ? }
? ? ? ? }
彻采。腐缤。朵栖。
}
在這個案例中，執(zhí)行的語句insert into ,xxxx select xxxx 操作柴梆，為例保持?jǐn)?shù)據(jù)的一致性陨溅，innodb會對select 語句中的記錄進(jìn)行x-lock操作，當(dāng)select的記錄比較多的時候绍在，加鎖所需的heap size越大门扇。從show engine innodb status可以看出：
9339476 lock struct(s), heap size 831633848, 49142856 row lock(s), undo log entries 12491866。
49142856 把行鎖偿渡，需要831633848 大小的內(nèi)存空間臼寄，當(dāng)然這還僅僅是語句執(zhí)行過程中的狀態(tài)，實際上比這個還要多溜宽〖回到上面的問題，那么當(dāng)前事務(wù)需要多大的lock size适揉，而系統(tǒng)的lock table size又是多大呢留攒？
這里至少需要800M，而系統(tǒng)的lock table size 可以認(rèn)為是buffer pool的75%嫉嘀，對于可用的block list,需要大于buffer pool size的25%才能繼續(xù)執(zhí)行當(dāng)前事務(wù)炼邀。如果解決該問題，很簡單剪侮，調(diào)大innod buffer pool size即可拭宁，
這里從1191M調(diào)整到5191M，start slave sql_thread瓣俯，一段時間之后杰标，順利通過了。
還有哪些場景會導(dǎo)致該問題? delete一個大表彩匕，insert xxx select xxxx 記錄數(shù)較大腔剂，update 一個大表，select xxx for update推掸，select xxx in share mode 這些大事務(wù)都有可能導(dǎo)致該問題桶蝎，影響的記錄數(shù)越多，lock heap size約大谅畅。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT count(*) FROM `test`.`T_XXX_ARTICLE_TMP` WHERE (`FID` >= 168666823 AND `FID` < 172310087) for update?;
+----------+
| count(*) |
+----------+
| ?3112193 |
+----------+
1 row in set (3 min 1.56 sec)
show engine innodb status:
---TRANSACTION 691281, ACTIVE 226 sec, process no 1249, OS thread id 140670129358592
746334 lock struct(s), heap size 66468280, 3858526 row lock(s)
MySQL thread id 5802, query id 367184 localhost root?
可以看出300W記錄登渣，需要heap size66468280 bytes = 63M。線上故障影響的記錄是5kw毡泻，需要800M heap size胜茧。其實也能看出，加鎖所需的內(nèi)存和記錄數(shù)有直接關(guān)系。
那么我們可以將大事務(wù)拆小呻顽，來降低每次事務(wù)加鎖需要的heap size大小雹顺。
這同時也說明，如果一個實例中有大表廊遍，那么buffer pool應(yīng)當(dāng)盡可能大一下嬉愧，buffer pool太小，則一旦遇上大事務(wù)就會導(dǎo)致該問題出現(xiàn)喉前。我們線上是單機跑了多個實例没酣，每個實例的buffer pool設(shè)置有限，
而該實例的數(shù)據(jù)大小達(dá)到了700G卵迂，出問題的大表有117G裕便，遇到大事務(wù)碰到該問題則是必然的。解決辦法：
1.調(diào)大innodb buffer pool size?
2.將大事務(wù)拆小
如果僅僅是查詢大表见咒，即使影響的記錄數(shù)很多偿衰，也是不會加鎖的，也就不存在上面的問題改览。
