事因：
修改order_detail 新增字段pre_delivery_date，使用原生alter命令或pt-online-schema-change操作均发现锁表，order_detail表数据为300w, 因数据量比较大，这里模拟复制了order_detail到order_detail_temp后，进行测试新增字段计算耗时，发现2分钟多就修改好结构，
但实际操作order_detail则卡住10分钟无法完成, show processlist, 发现select也是被锁表，中间也没insert或update操作，明显很不正常，经排查是运行了2个月未提前的事务导致的。

[root@db99 ~]# pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "add COLUMN pre_delivery_date DATETIME DEFAULT null COMMENT '预计发货时间' after is_pre_sale" D=sunday_mall,t='order_detail'  --execute 
1> Cannot connect to S=/tmp/mysql.sock,h=192.168.77.99,p=...,u=root
No slaves found.  See --recursion-method if host db99 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `sunday_mall`.`order_detail`...
Creating new table...
Created new table sunday_mall._order_detail_new OK.
Altering new table...
Altered `sunday_mall`.`_order_detail_new` OK.
2024-07-16T10:43:25 Creating triggers...

# Exiting on SIGINT.
Not dropping triggers because the tool was interrupted.  To drop the triggers, execute:

查看innodb stauts DEADLOCK 今天7月16号是没有死锁记录

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2024-07-16 13:51:12 0x7f8e9487f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 78440370 srv_active, 0 srv_shutdown, 84123911 srv_idle
srv_master_thread log flush and writes: 162558712
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 377455693
OS WAIT ARRAY INFO: signal count 2703158401
RW-shared spins 0, rounds 1562981588, OS waits 94183163
RW-excl spins 0, rounds 7947612327, OS waits 98115867
RW-sx spins 221853537, rounds 2800619716, OS waits 31652503
Spin rounds per wait: 1562981588.00 RW-shared, 7947612327.00 RW-excl, 12.62 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-07-12 12:01:47 0x7f8e88809700 # 今天7月16号 之前的不关事
*** (1) TRANSACTION:
TRANSACTION 2069459717, ACTIVE 28 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3
MySQL thread id 256292587, OS thread handle 140250376918784, query id 44391300417 192.168.77.42 sunday_mall updating
update `sunday_mallorder_detail` set `actual_cost_price` = '60.21', `updated_at` = '2024-07-12 12:01:19' where `id` = 5033032 and `sunday_mallorder_detail`.`deleted_at` is null
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2683 page no 122121 n bits 112 index PRIMARY of table `sunday_mall`.`sunday_mallorder_detail` trx id 2069459717 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 421731030381656, ACTIVE 30 sec fetching rows
mysql tables in use 12, locked 12
108339 lock struct(s), heap size 10019024, 4523770 row lock(s)
MySQL thread id 256324828, OS thread handle 140250152212224, query id 44391300248 192.168.77.42 sunday_mall Sending data
insert sunday_mallstatistics_recent_sale_temp (
            type,
            is_hot_sale,
            virtual_stock_count,
            virtual_stock_count_gi,

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2683 page no 122121 n bits 112 index PRIMARY of table `sunday_mall`.`sunday_mallorder_detail` trx id 421731030381656 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2681 page no 34110 n bits 152 index PRIMARY of table `sunday_mall`.`sunday_mallstatistics_order_detail` trx id 421731030381656 lock mode S waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 2072460412
....

查看锁表

[root@db99 shell]# mysql -e "show processlist" | grep -i lock
\n                    select product_id,sum(168.0.42:14702  sunday_mall Prepare 29  Waiting for table metadata lock select product_id,sum(buy_count) as product_count from (
\n                        inner join or a 8.0.42:18802  sunday_mall Prepare 35  Waiting for table metadata lock select a.id
\n                        inner join or a 8.0.42:18904  sunday_mall Prepare 35  Waiting for table metadata lock select a.id
257184994   sunday_mall 192.168.77.42:27748 sunday_mall Prepare 35  Waiting for table metadata lock select `platform_oid` from `order_detail` where `order_id` = ? and `order_detail`.`deleted_a   0   0
257185029   sunday_mall 192.168.77.42:28508 sunday_mall Prepare 35  Waiting for table metadata lock select * from `order_detail` where `order_id` = ? and `order_detail`.`deleted_at` is null   0   0
257185089   sunday_mall 192.168.77.42:29778 sunday_mall Prepare 35  Waiting for table metadata lock select * from `order_detail` where `order_id` in (?) and `order_detail`.`deleted_at` is null    0   0
257185309   sunday_mall 192.168.77.42:36230 sunday_mall Prepare 35  Waiting for table metadata lock select * from `order_detail` where `order_id` in (?) and `order_detail`.`deleted_at` is null    0   0
257191671   root    localhost   sunday_mall Query   35  Waiting for table metadata lock CREATE TRIGGER `pt_osc_sunday_mall_order_detail_del` AFTER DELETE ON `sunday_mall`.`be 0   0
257192777   sunday_mall 192.168.77.44:37710 sunday_mall Prepare 31  Waiting for table metadata lock select `product_id`, `product_name`, `product_img_url`, `product_stock_attr`, `sale_price`, `buy_cou 0   0
257192825   sunday_mall 192.168.77.43:21598 sunday_mall Prepare 10  Waiting for table metadata lock select `product_id`, `product_name`, `product_img_url`, `product_stock_attr`, `sale_price`, `buy_cou 0   0

查看事务 发现有其他表select order_detail的事务 运行了两个多月没提交
这里将这两个事务的线程KILL掉 添加字段就正常了

mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 421731030369248
                 trx_state: RUNNING
               trx_started: 2024-05-07 12:27:31
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 241941318
                 trx_query: select id from order where order_status in (1,2)
and platform_shop_id in (select id from platform_shop where shop_data_source in (1,2,8,6,11))
and (EXISTS (select order_detail.id from order_detail LEFT JOIN order_suit_product_detail on order_detail_id=order_suit_product_detail.order_detail_id where order_detail.order_id=order.id and 
 order_detail.product_id=19646 or order_suit_product_detail.product_id=19646
))
       trx_operation_state: NULL
         trx_tables_in_use: 4
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 1
*************************** 2. row ***************************
                    trx_id: 421731030323000
                 trx_state: RUNNING
               trx_started: 2024-05-07 12:25:31
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 241941143
                 trx_query: select id from order where order_status in (1,2)
and platform_shop_id in (select id from platform_shop where shop_data_source in (1,2,8,6,11))
and (EXISTS (select order_detail.id from order_detail LEFT JOIN order_suit_product_detail on order_detail_id=order_suit_product_detail.order_detail_id where order_detail.order_id=order.id and 
 order_detail.product_id=19646 or order_suit_product_detail.product_id=19646
))
       trx_operation_state: NULL
         trx_tables_in_use: 4
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 1
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> 
mysql> kill 241941318;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 241941143;
Query OK, 0 rows affected (0.00 sec)

https://blog.csdn.net/zhangjunli/article/details/128594776
https://www.ctyun.cn/zhishi/p-210089