鎖類型

元數(shù)據(jù)鎖

表鎖

IX

自增鎖

自增鎖模式通過(guò)參數(shù)innodb_autoinc_lock_mode來(lái)控制捎琐，加鎖選擇參閱函數(shù)ha_innobase::innobase_lock_autoinc

聚集索引或唯一索引檢查到唯一鍵沖突時(shí)加的鎖（標(biāo)記刪除的也算）

• 鎖類型
  對(duì)于普通的INSERT/UPDATE，會(huì)加LOCK_S鎖任斋，而對(duì)于類似REPLACE INTO或者INSERT … ON DUPLICATE這樣的SQL加的是X鎖柴淘。
• 鎖方式
  對(duì)于聚集索引加的是LOCK_REC_NOT_GAP類似的S或者X記錄鎖晃择。
  對(duì)于二級(jí)唯一索引雷蹂，若檢查到重復(fù)鍵蛆楞，當(dāng)前版本總是加 LOCK_ORDINARY 類型的記錄鎖溯乒。

插入意向鎖

執(zhí)行 insert 語(yǔ)句，判斷是否有和插入意向鎖沖突的鎖豹爹，如果有裆悄，加插入意向鎖，進(jìn)入鎖等待臂聋；如果沒有光稼，直接寫數(shù)據(jù)，不加任何鎖逻住；

insert ... select 情況

INSERT … SELECT插入數(shù)據(jù)時(shí)钟哥，會(huì)對(duì)SELECT的表上掃描到的數(shù)據(jù)加LOCK_S鎖。

加鎖順序

ha_innobase::write_row
----row_insert_for_mysql
--------row_insert_for_mysql_using_ins_graph
------------row_ins_step
----------------lock_table 加表級(jí)IX鎖
----------------row_ins
--------------------row_ins_index_entry_step
------------------------row_ins_index_entry
----------------------------row_ins_clust_index_entry or row_ins_sec_index_entry

row_ins_clust_index_entry

row_ins_clust_index_entry
----row_ins_clust_index_entry_low
--------btr_pcur_open //調(diào)用btr_cur_search_to_nth_level 查詢索引樹瞎访，將cursor移動(dòng)到待插入記錄相應(yīng)的位置
--------如果判斷存在唯一沖突,進(jìn)入下面函數(shù)加鎖
--------row_ins_duplicate_error_in_clust
--------btr_cur_optimistic_insert or btr_cur_pessimistic_insert
------------btr_cur_ins_lock_and_undo
----------------lock_rec_insert_check_and_lock
--------------------判斷是否與插入意向鎖沖突
--------------------lock_rec_other_has_conflicting

row_ins_sec_index_entry

row_ins_sec_index_entry
----row_ins_sec_index_entry_low
--------btr_cur_search_to_nth_level
------------如果判斷存在唯一沖突,進(jìn)入下面函數(shù)加鎖
------------row_ins_scan_sec_index_for_duplicate
--------btr_cur_optimistic_insert or btr_cur_pessimistic_insert
------------btr_cur_ins_lock_and_undo
----------------lock_rec_insert_check_and_lock
--------------------判斷是否與插入意向鎖沖突
--------------------lock_rec_other_has_conflicting

唯一鍵沖突時(shí)的加鎖場(chǎng)景

聚簇索引

