show engine innodb status 关于锁信息的解读

mysql版本 5.7.20 开启了
隔离级别为rc。
表的创建语句为:
Create Table: CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
数据为:
| dept_no | dept_name          |
+---------+--------------------+
| d001    | Marketing          |
| d002    | Finance            |
| d003    | Human Resources    |
| d004    | Production         |
| d005    | Development        |
| d006    | Quality Management |
| d008    | Research           |
| d009    | Customer Service   |
+---------+--------------------+
8 rows in set (0.00 sec)
 
我开启了innodb 的lock monitor。执行了两个会话。
session1 :
begin;
delete from departments where dept_name='sales';
session2:
begin:
delete from departments where dept_name='Marketing';
 
这个时候会话2 被阻塞。show engine innodb status 关于transaction 如下:
TRANSACTIONS
------------
Trx id counter 1222951
Purge done for trx's n:o < 1222951 undo n:o < 0 state: running but idle
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422191746035312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1222946, ACTIVE 4 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 140716622202624, query id 54 localhost root updating
delete from departments where dept_name='Marketing'
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 381 page no 3 n bits 88 index PRIMARY of table `employees`.`departments` trx id 1222946 lock_mode X locks rec but not gap waiting
Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 64303037; asc d007;;
 1: len 6; hex 00000012a920; asc       ;;
 2: len 7; hex 38000003770dbd; asc 8   w  ;;
 3: len 5; hex 53616c6573; asc Sales;;

------------------
TABLE LOCK table `employees`.`departments` trx id 1222946 lock mode IX
RECORD LOCKS space id 381 page no 3 n bits 88 index PRIMARY of table `employees`.`departments` trx id 1222946 lock_mode X locks rec but not gap
Record lock, heap no 19 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 64303031; asc d001;;
 1: len 6; hex 00000012a922; asc      ";;
 2: len 7; hex 3a0000035018df; asc :   P  ;;
 3: len 9; hex 4d61726b6574696e67; asc Marketing;;

RECORD LOCKS space id 381 page no 3 n bits 88 index PRIMARY of table `employees`.`departments` trx id 1222946 lock_mode X locks rec but not gap waiting
Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 64303037; asc d007;;
 1: len 6; hex 00000012a920; asc       ;;
 2: len 7; hex 38000003770dbd; asc 8   w  ;;
 3: len 5; hex 53616c6573; asc Sales;;

---TRANSACTION 1222944, ACTIVE 330 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140716621936384, query id 41 localhost root
TABLE LOCK table `employees`.`departments` trx id 1222944 lock mode IX
RECORD LOCKS space id 381 page no 3 n bits 88 index PRIMARY of table `employees`.`departments` trx id 1222944 lock_mode X locks rec but not gap
Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 64303037; asc d007;;
 1: len 6; hex 00000012a920; asc       ;;
 2: len 7; hex 38000003770dbd; asc 8   w  ;;
 3: len 5; hex 53616c6573; asc Sales;;
 
这里会话的record locks信息如何解释。这里的RECORD LOCKS space id 381 page no 3 n bits 88如何解释?
已邀请:

card_2005

赞同来自:

RECORD LOCKS space id 381 page no 3 n bits 88
space id:表空间号
page no : 页号
n bits: 这个是lock bitmap的大小,单位bit

要回复问题请先登录注册