Innodb-Insert-锁
2020-11-22 本文已影响0人
多血
锁类型
元数据锁
表锁
IX
自增锁
自增锁模式通过参数innodb_autoinc_lock_mode来控制，加锁选择参阅函数ha_innobase::innobase_lock_autoinc
聚集索引或唯一索引检查到唯一键冲突时加的锁（标记删除的也算）
- 锁类型
对于普通的INSERT/UPDATE，会加LOCK_S锁，而对于类似REPLACE INTO或者INSERT … ON DUPLICATE这样的SQL加的是X锁。 - 锁方式
对于聚集索引加的是LOCK_REC_NOT_GAP类似的S或者X记录锁。
对于二级唯一索引，若检查到重复键，当前版本总是加 LOCK_ORDINARY 类型的记录锁。
插入意向锁
执行 insert 语句，判断是否有和插入意向锁冲突的锁，如果有，加插入意向锁，进入锁等待；如果没有，直接写数据，不加任何锁；
insert ... select 情况
INSERT … SELECT插入数据时，会对SELECT的表上扫描到的数据加LOCK_S锁。
加锁顺序
ha_innobase::write_row
----row_insert_for_mysql
--------row_insert_for_mysql_using_ins_graph
------------row_ins_step
----------------lock_table 加表级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 //调用btr_cur_search_to_nth_level 查询索引树，将cursor移动到待插入记录相应的位置
--------如果判断存在唯一冲突,进入下面函数加锁
--------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
------------如果判断存在唯一冲突,进入下面函数加锁
------------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
唯一键冲突时的加锁场景
聚簇索引
/***************************************************************//**
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 */
//调用btr_cur_search_to_nth_level 查询索引树，将cursor移动到记录相应的位置
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 // 是否允许重复
&& n_uniq //唯一索引的字段数量
&& (cursor->up_match >= n_uniq || cursor->low_match >= n_uniq)) { //是否存在唯一索引冲突,up_match与low_match可以先简单理解为记录前后两条记录与它相等的字段数量
//满足了这几个条件需要进入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);
}
}
}
进入row_ins_duplicate_error_in_clust后分几种场景：
1）唯一键冲突，冲突的记录是未提交事务insert的。
2）唯一键冲突，冲突的记录是未提交事务delete。
3）唯一键冲突，冲突的记录是已提交的事务。
4）唯一键冲突，冲突的记录是已提交，但是delete-mark的未被purge。
5）唯一键冲突，冲突的记录是自身事务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
----------------判断冲突的记录事务是活跃的,调用下面函数给记录加锁,冲突记录的事务持有
----------------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))
--------------------判断事务状态不为TRX_STATE_COMMITTED_IN_MEMORY并且没有显式锁
--------------------lock_rec_add_to_queue
----------给冲突的记录加锁,本事务持有
-----------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
----------------判断冲突的记录事务是活跃的,调用下面函数给记录加锁,冲突记录的事务持有
----------------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))
--------------------因为delete有锁,不需要加锁
----------给冲突的记录加锁,本事务持有
-----------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
----------------判断冲突的记录事务是提交的,无需记录加锁
----------给自身记录加锁
-----------lock_rec_lock
---------------lock_rec_lock_fast or lock_rec_lock_slow
----判断是否是DB_DUPLICATE_KEY
----row_ins_dupl_error_with_rec
报错duplicate key但是仍然持有锁
死锁案例:https://mp.weixin.qq.com/s/RleocRPvK67aTJqbDXeICw,同样的场景可复现于聚簇索引。
4)待复现
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
--------------------因为delete有锁,所以不需要加锁
---------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
----------------------------因为事务自身已经持有了锁,不需要加锁
--------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
二级索引
1）唯一键冲突，冲突的记录是未提交事务insert的。
2）唯一键冲突，冲突的记录是未提交事务delete。
3）唯一键冲突，冲突的记录是已提交的事务。
4）唯一键冲突，冲突的记录是已提交，但是delete-mark的未被purge。
5）唯一键冲突，冲突的记录是自身事务delete-mark的记录。
待续
Replace into、insert on duplicate update与普通insert执行上的差异。
http://mysql.taobao.org/monthly/2015/03/01/
https://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ==&mid=2648450891&idx=1&sn=eb2736289b129abbaade2568d2114d4d&chksm=f3c97fa1c4bef6b7ce5ae09919a9a387a776b41c3dc41a4e2966e22791a7bdbd140b38ffddd0&scene=21%23wechat_redirect
案例
冲突监测时加的锁与记录排他锁冲突,下面案例中场景发生于聚簇索引与唯一二级索引。
https://mp.weixin.qq.com/s/RleocRPvK67aTJqbDXeICw
唯一二级索引冲突监测需要获取冲突记录下一条记录的锁，导致的锁等待。
https://zhuanlan.zhihu.com/p/52098868
插入意向锁与冲突监测时加的锁冲突,这种场景只会发生于唯一二级索引。
https://mp.weixin.qq.com/s?__biz=MzU2NzgwMTg0MA==&mid=2247484177&idx=1&sn=03916542bbfd8262811142c1db39a5b7&chksm=fc96e18ecbe168983b4c1fd3807948905d38429065dd3d7c112e0bbad329bcc3710c38d1ecc9&scene=21%23wechat_redirect
聚簇索引与唯一二级索引之间相互持有导致冲突。
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/
