MySQL Lock--MySQL INSERT加锁学习
准备测试数据：
## 开启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; INSERT INTO tb1001(order_id,order_num,order_type) VALUES(19,20,10)
上面操作执行后，使用SHOW ENGINE INNODB STATUS查看锁信息
---TRANSACTION 1454597, ACTIVE 353 sec 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 83, OS thread handle 140361075443456, query id 293 127.0.0.1 admin TABLE LOCK table `db001`.`tb1001` trx id 1454597 lock mode IX RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table `db001`.`tb1001` trx id 1454597 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000013; asc ;; 1: len 6; hex 000000163205; asc 2 ;; 2: len 7; hex a50000001c0110; asc ;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 8000000a; asc ;;
上面事务加两个锁：
1、表上加意向修改锁(IX)。
2、在新插入的记录上加行锁(RECORD LOCKS ..lock_mode X locks rec but not gap)
测试2：
## 先执行事务A但不提交 BEGIN; INSERT INTO tb1001(order_id,order_num,order_type) VALUES(19,20,10) ## 先执行事务B ## 事务B被阻塞 BEGIN; INSERT INTO tb1001(order_id,order_num,order_type) VALUES(19,20,10)
上面操作执行后，使用SHOW ENGINE INNODB STATUS查看锁信息
---TRANSACTION 1454599, ACTIVE 4 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 82, OS thread handle 140361075709696, query id 335 127.0.0.1 admin update INSERT INTO tb1001(order_id,order_num,order_type) VALUES(19,20,10) ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table `db001`.`tb1001` trx id 1454599 lock mode S waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000013; asc ;; 1: len 6; hex 000000163205; asc 2 ;; 2: len 7; hex a50000001c0110; asc ;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 8000000a; asc ;; ------------------ TABLE LOCK table `db001`.`tb1001` trx id 1454599 lock mode IX RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table `db001`.`tb1001` trx id 1454599 lock mode S waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000013; asc ;; 1: len 6; hex 000000163205; asc 2 ;; 2: len 7; hex a50000001c0110; asc ;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 8000000a; asc ;; ---TRANSACTION 1454597, ACTIVE 590 sec 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 83, OS thread handle 140361075443456, query id 293 127.0.0.1 admin TABLE LOCK table `db001`.`tb1001` trx id 1454597 lock mode IX RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table `db001`.`tb1001` trx id 1454597 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000013; asc ;; 1: len 6; hex 000000163205; asc 2 ;; 2: len 7; hex a50000001c0110; asc ;; 3: len 4; hex 80000014; asc ;; 4: len 4; hex 8000000a; asc ;;
查看事务锁信息：
SELECT * FROM `information_schema`.`INNODB_LOCKS` \G *************************** 1. row *************************** lock_id: 1454606:29:3:7 lock_trx_id: 1454606 lock_mode: S lock_type: RECORD lock_table: `db001`.`tb1001` lock_index: PRIMARY lock_space: 29 lock_page: 3 lock_rec: 7 lock_data: 19 *************************** 2. row *************************** lock_id: 1454605:29:3:7 lock_trx_id: 1454605 lock_mode: X lock_type: RECORD lock_table: `db001`.`tb1001` lock_index: PRIMARY lock_space: 29 lock_page: 3 lock_rec: 7 lock_data: 19
绿色表示锁申请完成，黄色表示申请锁失败被阻塞。
加锁详解：
Insert操作加锁规则 1、INSERT操作会对新插入的记录加行锁(ROW LOCK)+排他锁(X LOCK),不会产生任何GAP锁和Next-Key锁 2、在插入记录前，会向插入记录所在位置申请意向插入Gap锁(Insertion Intention Gap LOCK),相同区间的意向插入Gap锁不会冲突。 3、对于唯一索引，如果插入记录时表中已存在相同键值记录(被其他事务修改且未提交)，即存在唯一键冲突，会尝试在已有记录上加读锁，然后等待。 加锁操作： 1、事务A插入操作完成，对新插入记录加行锁(ROW LOCK)+排他锁(X LOCK)，且不会任何加GAP锁，因此锁信息为： RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454605 lock_mode X LOCKS rec but NOT gap Record LOCK, HEAP NO 7 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 80000013; ASC ;; order_id=19 2、事务B执行插入操作，由于在记录order_id=19上存在唯一键冲突，因此改为申请记录order_id=19上的S锁，又由于事务A上持有记录order_id=19上的行锁(ROW LOCK)+排他锁(X LOCK)，事务B申请S锁被阻塞，锁信息为： RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454606 LOCK MODE S waiting Record LOCK, HEAP NO 7 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 80000013; ASC ;; order_id=19