/***************************************************************//**
Tries to insert an entry into a clustered index, ignoring foreign key
constraints. If a record with the same unique key is found, the other
record is necessarily marked deleted by a committed transaction, or a
unique key violation error occurs. The delete marked record is then
updated to an existing record, and we must write an undo log record on
the delete marked record.
@retval DB_SUCCESS on success
@retval DB_LOCK_WAIT on lock wait when !(flags & BTR_NO_LOCKING_FLAG)
@retval DB_FAIL if retry with BTR_MODIFY_TREE is needed
@return error code */
dberr_t
row_ins_clust_index_entry_low(
/*==========================*/
    ulint       flags,  /*!< in: undo logging and locking flags */
    ulint       mode,   /*!< in: BTR_MODIFY_LEAF or BTR_MODIFY_TREE,
                depending on whether we wish optimistic or
                pessimistic descent down the index tree */
    dict_index_t*   index,  /*!< in: clustered index */
    ulint       n_uniq, /*!< in: 0 or index->n_uniq */
    dtuple_t*   entry,  /*!< in/out: index entry to insert */
    ulint       n_ext,  /*!< in: number of externally stored columns */
    que_thr_t*  thr,    /*!< in: query thread */
    bool        dup_chk_only)
                /*!< in: if true, just do duplicate check
                and return. don't execute actual insert. */
{
    btr_pcur_t  pcur;
    btr_cur_t*  cursor;
    dberr_t     err     = DB_SUCCESS;
    big_rec_t*  big_rec     = NULL;
    mtr_t       mtr;
    mem_heap_t* offsets_heap    = NULL;
    ulint           offsets_[REC_OFFS_NORMAL_SIZE];
    ulint*          offsets         = offsets_;
    rec_offs_init(offsets_);

    /* Note that we use PAGE_CUR_LE as the search mode, because then
    the function will return in both low_match and up_match of the
    cursor sensible values */
    //調(diào)用btr_cur_search_to_nth_level 查詢索引樹腻贰，將cursor移動(dòng)到記錄相應(yīng)的位置
    btr_pcur_open(index, entry, PAGE_CUR_LE, mode, &pcur, &mtr); 
    cursor = btr_pcur_get_btr_cur(&pcur);
    cursor->thr = thr;

    /* Allowing duplicates in clustered index is currently enabled
    only for intrinsic table and caller understand the limited
    operation that can be done in this case. */
    if (!index->allow_duplicates // 是否允許重復(fù)
        && n_uniq //唯一索引的字段數(shù)量
        && (cursor->up_match >= n_uniq || cursor->low_match >= n_uniq)) { //是否存在唯一索引沖突,up_match與low_match可以先簡(jiǎn)單理解為記錄前后兩條記錄與它相等的字段數(shù)量
           //滿足了這幾個(gè)條件需要進(jìn)入row_ins_duplicate_error_in_clust
        if (flags
            == (BTR_CREATE_FLAG | BTR_NO_LOCKING_FLAG
            | BTR_NO_UNDO_LOG_FLAG | BTR_KEEP_SYS_FLAG)) {
        } else {
            /* Note that the following may return also
            DB_LOCK_WAIT */

            err = row_ins_duplicate_error_in_clust(
                flags, cursor, entry, thr, &mtr);
        }
    }
}

進(jìn)入row_ins_duplicate_error_in_clust后分幾種場(chǎng)景：
1）唯一鍵沖突，沖突的記錄是未提交事務(wù)insert的扒秸。
2）唯一鍵沖突播演，沖突的記錄是未提交事務(wù)delete。
3）唯一鍵沖突伴奥，沖突的記錄是已提交的事務(wù)写烤。
4）唯一鍵沖突，沖突的記錄是已提交拾徙，但是delete-mark的未被purge洲炊。
5）唯一鍵沖突，沖突的記錄是自身事務(wù)delete-mark的記錄尼啡。
1)
row_ins_duplicate_error_in_clust
----row_ins_set_shared_rec_lock
--------lock_clust_rec_read_check_and_lock
------------lock_rec_convert_impl_to_expl
----------------判斷沖突的記錄事務(wù)是活躍的,調(diào)用下面函數(shù)給記錄加鎖,沖突記錄的事務(wù)持有
----------------lock_rec_convert_impl_to_expl_for_trx
--------------------!trx_state_eq(trx, TRX_STATE_COMMITTED_IN_MEMORY) && !lock_rec_has_expl(LOCK_X |LOCK_REC_NOT_GAP, block, heap_no, trx))
--------------------判斷事務(wù)狀態(tài)不為TRX_STATE_COMMITTED_IN_MEMORY并且沒有顯式鎖
--------------------lock_rec_add_to_queue
----------給沖突的記錄加鎖,本事務(wù)持有
-----------lock_rec_lock
---------------lock_rec_lock_fast or lock_rec_lock_slow
2)
  
