mysql show engine innodb status lock锁学习

在mysql中遇到锁的情况，看不懂锁的情况，可以进行学下
#show engine innodb status
TRANSACTION 1091557054, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 1136, 23 row lock(s)
MySQL thread id 15432888, OS thread handle 139828380456704, query id 1297000886 172.16.57.35 dolphinscheduler Sending data
insert into t_ds_alert(sign, title, content, alert_status, warning_type, log, alertgroup_id, create_time, update_time, alert_type)
        SELECT 'ab98610a6ab608ed64c68cf7e1cc6e698f2d40d5', 'Fault tolerance warning', '[{"type":"WORKER","host":"/nodes/worker/dolphinscheduler-worker-2.dolphinscheduler-worker-headless:1234","event":"SERVER_DOWN","warningLevel":"SERIOUS"}]', 0, 2,
               null, 1, '2023-08-04 08:31:17.6', '2023-08-04 08:31:17.6', 4
        from t_ds_alert
        where create_time >= '2023-08-04 07:31:17.6' and sign = 'ab98610a6ab608ed64c68cf7e1cc6e698f2d40d5' and alert_status = 0
        having count(*) = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `dolphinscheduler`.`t_ds_alert` trx id 1091557054 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 1091557052, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1136, 24 row lock(s), undo log entries 1
MySQL thread id 15432932, OS thread handle 139828372735744, query id 1297000883 172.16.57.38 dolphinscheduler Sending data
insert into t_ds_alert(sign, title, content, alert_status, warning_type, log, alertgroup_id, create_time, update_time, alert_type)
        SELECT 'ab98610a6ab608ed64c68cf7e1cc6e698f2d40d5', 'Fault tolerance warning', '[{"type":"WORKER","host":"/nodes/worker/dolphinscheduler-worker-2.dolphinscheduler-worker-headless:1234","event":"SERVER_DOWN","warningLevel":"SERIOUS"}]', 0, 2,
               null, 1, '2023-08-04 08:31:17.599', '2023-08-04 08:31:17.599', 4
        from t_ds_alert
        where create_time >= '2023-08-04 07:31:17.599' and sign = 'ab98610a6ab608ed64c68cf7e1cc6e698f2d40d5' and alert_status = 0
        having count(*) = 0
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `dolphinscheduler`.`t_ds_alert` trx id 1091557052 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 91755 page no 5 n bits 136 index idx_sign of table `dolphinscheduler`.`t_ds_alert` trx id 1091557052 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 24 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 633862366232616434663739623035633136633462633162646430373165; asc c8b6b2ad4f79b05c16c4bc1bdd071e; (total 40 bytes);
 1: len 4; hex 80000017; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

这段日志是MySQL数据库的事务日志，描述了一个正在执行的事务以及它所涉及的锁和等待情况。下面对其中的关键部分进行逐条解释：

1. TRANSACTION 1091557054, ACTIVE 0 sec setting auto-inc lock: 这是一个事务的ID和状态信息。事务ID为1091557054，表示当前事务处于活动状态，已经设置了自增锁。

2. mysql tables in use 2, locked 2: 当前事务正在使用2个表，并对这2个表进行了锁定。

3. LOCK WAIT 6 lock struct(s), heap size 1136, 23 row lock(s): 当前事务在等待锁的授予。它有6个锁结构（lock struct），占用了1136字节的堆空间，并涉及到了23个行锁（row lock）。

4. MySQL thread id 15432888, OS thread handle 139828380456704, query id 1297000886 172.16.57.35 dolphinscheduler Sending data: 这是执行该事务的MySQL线程的相关信息，包括线程ID、操作系统线程句柄、查询ID和客户端IP地址。

5. *** (1) WAITING FOR THIS LOCK TO BE GRANTED: TABLE LOCK tabledolphinscheduler.t_ds_alerttrx id 1091557054 lock mode AUTO-INC waiting: 这是一个等待锁的提示。当前事务正在等待一个名为t_ds_alert的表的AUTO-INC（自增）锁的授予。

6. *** (2) TRANSACTION: TRANSACTION 1091557052, ACTIVE 0 sec inserting: 这是另一个事务的ID和状态信息。事务ID为1091557052，表示当前事务处于活动状态，并且正在执行插入操作。

7. mysql tables in use 2, locked 2: 当前事务正在使用2个表，并对这2个表进行了锁定。

8. 8 lock struct(s), heap size 1136, 24 row lock(s), undo log entries 1: 当前事务有8个锁结构，占用了1136字节的堆空间，并涉及到了24个行锁和1个撤销日志条目。

9. MySQL thread id 15432932, OS thread handle 139828372735744, query id 1297000883 172.16.57.38 dolphinscheduler Sending data: 这是执行该事务的MySQL线程的相关信息，包括线程ID、操作系统线程句柄、查询ID和客户端IP地址。

10. *** (2) HOLDS THE LOCK(S): TABLE LOCK tabledolphinscheduler.t_ds_alerttrx id 1091557052 lock mode AUTO-INC: 这是另一个事务持有的锁的信息。该事务持有了名为t_ds_alert的表的AUTO-INC锁。

11. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 91755 page no 5 n bits 136 index idx_sign of tabledolphinscheduler.t_ds_alerttrx id 1091557052 lock_mode X locks gap before rec insert intention waiting: 这是等待的锁的信息。当前事务正在等待一个名为t_ds_alert的表中的一个记录锁的授予。

通过分析这些日志，可以了解到事务之间的锁等待情况，以及当前事务所涉及的_