mysql 锁死的行怎么解锁 mysql锁操作

背景

看微博上丁奇大牛转发的一条微博，看作者分析的过程有疑问，Insert操作会有插入意向锁的判断，文中没有提到， 故查看源码试着分析一下。

问题描述1

CREATE TABLE `z` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `b` (`b`)
) ENGINE=InnoDB
INSERT INTO z (id, b)
VALUES (1,2),
(3,4),
(5,6),
(7,8),
(9,10);
/* session A */
begin;
select * from z where b=6 for update;
/* session B*/
insert into z values(0,4); /*blocked*/
insert into z values(-1,4); /*success*/
insert into z values(4,8); /*blocked*/
insert into z values(8,8); /*success*/

文中提到的(id=5,b=6)前面有Gap Lock这点觉得有疑问。

Innodb打印出的锁信息

设置参数开启Innodb打印SQL加锁详情1

2set global innodb_status_output_locks=1;

set global innodb_status_output=1;

在RR模式下运行session A1

## select * from z where b=6 for update
4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 1, OS thread handle 0x7fff65ce9700, query id 15 localhost root cleaning up
TABLE LOCK table `test`.`z` trx id 102152 lock mode IX
### 索引b上的Next-key
RECORD LOCKS space id 57 page no 4 n bits 80 index `b` of table `test`.`z` trx id 102152 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;; ### b字段，值为6
1: len 4; hex 80000005; asc ;; ### 主键id字段，值为5
### 主键索引上记录锁
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`z` trx id 102152 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;; ### 主键id字段，值为5
1: len 6; hex 00000001890b; asc ;; ### 事务id，此行最近一次被更新的事务id，占6个字节
2: len 7; hex 8b0000013d0128; asc = (;; ### 回滚段指针DB_ROLL_PTR，用于MVCC
3: len 4; hex 80000006; asc ;; ### b字段，值为6
### 索引b上的GAP锁
RECORD LOCKS space id 57 page no 4 n bits 80 index `b` of table `test`.`z` trx id 102152 lock_mode X 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 ;; ## b字段，值为8
1: len 4; hex 80000007; asc ;; ## 主键id，值为7

可以看出此SQL语句加锁为索引b上的(4,8) = (4,6]的Next-key锁，(6,8)之间的GAP锁，主键上[5]。

这里就是疑问点。

在RR模式下运行session B

插入(0,4)1

---TRANSACTION 102153, ACTIVE 689 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7fff65ca8700, query id 26 localhost root update
insert into z values(0,4)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 4 n bits 80 index `b` of table `test`.`z` trx id 102153 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000005; asc ;;
------------------
TABLE LOCK table `test`.`z` trx id 102153 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `b` of table `test`.`z` trx id 102153 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000005; asc ;;

可以看出sessionB阻塞的原因是插入意向锁，等待b索引上(b=6,id=5)之前的GAP锁。

插入(2,8)1

6insert into z values(2,8)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 4 n bits 80 index `b` of table `test`.`z` trx id 102698 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 80000007; asc ;;

等待b索引上(b=8,id=7)之前的GAP锁。

[space, page_no] 可以确定锁对应哪个页，参考下上个月月报最后两个小节，页上每行数据紧接着存放，内部使用一个 heap_no 来表示是第几行数据。因此[space, page_no, heap_no]可以唯一确定一行。

尽管都是同一个page，但是heap_no不同。

Insert Debug调试

Insert操作的锁判断

给lock_rec_insert_check_and_lock函数添加断点。

0/*********************************************************************//**
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
dberr_t
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 */
{
...
//当前insert记录的逻辑下一条记录
next_rec = page_rec_get_next_const(rec);
next_rec_heap_no = page_rec_get_heap_no(next_rec);
...
//判断当前记录的下一条逻辑记录值上是否存在GAP锁或者插入意向锁
//如果存在，返回DB_LOCK_WAIT，当前insert进行等待队列
/* 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(
¦ static_cast(
¦ LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION),
¦ block, next_rec_heap_no, trx)) {
/* Note that we may get DB_SUCCESS also here! */
trx_mutex_enter(trx);
err = lock_rec_enqueue_waiting(
LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,
block, next_rec_heap_no, index, thr);
trx_mutex_exit(trx);
} else {
err = DB_SUCCESS;
}
...

两条Insert操作对应的下一条记录情况

insert into z values(0,4)1

(gdb) p *next_rec
$5 = 128 '\200'
(gdb) p next_rec_heap_no
$6 = 4
TABLE LOCK table `test`.`z` trx id 102153 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `b` of table `test`.`z` trx id 102153 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000005; asc ;;
打印这个下一行其加锁信息：
1
2(gdb)p lock->un_member->rec_lock
$21 = {space = 57, page_no = 4, n_bits = 80}
在没有加锁的情况，此语句插入的记录是(id=10,b=4)，而不是(id=0,b=4)。这是因为主键是Auto_Increment。
insert into z values(-1,4)1
2
3
4
5
6(gdb) p next_rec
$7 = (const rec_t *) 0x7fff6e00808c "\200"
(gdb) p *next_rec
$8 = 128 '\200'
(gdb) p next_rec_heap_no
$9 = 3

这里就是为啥插入(-1,4)记录不会阻塞，而(0,4)会阻塞的根本原因。

思考插入意向锁作用是啥？