测试数据
------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(11) default NULL,
`COMMENT` varchar(11) default NULL,
PRIMARY KEY (`ID`),
KEY `user_ix1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+------+---------+
| ID | name | COMMENT |
+----+------+---------+
| 20 | 22 | 20 |
| 25 | 25 | 25 |
| 30 | 30 | 30 |
+----+------+---------+
3 rows in set (0.00 sec)
session 1
begin；
select * from user where name=22 for update;--这个是类型错误，导致没有用索引，所以索引上没有锁
session 2
insert into user values(27,'27','27');
插入操作的堆栈
lock_rec_has_to_wait(ulint lock_is_on_supremum, ib_lock_t * lock2, ulint type_mode, trx_t * trx) (/root/mysql-5.0.15/innobase/lock/lock0lock.c:957)
lock_rec_other_has_conflicting(ulint mode, rec_t * rec, trx_t * trx) (/root/mysql-5.0.15/innobase/lock/lock0lock.c:1598)
lock_rec_insert_check_and_lock(ulint flags, rec_t * rec, dict_index_t * index, que_thr_t * thr, ulint * inherit) (/root/mysql-5.0.15/innobase/lock/lock0lock.c:4754)
btr_cur_ins_lock_and_undo(ulint * inherit, que_thr_t * thr, dtuple_t * entry, btr_cur_t * cursor, ulint flags) (/root/mysql-5.0.15/innobase/btr/btr0cur.c:859)
btr_cur_optimistic_insert(ulint flags, btr_cur_t * cursor, dtuple_t * entry, rec_t ** rec, big_rec_t ** big_rec, que_thr_t * thr, mtr_t * mtr) (/root/mysql-5.0.15/innobase/btr/btr0cur.c:1021)
row_ins_index_entry_low(ulint mode, dict_index_t * index, dtuple_t * entry, ulint * ext_vec, ulint n_ext_vec, que_thr_t * thr) (/root/mysql-5.0.15/innobase/row/row0ins.c:2057)
row_ins_index_entry(dict_index_t * index, dtuple_t * entry, ulint * ext_vec, ulint n_ext_vec, que_thr_t * thr) (/root/mysql-5.0.15/innobase/row/row0ins.c:2135)
row_ins_index_entry_step(que_thr_t * thr, ins_node_t * node) (/root/mysql-5.0.15/innobase/row/row0ins.c:2214)
row_ins(ins_node_t * node, que_thr_t * thr) (/root/mysql-5.0.15/innobase/row/row0ins.c:2346)
row_ins_step(que_thr_t * thr) (/root/mysql-5.0.15/innobase/row/row0ins.c:2450)
row_insert_for_mysql(unsigned char * mysql_rec, row_prebuilt_t * prebuilt) (/root/mysql-5.0.15/innobase/row/row0mysql.c:1141)
ha_innobase::write_row(ha_innobase * const this, mysql_byte * record) (/root/mysql-5.0.15/sql/ha_innodb.cc:3371)
write_record(THD * thd, TABLE * table, COPY_INFO * info) (/root/mysql-5.0.15/sql/sql_insert.cc:1146)
mysql_insert(THD * thd, TABLE_LIST * table_list, List<Item> & fields, List<List<Item> > & values_list, List<Item> & update_fields, List<Item> & update_values, enum_duplicates duplic, bool ignore) (/root/mysql-5.0.15/sql/sql_insert.cc:531)
mysql_execute_command(THD * thd) (/root/mysql-5.0.15/sql/sql_parse.cc:3239)
mysql_parse(THD * thd, char * inBuf, uint length) (/root/mysql-5.0.15/sql/sql_parse.cc:5536)
dispatch_command(enum_server_command command, THD * thd, char * packet, unsigned int packet_length) (/root/mysql-5.0.15/sql/sql_parse.cc:1697)
do_command(THD * thd) (/root/mysql-5.0.15/sql/sql_parse.cc:1498)
handle_one_connection(void * arg) (/root/mysql-5.0.15/sql/sql_parse.cc:1143)
libpthread.so.0!start_thread (Unknown Source:0)
在lock_rec_insert_check_and_lock 中先获取下一个记录，然后看下一个记录上是否有锁，判断是否需要等待
next_rec = page_rec_get_next(rec);
*inherit = FALSE;
lock_mutex_enter_kernel();
ut_ad(lock_table_has(thr_get_trx(thr), index->table, LOCK_IX));
下一个记录的锁
lock = lock_rec_get_first(next_rec);
下一个记录没有锁，直接执行了
if (lock == NULL) {
/* We optimize CPU time usage in the simplest case */
lock_mutex_exit_kernel();
if (!(index->type & DICT_CLUSTERED)) {
/* Update the page max trx id field */
page_update_max_trx_id(buf_frame_align(rec),
thr_get_trx(thr)->id);
}
return(DB_SUCCESS);
}
*inherit = TRUE;
/* If another transaction has an explicit lock request which locks
the gap, waiting or granted, on the successor, the insert has to wait.
An exception is the case where the lock by the another transaction
is a gap type lock which it placed to wait for its turn to insert. We
do not consider that kind of a lock conflicting with our insert. This
eliminates an unnecessary deadlock which resulted when 2 transactions
had to wait for their insert. Both had waiting gap type lock requests
on the successor, which produced an unnecessary deadlock. */
下一个记录有锁，判断是否需要进行等待
if (lock_rec_other_has_conflicting(LOCK_X | LOCK_GAP
| LOCK_INSERT_INTENTION, next_rec, trx)) {
/* Note that we may get DB_SUCCESS also here! */
err = lock_rec_enqueue_waiting(LOCK_X | LOCK_GAP
| LOCK_INSERT_INTENTION,
next_rec, index, thr);
} else {
err = DB_SUCCESS;
}
lock_rec_other_has_conflicting 中
lock = lock_rec_get_first(rec);
while (lock) {
判断是否需要等待
if (lock_rec_has_to_wait(trx, mode, lock,
page_rec_is_supremum(rec))) {
return(lock);
}
lock = lock_rec_get_next(rec, lock);
}
所以我们的测试中插入等待的是下一个记录的锁，也就是记录25.
因为25上的记录锁是gap锁，所以insert被阻塞，我们可以看到因为是没有走索引，导致在primary key上加锁，
并且将supremum这个伪纪录也加上了锁。
在输出中都是primary index上的锁，没有secondary 上的锁 如果session执行的是select * from user where name='20' for update走索引，则锁的情况是，是在二级索引上的范围锁，2个字段，一个主键，一个name字段
LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 8964, ACTIVE 9 sec, process no 108919, OS thread id 140737276618496 2 lock struct(s), heap size 368 MySQL thread id 1, query id 7 localhost root TABLE LOCK table `test/user` trx id 0 8964 lock mode IX RECORD LOCKS space id 0 page no 51 n bits 72 index `user_ix1` of table `test/user` trx id 0 8964 lock_mode X locks gap before rec Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 2; hex 3232; asc 22;; 1: len 4; hex 80000014; asc ;;
对应的堆栈是
lock_rec_lock(unsigned long impl, unsigned long mode, rec_t * rec, dict_index_t * index, que_thr_t * thr) (/root/mysql-5.0.15/innobase/lock/lock0lock.c:2150) lock_sec_rec_read_check_and_lock(ulint flags, rec_t * rec, dict_index_t * index, const ulint * offsets, unsigned long mode, unsigned long gap_mode, que_thr_t * thr) (/root/mysql-5.0.15/innobase/lock/lock0lock.c:5003) sel_set_rec_lock(rec_t * rec, dict_index_t * index, const ulint * offsets, unsigned long mode, unsigned long type, que_thr_t * thr) (/root/mysql-5.0.15/innobase/row/row0sel.c:784) row_search_for_mysql(unsigned char * buf, ulint mode, row_prebuilt_t * prebuilt, ulint match_mode, ulint direction) (/root/mysql-5.0.15/innobase/row/row0sel.c:3653) ha_innobase::index_read(ha_innobase * const this, mysql_byte * buf, const mysql_byte * key_ptr, uint key_len, ha_rkey_function find_flag) (/root/mysql-5.0.15/sql/ha_innodb.cc:3917) join_read_always_key(JOIN_TAB * tab) (/root/mysql-5.0.15/sql/sql_select.cc:9788) sub_select(JOIN * join, JOIN_TAB * join_tab, bool end_of_records) (/root/mysql-5.0.15/sql/sql_select.cc:9292) do_select(JOIN * join, List<Item> * fields, TABLE * table, Procedure * procedure) (/root/mysql-5.0.15/sql/sql_select.cc:9056) JOIN::exec(JOIN * const this) (/root/mysql-5.0.15/sql/sql_select.cc:1669) mysql_select(THD * thd, Item *** rref_pointer_array, TABLE_LIST * tables, uint wild_num, List<Item> & fields, COND * conds, uint og_num, ORDER * order, ORDER * group, Item * having, ORDER * proc_param, ulong select_options, select_result * result, SELECT_LEX_UNIT * unit, SELECT_LEX * select_lex) (/root/mysql-5.0.15/sql/sql_select.cc:1833) handle_select(THD * thd, LEX * lex, select_result * result, ulong setup_tables_done_option) (/root/mysql-5.0.15/sql/sql_select.cc:246) mysql_execute_command(THD * thd) (/root/mysql-5.0.15/sql/sql_parse.cc:2484) mysql_parse(THD * thd, char * inBuf, uint length) (/root/mysql-5.0.15/sql/sql_parse.cc:5536) dispatch_command(enum_server_command command, THD * thd, char * packet, unsigned int packet_length) (/root/mysql-5.0.15/sql/sql_parse.cc:1697) do_command(THD * thd) (/root/mysql-5.0.15/sql/sql_parse.cc:1498) handle_one_connection(void * arg) (/root/mysql-5.0.15/sql/sql_parse.cc:1143) libpthread.so.0!start_thread (Unknown Source:0) libc.so.6!clone (Unknown Source:0)
在row_search_for_mysql中，加lock_gap锁，先是搜索记录，找到记录后dml或锁命令开始加锁， 根据函数的输入参数以及参数设置判断是否需要加GAP锁 err = sel_set_rec_lock(rec, index, offsets, prebuilt->select_lock_type, LOCK_GAP, thr);
那么gap锁是怎么加上的，继续看sel_set_rec_lock函数，在这个函数中有对cluster index的加锁和对二级索引的加锁
if (index->type & DICT_CLUSTERED) {
err = lock_clust_rec_read_check_and_lock(0,
rec, index, offsets, mode, type, thr);
} else {
err = lock_sec_rec_read_check_and_lock(0,
rec, index, offsets, mode, type, thr);
}
看看对二级索引的加锁lock_sec_rec_read_check_and_lock
lock_sec_rec_read_check_and_lock( /*=============================*/ /* out: DB_SUCCESS, DB_LOCK_WAIT, DB_DEADLOCK, or DB_QUE_THR_SUSPENDED */ ulint flags, /* in: if BTR_NO_LOCKING_FLAG bit is set, does nothing */ rec_t* rec, /* in: user record or page supremum record which should be read or passed over by a read cursor */ dict_index_t* index, /* in: secondary index */ const ulint* offsets,/* in: rec_get_offsets(rec, index) */ ulint mode, /* in: mode of the lock which the read cursor should set on records: LOCK_S or LOCK_X; the latter is possible in SELECT FOR UPDATE */ ulint gap_mode,/* in: LOCK_ORDINARY, LOCK_GAP, or LOCK_REC_NOT_GAP */ que_thr_t* thr) /* in: query thread */
注意函数的mode参数和gap_mode参数，mode代表是lock_s,lock_x,gap_mode 是表示next key lock 还是普通gap lock 还是仅仅记录上的锁，先看看二级索引上是否有隐式的锁，有就转换成显式锁
if (((ut_dulint_cmp(page_get_max_trx_id(buf_frame_align(rec)),
trx_list_get_min_trx_id()) >= 0)
|| recv_recovery_is_on())
&& !page_rec_is_supremum(rec)) {
lock_rec_convert_impl_to_expl(rec, index, offsets);
}
//此时的mode|gap_mode合并
err = lock_rec_lock(FALSE, mode | gap_mode, rec, index, thr);
继续看lock_rec_lock函数，这个函数在指定行上加指定mode的锁
if (lock_rec_lock_fast(impl, mode, rec, index, thr)) {
/* We try a simplified and faster subroutine for the most
common cases */
err = DB_SUCCESS;
} else {
err = lock_rec_lock_slow(impl, mode, rec, index, thr);
}
大部分情况是lock_rec_lock_fast,所以我们看这个函数
//获取记录的heap_no
heap_no = rec_get_heap_no(rec, page_rec_is_comp(rec));
//获取page上的第一个锁，page上有bitmap,bitmap记录每行的锁信息
lock = lock_rec_get_first_on_page(rec);
trx = thr_get_trx(thr);
if (lock == NULL) {
if (!impl) {
// page上一个锁也没有创建一个锁对象
lock_rec_create(mode, rec, index, trx);
if (srv_locks_unsafe_for_binlog) {
trx_register_new_rec_lock(trx, index);
}
}
// 有，就设置对应的bit，lock_rec_set_nth_bit(lock, heap_no);
if (!impl) {
/* If the nth bit of the record lock is already set then we
do not set a new lock bit, otherwise we do set */
if (!lock_rec_get_nth_bit(lock, heap_no)) {
lock_rec_set_nth_bit(lock, heap_no);
if (srv_locks_unsafe_for_binlog) {
trx_register_new_rec_lock(trx, index);
}
}
我们看下新建锁的情况，type_mode 就是锁的模式，mode, gap_mode的组合
lock_rec_create( /*============*/ /* out: created lock, NULL if out of memory */ ulint type_mode,/* in: lock mode and wait flag, type is ignored and replaced by LOCK_REC */ rec_t* rec, /* in: record on page */ dict_index_t* index, /* in: index of record */ trx_t* trx) /* in: transaction */
//新建锁对象
lock = mem_heap_alloc(trx->lock_heap, sizeof(lock_t) + n_bytes);
if (UNIV_UNLIKELY(lock == NULL)) {
return(NULL);
}
UT_LIST_ADD_LAST(trx_locks, trx->trx_locks, lock);
lock->trx = trx;
// 设置所模式
lock->type_mode = (type_mode & ~LOCK_TYPE_MASK) | LOCK_REC;
lock->index = index;
lock->un_member.rec_lock.space = space;
lock->un_member.rec_lock.page_no = page_no;
lock->un_member.rec_lock.n_bits = n_bytes * 8;
/* Reset to zero the bitmap which resides immediately after the
lock struct */
lock_rec_bitmap_reset(lock);
//设置记录对应的bit
/* Set the bit corresponding to rec */
lock_rec_set_nth_bit(lock, heap_no);
//将锁插入hash
HASH_INSERT(lock_t, hash, lock_sys->rec_hash,
lock_rec_fold(space, page_no), lock);
//如果锁是等待，设置锁的等待flag
if (type_mode & LOCK_WAIT) {
lock_set_lock_and_trx_wait(lock, trx);
}
从上面的过程中我们可以看到gap锁的实现其实也是记录锁，只不是是通过lock->type_mode去区分锁是记录锁还是gap还是next key锁等待的判断
static
ibool
lock_has_to_wait(
/*=============*/
/* out: TRUE if lock1 has to wait for lock2 to be
removed */
lock_t* lock1, /* in: waiting lock */
lock_t* lock2) /* in: another lock; NOTE that it is assumed that this
has a lock bit set on the same record as in lock1 if
the locks are record locks */
{
ut_ad(lock1 && lock2);
if (lock1->trx != lock2->trx
&& !lock_mode_compatible(lock_get_mode(lock1),
lock_get_mode(lock2))) {
//是记录锁，不兼容，进入记录锁的判断逻辑
if (lock_get_type(lock1) == LOCK_REC) {
ut_ad(lock_get_type(lock2) == LOCK_REC);
/* If this lock request is for a supremum record
then the second bit on the lock bitmap is set */
return(lock_rec_has_to_wait(lock1->trx,
lock1->type_mode, lock2,
lock_rec_get_nth_bit(lock1,1)));
}
return(TRUE);
}
return(FALSE);
}
// 锁的类型有LOCK_TABLE 表锁，LOCK_REC 记录锁，锁的模式有 LOCK_ORDINARY 就是 next-key lock
// LOCK_GAP gap锁，LOCK_REC_NOT_GAP 只是记录锁 LOCK_INSERT_INTENTION 插入意向锁？
lock_rec_has_to_wait(
/*=================*/
/* out: TRUE if new lock has to wait for lock2 to be
removed */
trx_t* trx, /* in: trx of new lock */
ulint type_mode,/* in: precise mode of the new lock to set:
LOCK_S or LOCK_X, possibly ORed to
LOCK_GAP or LOCK_REC_NOT_GAP, LOCK_INSERT_INTENTION */
lock_t* lock2, /* in: another record lock; NOTE that it is assumed
that this has a lock bit set on the same record as
in the new lock we are setting */
ibool lock_is_on_supremum) /* in: TRUE if we are setting the lock
on the 'supremum' record of an index
page: we know then that the lock request
is really for a 'gap' type lock */
{
ut_ad(trx && lock2);
ut_ad(lock_get_type(lock2) == LOCK_REC);
fprintf(stderr," lock2 type mode is %lu",lock_get_mode(lock2));
if (trx != lock2->trx
&& !lock_mode_compatible(LOCK_MODE_MASK & type_mode,
lock_get_mode(lock2))) {
/* We have somewhat complex rules when gap type record locks
cause waits */
// 在锁不兼容的情况下，有下面几个情况是不需等待的
// 是supremum上的锁，或者是gap lock，并且锁没有LOCK_INSERT_INTENTION flag，不需要等待
//不是insert导致的gap锁，不需要等待，不看lock2的信息
if ((lock_is_on_supremum || (type_mode & LOCK_GAP))
&& !(type_mode & LOCK_INSERT_INTENTION)) {
/* Gap type locks without LOCK_INSERT_INTENTION flag
do not need to wait for anything. This is because
different users can have conflicting lock types
on gaps. */
return(FALSE);
}
// 不是insert产生的非gap锁，并且lock2上有gap也是不需要等待，唯一索引update，delete不需要等待，即使lock2有gap锁，这个跟我们测试结果是一致的，
if (!(type_mode & LOCK_INSERT_INTENTION)
&& lock_rec_get_gap(lock2)) {
/* Record lock (LOCK_ORDINARY or LOCK_REC_NOT_GAP
does not need to wait for a gap type lock */
return(FALSE);
}
// lock2不是gap锁，请求的是gap锁，不需要等待
if ((type_mode & LOCK_GAP)
&& lock_rec_get_rec_not_gap(lock2)) {
/* Lock on gap does not need to wait for
a LOCK_REC_NOT_GAP type lock */
return(FALSE);
}
if (lock_rec_get_insert_intention(lock2)) {
/* No lock request needs to wait for an insert
intention lock to be removed. This is ok since our
rules allow conflicting locks on gaps. This eliminates
a spurious deadlock caused by a next-key lock waiting
for an insert intention lock; when the insert
intention lock was granted, the insert deadlocked on
the waiting next-key lock.
Also, insert intention locks do not disturb each
other. */
return(FALSE);
}
// 其他条件都等待
return(TRUE);
}
return(FALSE);
}
关于显示锁以及隐式锁 隐式锁 这个目的是减少锁对象的创建，能节省资源，但是在锁等待情况下会将 隐式锁转化成显示锁，这种的是针对insert的优化，在没有锁等待的情况下，有资源节省，高并发争用下存在这种转化，性能上肯定有消耗，为什么不直接用空间换时间？对隐式锁的理解还是不太明白
An explicit record lock affects both the record and the gap before it. An implicit x-lock does not affect the gap, it only locks the index record from read or update. If a transaction has modified or inserted an index record, then it owns an implicit x-lock on the record. On a secondary index record, a transaction has an implicit x-lock also if it has modified the clustered index record, the max trx id of the page where the secondary index record resides is >= trx id of the transaction (or database recovery is running), and there are no explicit non-gap lock requests on the secondary index record.
经过测试update是显示锁，在锁监控里能输出行锁信息，在insert中是隐事锁，看不到记录锁信息，只能看到ix信息 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 10002, ACTIVE 142 sec, process no 5929, OS thread id 140737276618496 1 lock struct(s), heap size 368, undo log entries 1 MySQL thread id 4, query id 42 localhost root TABLE LOCK table `test/user` trx id 0 10002 lock mode IX
隐式锁没有锁对象，那么怎么判断是否有隐式锁？在cluster index上，获取记录的trx_id 是活跃的就有隐事锁
trx_t*
lock_clust_rec_some_has_impl(
/*=========================*/
/* out: transaction which has the x-lock, or
NULL */
rec_t* rec, /* in: user record */
dict_index_t* index, /* in: clustered index */
const ulint* offsets)/* in: rec_get_offsets(rec, index) */
{
dulint trx_id;
#ifdef UNIV_SYNC_DEBUG
ut_ad(mutex_own(&kernel_mutex));
#endif /* UNIV_SYNC_DEBUG */
ut_ad(index->type & DICT_CLUSTERED);
ut_ad(page_rec_is_user_rec(rec));
trx_id = row_get_rec_trx_id(rec, index, offsets);
if (trx_is_active(trx_id)) {
/* The modifying or inserting transaction is active */
return(trx_get_on_id(trx_id));
}
return(NULL);
}
二级索引通过页上的max trx id判断