----row_ins_set_shared_rec_lock
--------lock_clust_rec_read_check_and_lock
------------lock_rec_convert_impl_to_expl
----------------判斷沖突的記錄事務(wù)是活躍的,調(diào)用下面函數(shù)給記錄加鎖,沖突記錄的事務(wù)持有
----------------lock_rec_convert_impl_to_expl_for_trx
--------------------!trx_state_eq(trx, TRX_STATE_COMMITTED_IN_MEMORY) && !lock_rec_has_expl(LOCK_X |LOCK_REC_NOT_GAP, block, heap_no, trx))
--------------------因?yàn)閐elete有鎖,不需要加鎖
----------給沖突的記錄加鎖,本事務(wù)持有
-----------lock_rec_lock
---------------lock_rec_lock_fast or lock_rec_lock_slow
3)
row_ins_duplicate_error_in_clust
----row_ins_set_shared_rec_lock
--------lock_clust_rec_read_check_and_lock
------------lock_rec_convert_impl_to_expl
----------------判斷沖突的記錄事務(wù)是提交的,無(wú)需針對(duì)該記錄給其他事務(wù)加鎖
----------給沖突記錄加鎖本事務(wù)持有
-----------lock_rec_lock
---------------lock_rec_lock_fast or lock_rec_lock_slow
----判斷是否是DB_DUPLICATE_KEY
----row_ins_dupl_error_with_rec
報(bào)錯(cuò)duplicate key但是仍然持有鎖
死鎖案例:https://mp.weixin.qq.com/s/RleocRPvK67aTJqbDXeICw,同樣的場(chǎng)景可復(fù)現(xiàn)于聚簇索引暂衡。
4)待復(fù)現(xiàn)
5)
row_ins_clust_index_entry
----row_ins_clust_index_entry_low
--------row_ins_set_shared_rec_lock
------------lock_clust_rec_read_check_and_lock
----------------lock_rec_convert_impl_to_expl
--------------------lock_rec_convert_impl_to_expl_for_trx
--------------------因?yàn)閐elete有鎖,所以不需要加鎖
---------row_ins_duplicate_error_in_clust
------------row_ins_set_shared_rec_lock
----------------lock_clust_rec_read_check_and_lock
--------------------lock_rec_lock
------------------------lock_rec_lock_slow
----------------------------lock_rec_has_expl
----------------------------因?yàn)槭聞?wù)自身已經(jīng)持有了鎖,不需要加鎖
--------row_ins_clust_index_entry_by_modify
------------btr_cur_optimistic_update
----------------btr_cur_update_in_place
--------------------btr_cur_upd_lock_and_undo
------------------------lock_clust_rec_modify_check_and_lock
案例：https://zhuanlan.zhihu.com/p/52098868

二級(jí)索引

1）唯一鍵沖突，沖突的記錄是未提交事務(wù)insert的崖瞭。
2）唯一鍵沖突狂巢，沖突的記錄是未提交事務(wù)delete。
3）唯一鍵沖突书聚，沖突的記錄是已提交的事務(wù)唧领。
4）唯一鍵沖突藻雌，沖突的記錄是已提交，但是delete-mark的未被purge斩个。
5）唯一鍵沖突胯杭，沖突的記錄是自身事務(wù)delete-mark的記錄。

待續(xù)
Replace into受啥、insert on duplicate update與普通insert執(zhí)行上的差異歉摧。
http://mysql.taobao.org/monthly/2015/03/01/
下面這個(gè)案例在5.7.28以及8.0上都沒復(fù)現(xiàn)出來(lái)。
https://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ==&mid=2648450891&idx=1&sn=eb2736289b129abbaade2568d2114d4d&chksm=f3c97fa1c4bef6b7ce5ae09919a9a387a776b41c3dc41a4e2966e22791a7bdbd140b38ffddd0&scene=21%23wechat_redirect

案例

沖突監(jiān)測(cè)時(shí)加的鎖與記錄排他鎖沖突,下面案例中場(chǎng)景發(fā)生于聚簇索引與唯一二級(jí)索引腔呜。
https://mp.weixin.qq.com/s/RleocRPvK67aTJqbDXeICw
唯一二級(jí)索引沖突監(jiān)測(cè)需要獲取沖突記錄下一條記錄的鎖叁温，導(dǎo)致的鎖等待。
https://zhuanlan.zhihu.com/p/52098868
插入意向鎖與沖突監(jiān)測(cè)時(shí)加的鎖沖突,這種場(chǎng)景只會(huì)發(fā)生于唯一二級(jí)索引核畴。
https://mp.weixin.qq.com/s?__biz=MzU2NzgwMTg0MA==&mid=2247484177&idx=1&sn=03916542bbfd8262811142c1db39a5b7&chksm=fc96e18ecbe168983b4c1fd3807948905d38429065dd3d7c112e0bbad329bcc3710c38d1ecc9&scene=21%23wechat_redirect
聚簇索引與唯一二級(jí)索引之間相互持有導(dǎo)致沖突膝但。
https://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ==&mid=2648450895&idx=1&sn=c3241c472a059370673e499dccb1fb0b&chksm=f3c97fa5c4bef6b3f2d85f77feb4b335f2f0cfcc18a755c5c2fa781e097b189d530b0f5614a5&scene=21%23wechat_redirect

自增鎖
https://www.cnblogs.com/code-007/p/7729569.html
Insert into ... select
https://www.cnblogs.com/zhoujinyi/archive/2013/04/28/3049382.html
https://mp.weixin.qq.com/s/HbRrvQwW_QmKlxhZG5x0Xw
https://www.cnblogs.com/code-007/p/7729132.html

參考：
https://www.aneasystone.com/archives/2018/06/insert-locks-via-mysql-source-code.html
http://mysql.taobao.org//monthly/2016/01/01/