Insert into 加锁机制

关于Mysql innodb Insert into 加锁的机制的文章网上很少，个人对于insert 的加锁机制比较感兴趣，所以通过此wiki对研究的过程做个总结，如有不对的地方，欢迎指正。

我先把官方文档对于insert 加锁的描述贴出来

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. 

大体的意思是：insert会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非next-key锁（当然更不是gap锁了），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁（简称I锁吧），并发的事务可以对同一个gap加I锁。如果insert 的事务出现了duplicate-key error ，事务会对duplicate index record加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的insert都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的insert操作是会发生死锁的。

 

---

关于插入意向锁：

从mysql的insert 加锁的源码可以看出,insert 插入的时候是用的是LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION （这就是插入意向锁）去检查插入的gap，这个锁模式是与LOCK_S | LOCK_GAP，LOCK_X | LOCK_GAP锁模式冲突的，但对于相同的gap，两个锁模式为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，是兼容的。

/*********************************************************************//**
Checks if locks of other transactions prevent an immediate insert of
a record. If they do, first tests if the query thread should anyway
be suspended for some reason; if not, then puts the transaction and
the query thread to the lock wait state and inserts a waiting request
for a gap x-lock to the lock queue.
@return	DB_SUCCESS, DB_LOCK_WAIT, DB_DEADLOCK, or DB_QUE_THR_SUSPENDED */
UNIV_INTERN
ulint
lock_rec_insert_check_and_lock(
/*===========================*/
	ulint		flags,	/*!< in: if BTR_NO_LOCKING_FLAG bit is
				set, does nothing */
	const rec_t*	rec,	/*!< in: record after which to insert */
	buf_block_t*	block,	/*!< in/out: buffer block of rec */
	dict_index_t*	index,	/*!< in: index */
	que_thr_t*	thr,	/*!< in: query thread */
	mtr_t*		mtr,	/*!< in/out: mini-transaction */
	ibool*		inherit)/*!< out: set to TRUE if the new
				inserted record maybe should inherit
				LOCK_GAP type locks from the successor
				record */
{
	const rec_t*	next_rec;
	trx_t*		trx;
	lock_t*		lock;
	ulint		err;
	ulint		next_rec_heap_no;
	ut_ad(block->frame == page_align(rec));
	if (flags & BTR_NO_LOCKING_FLAG) {
		return(DB_SUCCESS);
	}
	trx = thr_get_trx(thr);
	next_rec = page_rec_get_next_const(rec);
	next_rec_heap_no = page_rec_get_heap_no(next_rec);
	lock_mutex_enter_kernel();
	/* When inserting a record into an index, the table must be at
	least IX-locked or we must be building an index, in which case
	the table must be at least S-locked. */
	ut_ad(lock_table_has(trx, index->table, LOCK_IX)
	      || (*index->name == TEMP_INDEX_PREFIX
		  && lock_table_has(trx, index->table, LOCK_S)));
	lock = lock_rec_get_first(block, next_rec_heap_no);
	if (UNIV_LIKELY(lock == NULL)) {
		/* We optimize CPU time usage in the simplest case */
		lock_mutex_exit_kernel();
		if (!dict_index_is_clust(index)) {
			/* Update the page max trx id field */
			page_update_max_trx_id(block,
					       buf_block_get_page_zip(block),
					       trx->id, mtr);
		}
		*inherit = FALSE;
		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,
		    block, next_rec_heap_no, trx)) {
		/* Note that we may get DB_SUCCESS also here! */
		err = lock_rec_enqueue_waiting(LOCK_X | LOCK_GAP
					       | LOCK_INSERT_INTENTION,
					       block, next_rec_heap_no,
					       NULL, index, thr);
	} else {
		err = DB_SUCCESS;
	}
	lock_mutex_exit_kernel();
	switch (err) {
	case DB_SUCCESS_LOCKED_REC:
		err = DB_SUCCESS;
		/* fall through */
	case DB_SUCCESS:
		if (dict_index_is_clust(index)) {
			break;
		}
		/* Update the page