准备测试数据：
## 开启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)，申请成功，