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，是兼容的。

[cpp] view plain copy

1. /*********************************************************************//** 
2. Checks if locks of other transactions prevent an immediate insert of 
3. a record. If they do, first tests if the query thread should anyway 
4. be suspended for some reason; if not, then puts the transaction and 
5. the query thread to the lock wait state and inserts a waiting request 
6. for a gap x-lock to the lock queue. 
7. @return DB_SUCCESS, DB_LOCK_WAIT, DB_DEADLOCK, or DB_QUE_THR_SUSPENDED */  
8. UNIV_INTERN  
9. ulint  
10. lock_rec_insert_check_and_lock(  
11. /*===========================*/  
12.     ulint       flags,  /*!< in: if BTR_NO_LOCKING_FLAG bit is 
13.                 set, does nothing */  
14.     const rec_t*    rec,    /*!< in: record after which to insert */  
15.     buf_block_t*    block,  /*!< in/out: buffer block of rec */  
16.     dict_index_t*   index,  /*!< in: index */  
17.     que_thr_t*  thr,    /*!< in: query thread */  
18.     mtr_t*      mtr,    /*!< in/out: mini-transaction */  
19.     ibool*      inherit)/*!< out: set to TRUE if the new 
20.                 inserted record maybe should inherit 
21.                 LOCK_GAP type locks from the successor 
22.                 record */  
23. {  
24.     const rec_t*    next_rec;  
25.     trx_t*      trx;  
26.     lock_t*     lock;  
27.     ulint       err;  
28.     ulint       next_rec_heap_no;  
29.     ut_ad(block->frame == page_align(rec));  
30.     if (flags & BTR_NO_LOCKING_FLAG) {  
31.         return(DB_SUCCESS);  
32.     }  
33.     trx = thr_get_trx(thr);  
34.     next_rec = page_rec_get_next_const(rec);  
35.     next_rec_heap_no = page_rec_get_heap_no(next_rec);  
36.     lock_mutex_enter_kernel();  
37.     /* When inserting a record into an index, the table must be at 
38.     least IX-locked or we must be building an index, in which case 
39.     the table must be at least S-locked. */  
40.     ut_ad(lock_table_has(trx, index->table, LOCK_IX)  
41.           || (*index->name == TEMP_INDEX_PREFIX  
42.           && lock_table_has(trx, index->table, LOCK_S)));  
43.     lock = lock_rec_get_first(block, next_rec_heap_no);  
44.     if (UNIV_LIKELY(lock == NULL)) {  
45.         /* We optimize CPU time usage in the simplest case */  
46.         lock_mutex_exit_kernel();  
47.         if (!dict_index_is_clust(index)) {  
48.             /* Update the page max trx id field */  
49.             page_update_max_trx_id(block,  
50.                            buf_block_get_page_zip(block),  
51.                            trx->id, mtr);  
52.         }  
53.         *inherit = FALSE;  
54.         return(DB_SUCCESS);  
55.     }  
56.     *inherit = TRUE;  
57.     /* If another transaction has an explicit lock request which locks 
58.     the gap, waiting or granted, on the successor, the insert has to wait. 
59.     An exception is the case where the lock by the another transaction 
60.     is a gap type lock which it placed to wait for its turn to insert. We 
61.     do not consider that kind of a lock conflicting with our insert. This 
62.     eliminates an unnecessary deadlock which resulted when 2 transactions 
63.     had to wait for their insert. Both had waiting gap type lock requests 
64.     on the successor, which produced an unnecessary deadlock. */  
65.     if (lock_rec_other_has_conflicting(  
66.             LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,  
67.             block, next_rec_heap_no, trx)) {  
68.         /* Note that we may get DB_SUCCESS also here! */  
69.         err = lock_rec_enqueue_waiting(LOCK_X | LOCK_GAP  
70.                            | LOCK_INSERT_INTENTION,  
71.                            block, next_rec_heap_no,  
72.                            NULL, index, thr);  
73.     } else {  
74.         err = DB_SUCCESS;  
75.     }  
76.     lock_mutex_exit_kernel();  
77.     switch (err) {  
78.     case DB_SUCCESS_LOCKED_REC:  
79.         err = DB_SUCCESS;  
80.         /* fall through */  
81.     case DB_SUCCESS:  
82.         if (dict_index_is_clust(index)) {  
83.             break;  
84.         }  
85.         /* Update the page max trx id field */  
86.         page_update_max_trx_id(block,  
87.                        buf_block_get_page_zip(block),  
88.                        trx->id, mtr);  
89.     }  
90. #ifdef UNIV_DEBUG  
91.     {  
92.         mem_heap_t* heap        = NULL;  
93.         ulint       offsets_[REC_OFFS_NORMAL_SIZE];  
94.         const ulint*    offsets;  
95.         rec_offs_init(offsets_);  
96.         offsets = rec_get_offsets(next_rec, index, offsets_,  
97.                       ULINT_UNDEFINED, &heap);  
98.         ut_ad(lock_rec_queue_validate(block,  
99.                           next_rec, index, offsets));  
100.         if (UNIV_LIKELY_NULL(heap)) {  
101.             mem_heap_free(heap);  
102.         }  
103.     }  
104. #endif /* UNIV_DEBUG */  
105.     return(err);  
106. }  

