MySQL Lock--MySQL INSERT加锁学习

Dear 丶 2021-10-20 01:26 289阅读 0赞

准备测试数据：

## 开启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

转载于:https://www.cnblogs.com/gaogao67/p/11059619.html