MySQL Lock--MySQL加锁学习1
准备测试数据：
## 开启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<=10 FOR UPDATE; ## 再执行事务B ## 事务B被阻塞 BEGIN; INSERT INTO tb1001(order_id,order_num,order_type) VALUES(19,20,10)
锁阻塞信息如下：
SELECT * FROM `information_schema`.`INNODB_LOCKS` \G *************************** 1. row *************************** lock_id: 1454153:29:3:3 lock_trx_id: 1454153 lock_mode: X,GAP lock_type: RECORD lock_table: `db001`.`tb1001` lock_index: PRIMARY lock_space: 29 lock_page: 3 lock_rec: 3 lock_data: 20 *************************** 2. row *************************** lock_id: 1454152:29:3:3 lock_trx_id: 1454152 lock_mode: X lock_type: RECORD lock_table: `db001`.`tb1001` lock_index: PRIMARY lock_space: 29 lock_page: 3 lock_rec: 3 lock_data: 20 SELECT * FROM `information_schema`.`INNODB_LOCK_WAITS` \G *************************** 1. row *************************** requesting_trx_id: 1454153 requested_lock_id: 1454153:29:3:3 blocking_trx_id: 1454152 blocking_lock_id: 1454152:29:3:3
绿色部分表示申请锁成功，黄色部分表示申请锁被阻塞。
使用SHOW ENGINE INNODB STATUS 查看，输出锁信息为：
---TRANSACTION 1454152, ACTIVE 38 sec ## SQL: ## BEGIN; ## SELECT * FROM tb1001 WHERE order_id<=10 FOR UPDATE; 2 LOCK struct(s), HEAP size 1136, 2 ROW LOCK(s) MySQL thread id 1475204, OS thread handle 139581472573184, QUERY id 4425986 127.0.0.1 admin TABLE LOCK TABLE `db001`.`tb1001` trx id 1454152 LOCK MODE IX RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454152 lock_mode X Record LOCK, HEAP NO 2 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 8000000a; ASC ;; order_id=10 1: len 6; HEX 00000016303e; ASC 0>;; 2: len 7; HEX f6000000320110; ASC 2 ;; 3: len 4; HEX 8000000a; ASC ;; 4: len 4; HEX 8000000a; ASC ;; Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 80000014; ASC ;; order_id=20 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 ;; ---TRANSACTION 1454153, ACTIVE 17 sec inserting ## SQL: ## BEGIN; ## INSERT INTO tb1001(order_id,order_num,order_type) ## VALUES(19,20,10); mysql TABLES IN USE 1, locked 1 LOCK WAIT 2 LOCK struct(s), HEAP size 1136, 1 ROW LOCK(s) MySQL thread id 1475203, OS thread handle 139581473105664, QUERY id 4425988 127.0.0.1 admin UPDATE INSERT INTO tb1001(order_id,order_num,order_type) VALUES(19,20,10) ------- TRX HAS BEEN WAITING 17 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 1454153 lock_mode X LOCKS gap BEFORE rec INSERT intention waiting Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 80000014; ASC ;; order_id=20 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 ;; ------------------ TABLE LOCK TABLE `db001`.`tb1001` trx id 1454153 LOCK MODE IX RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454153 lock_mode X LOCKS gap BEFORE rec INSERT intention waiting Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 80000014; ASC ;; order_id=20 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<=10 FOR UPDATE; 在可重复读事务隔离级别下,由于 WHERE order_id<=10 需要对主键做范围扫描，使用加锁规则： 1、对满足条件的记录加Next-key锁。 2、从左向右扫描满足条件的记录，当遇到第一条不满足条件记录时，对该记录和该记录之前的间隙加锁(NEXT-KEY LOCK)。 加锁操作如下 1、当扫描到记录order_id=10时，满足条件，对记录order_id=10加Next-key锁,锁信息为：
RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454152 lock_mode X Record LOCK, HEAP NO 2 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0 0: len 4; HEX 8000000a; ASC ;; order_id=10 2、继续扫描到记录order_id=20时，第一条不满足条件的，对记录order_id=20加Next-key锁，锁信息为：
RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454152 lock_mode X 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)，但由于上面事务对记录order_id=20加Next-key锁，申请失败处于等待状态(lock_mode X LOCKS gap BEFORE rec INSERT intention waiting),等待锁信息为：
Record LOCK, HEAP NO 3 PHYSICAL RECORD: n_fields 5; COMPACT FORMAT; info bits 0
0: len 4; HEX 80000014; ASC ;; order_id=20
GAP LOCK /RECORD LOCK /NEXT KEY LOCK
在输出的锁信息中，如果仅对记录加行锁且未对记录前空间加GAP锁，则锁信息为：
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
在输出的锁信息中，如果记录加行锁且对记录前空间加GAP锁，则锁信息为：
RECORD LOCKS SPACE id 29 page NO 3 n bits 80 INDEX PRIMARY of TABLE `db001`.`tb1001` trx id 1454152 lock_mode X
未显式指明"but NOT GAP"的RECORD LOCK实际上NEXT KEY LOCK。
在输出的锁信息中，如果未对记录加锁且仅对记录前空间加GAP锁，则锁信息为：
RECORD LOCKS space id 29 page no 3 n bits 80 index PRIMARY of table `db001`.`tb1001` trx id 1454596 lock_mode X locks gap before rec
