MySQL Lock--MySQL加锁学习2

准备测试数据：

## 开启InnoDB Monitor
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

## 创建测试表
DROP TABLE IF EXISTS tb1001;

CREATE TABLE `tb1001` (
  `order_id` INT(11) NOT NULL,
  `order_num` INT(11) DEFAULT NULL,
  `order_type` INT(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_order_type` (`order_type`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

## 准备测试数据
INSERT INTO tb1001(order_id,order_num,order_type)
VALUES(10,10,10),(20,10,20),(21,10,20),(30,10,30),(40,10,40);


## 查看当前表数据
SELECT * FROM tb1001;
+----------+-----------+------------+
| order_id | order_num | order_type |
+----------+-----------+------------+
|       10 |        10 |         10 |
|       20 |        10 |         20 |
|       21 |        10 |         20 |
|       30 |        10 |         30 |
|       40 |        10 |         40 |
+----------+-----------+------------+

 

测试1：

## 先执行事务A
BEGIN;
SELECT * FROM tb1001 WHERE order_id=20 FOR UPDATE;


## 再执行事务B
## 事务B执行成功，未被阻塞
BEGIN;
INSERT INTO tb1001(order_id,order_num,order_type)
VALUES(19,20,10)

 

使用SHOW ENGINE INNODB STATUS 查看，输出锁信息为：

---TRANSACTION 1454155, ACTIVE 15 sec
## BEGIN;
## INSERT INTO tb1001(order_id,order_num,order_type)
## VALUES(19,20,10)

1 LOCK struct(s), HEAP size 1136, 0 ROW LOCK(s), UNDO LOG entries 1
MySQL thread id 1475203, OS thread handle 139581473105664, QUERY id 4426152 127.0.0.1 admin
TABLE LOCK TABLE `db001`.`tb1001` trx id 1454155 LOCK MODE IX


---TRANSACTION 1454154, ACTIVE 23 sec
## SQL
## BEGIN;
## SELECT * FROM tb1001 WHERE order_id=20 FOR UPDATE;

2 LOCK struct(s), HEAP size 1136, 1 ROW LOCK(s)
MySQL thread id 1475204, OS thread handle 139581472573184, QUERY id 4426150 127.0.0.1 admin
TABLE LOCK TABLE `db001`.`tb1001` trx id 1454154 LOCK MODE IX
RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454154 lock_mode X LOCKS rec but NOT gap
Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0
 0: len 4; HEX 80000014; ASC     ;;
 1: len 6; HEX 00000016303e; ASC     0>;;
 2: len 7; HEX f600000032011c; ASC     2  ;;
 3: len 4; HEX 8000000a; ASC     ;;
 4: len 4; HEX 80000014; ASC     ;;

 

加锁详解：

## SQL
## BEGIN;
## SELECT * FROM tb1001 WHERE order_id=20 FOR UPDATE;

在可重复读事务隔离级别下,由于 WHERE order_id=20 需要对主键做等值查询，使用加锁规则：
1、如果找到到满足条件的记录，则对该记录加行锁。
2、如果未找到满足条件的记录，则对该值前后两条记录之间间隙加间隙锁。

加锁操作如下：
1、找到满足条件记录order_id=20，对记录order_id=10行锁,锁信息为：
    RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454154 lock_mode X LOCKS rec but NOT gap
    Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0
    0: len 4; HEX 80000014; ASC     ;; order_id=20


## SQL 
## BEGIN;
## insert into tb1001(order_id,order_num,order_type)
## values(19,20,10)

INSERT加锁规则如下：
1、INSERT操作会对新插入的记录加行锁(ROW LOCK)+排他锁(X LOCK),不会产生任何GAP锁和Next-Key锁
2、在插入记录前，会向插入记录所在位置申请意向插入Gap锁(Insertion Intention Gap LOCK),相同区间的意向插入Gap锁不会冲突。
3、对于唯一索引，如果插入记录时表中已存在相同键值记录(被其他事务修改且未提交)，即存在唯一键冲突，会尝试在已有记录上加读锁，然后等待。

加锁操作如下：
1、对表tb1001做数据插入操作，需要对表tb001上申请意向锁(TABLE LOCK TABLE `db001`.`tb1001` trx id 1454153 LOCK MODE IX)
2、向新记录order_id=19所在位置申请插入Gap锁(Insertion Intention Gap LOCK)，申请成功，