下面通过几个场景我们看看insert的具体加锁的机制。

演示表如下：

[sql] view plain copy

1. CREATE TABLE `tt` (  
2.   `a` int(11) NOT NULL AUTO_INCREMENT,  
3.   `b` int(11) DEFAULT NULL,  
4.   PRIMARY KEY (`a`),  
5.   KEY `idx_b` (`b`)  
6. ) ENGINE=InnoDB  

tt表中插入一些数据insert into tt values(1,8),(2,3),(3,4),(4,1),(5,12);

场景一：

	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> select * from tt where b = 5 for update; Empty set (0.00 sec)
3		mysql> insert into tt(b) values(6); 锁等待。。
4	commit;
		Query OK, 1 row affected (14.92 sec)

我们看看第3步innodb锁状态，9D53488C是事务1，9D5348C0是事务2，事务1的锁很容易理解，select for update，数据表中没有b=5的记录，所以加的是X gap锁，锁住的间隙是(4,8)，事务2发生了锁等待。事务2锁等待是因为事务1对（4，8）加上了排它锁（锁模式为X，GAP），是会阻塞事务2的I锁，事务2发生等待，因此INNODB_LOCKS表中显示的lock_mode是X和GAP，lock_type是行级锁。

（关于INNODB_LOCKS和INNODB_LOCK_WAITS字段意义可以参考information_schema中Innodb相关表用于分析sql查询锁的使用情况介绍），

场景二：两个并发插入到相同gap不同的记录

	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> insert into tt(b) values(6); Query OK, 1 row affected (0.04 sec)
4	commit;	commit;

这个场景证明，对于同一个gap，I锁是不冲突的，事务1和事务2没有锁等待，都插入成功。

场景三：演示对插入的记录加的排它锁

	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> select * from tt where b >4 and b <8 lock in share mode; 锁等待
4	commit;
		+----+------+ | a | b | +----+------+ | 12 | 5 | +----+------+ 1 row in set (6.90 sec) commit;

事务1对应的是9D5CD9A9 事务2对应的是9D5CD9F8，事务2发生了锁等待，通过innodb_locks,可以看出事务2要等待的锁的类型是S gap锁（没弄明白这里为什么不是显示的gap锁），加锁的间隙是（4，8），这个锁被事务1的X锁组塞，所以可以确认insert插入后是会加排它锁，这里可以通过修改事务2的语句，确定出insert 插入后加的是记录锁（这里就不列出具体的演示场景了）。

场景四：演示下insert 的事务出现了duplicate-key error的情况

演示前先tt表的b字段改成unique key。

	事务1	事务2	事务3
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql>  insert into tt(b) values(5); 锁等待	mysql>  insert into tt(b) values(5); 锁等待
4	mysql> rollback; Query OK, 0 rows affected (0.00 sec)
		mysql> insert into tt(b) values(5); Query OK, 1 row affected (37.17 sec)	mysql> insert into tt(b) values(5); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 死锁发生了

先看看3的时候，锁的状态：

9D961CAD是事务1 ，9D960FC9和9D960FD9分别是事务2和3，从innodb_locks表中可以看出事务1是X记录锁，事务2和3是S记录锁，且这三个锁对应的是同样的记录，从innodb_lock_waits表可以看出事务2和事务3 的S锁被事务1 的X锁阻塞了。

当事务1 rollback后，事务2和事务3发生死锁。通过show engine innodb status查看死锁日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
150109 9:59:59
*** (1) TRANSACTION:
TRANSACTION 9D96295F, ACTIVE 19 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 1675150, OS thread handle 0x7f5181977700, query id 1001786133 192.168.148.68 q3boy update
insert into tt(b) values(5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48562 page no 4 n bits 80 index `ux_b` of table `testdg`.`tt` trx id 9D960FD9 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 9D962A68, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 1675251, OS thread handle 0x7f518055e700, query id 1001790623 192.168.148.68 q3boy update
insert into tt(b) values(5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 48562 page no 4 n bits 80 index `ux_b` of table `testdg`.`tt` trx id 9D960FC9 lock mode S locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 48562 page no 4 n bits 80 index `ux_b` of table `testdg`.`tt` trx id 9D962A68 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 4; hex 80000001; asc ;;

从上面死锁日志，我们可以很容易理解死锁为何发生。事务1插入记录，事务2插入同一条记录，主键冲突，事务2将事务1的隐式锁转为显式锁，同时事务2向队列中加入一个s锁请求;

事务3同样也加入一个s锁请求；

当事务1回滚后，事务2和事务3获得s锁，但随后事务2和事务3又先后请求插入意向锁，因此锁队列为：

事务2(S GAP)<—事务3(S GAP)<—事务2(插入意向锁)<–事务3(插入意向锁)   事务3,事务2,事务3形成死锁。