小晋学习之触发器加锁顺序 元宵特别篇（20220215）

触发器

create trigger  trigger_name 

　　before | after　　insert | update | delete

　　on 表名 for each row

　　trigger_body

触发器有六种:　触发时机before(之前)和after(之后), 触发动作insert | update | delete

本片主要测试before after 和操作本表的加锁顺序

create table t1
(
    id int primary key auto_increment,
    number int    
)engine innodb default charset utf8mb4;

create table t2
(
    id int primary key auto_increment,
    number int    
)engine innodb default charset utf8mb4;

create table t3
(
    id int primary key auto_increment,
    number int    
)engine innodb default charset utf8mb4;

以上创建了三个表

create trigger trig1 after insert on t1 for each row insert into t2 values(new.id,new.number);

create trigger trig2 before insert on t1 for each row insert into t3 values(new.id,new.number);

执行

insert into table t1 values(1,1);

查看binlog

BEGIN
/*!*/;
# at 592491774
#220215 11:17:19 server id 5768  end_log_pos 592491830 CRC32 0xe3ef5da9 	Table_map: `xjclassroom`.`t1` mapped to number 127
# at 592491830
#220215 11:17:19 server id 5768  end_log_pos 592491886 CRC32 0xf79ab859 	Table_map: `xjclassroom`.`t3` mapped to number 128
# at 592491886
#220215 11:17:19 server id 5768  end_log_pos 592491942 CRC32 0xbf6fd6b6 	Table_map: `xjclassroom`.`t2` mapped to number 129
# at 592491942
#220215 11:17:19 server id 5768  end_log_pos 592491986 CRC32 0x0b4ca2fa 	Write_rows: table id 128
# at 592491986
#220215 11:17:19 server id 5768  end_log_pos 592492030 CRC32 0x420d21d7 	Write_rows: table id 127
# at 592492030
#220215 11:17:19 server id 5768  end_log_pos 592492074 CRC32 0xc3b0c77f 	Write_rows: table id 129 flags: STMT_END_F

BINLOG '
PxsLYhOIFgAAOAAAADa1UCMAAH8AAAAAAAEAC3hqY2xhc3Nyb29tAAJ0MQACAwMAAgEBAKld7+M=
PxsLYhOIFgAAOAAAAG61UCMAAIAAAAAAAAEAC3hqY2xhc3Nyb29tAAJ0MwACAwMAAgEBAFm4mvc=
PxsLYhOIFgAAOAAAAKa1UCMAAIEAAAAAAAEAC3hqY2xhc3Nyb29tAAJ0MgACAwMAAgEBALbWb78=
PxsLYh6IFgAALAAAANK1UCMAAIAAAAAAAAAAAgAC/wABAAAAAQAAAPqiTAs=
PxsLYh6IFgAALAAAAP61UCMAAH8AAAAAAAAAAgAC/wABAAAAAQAAANchDUI=
PxsLYh6IFgAALAAAACq2UCMAAIEAAAAAAAEAAgAC/wABAAAAAQAAAH/HsMM=
'/*!*/;
### INSERT INTO `xjclassroom`.`t3`
### SET
###   @1=1
###   @2=1
### INSERT INTO `xjclassroom`.`t1`
### SET
###   @1=1
###   @2=1
### INSERT INTO `xjclassroom`.`t2`
### SET
###   @1=1
###   @2=1
# at 592492074
#220215 11:17:19 server id 5768  end_log_pos 592492105 CRC32 0x97299b1c 	Xid = 1074
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

	事务1	事务2
-	t3 添加 share 锁
-		insert into t1

查看锁情况，只能查到t3表的相关锁，S锁（记录），IS锁（表），IX锁(表），等待X锁（记录）

	事务1	事务2
-	t1 添加 share 锁
-		insert into t1

查看锁情况，t3 IX锁（表），t1 S锁（记录），等待X锁（记录），IX锁（表）

此时在事务1中，给t3加S锁，变成死锁

	事务1	事务2
-	t2 添加 share 锁
-		insert into t1

查看锁情况，t3 IX锁（表），t1 S锁（记录），IX锁（表），t2 IX锁（表），X锁（记录）

在触发器中，执行的顺序应该是 t3 t1 t2
以上的测试表明，整个加锁顺序也是t3 t1 t2，并不是同时加锁。

delimiter ;;
CREATE TRIGGER trig2 AFTER 
  UPDATE ON t1 FOR EACH ROW 
  begin 
      declare x int;
      declare y int;
      -- 延后触发器插入
      set x =sleep(10);
      replace into t2 values(new.id,new.number,new.un);
      set y = sleep(10);
  END;;
delimiter ;