MySQL报错Lock wait timeout exceeded的原因排查和解决方法

org.springframework.dao.cannotacquirelockexception: 
### error updating database.  cause: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: 
lock wait timeout exceeded; try restarting transaction
### sql: update order_info set status = ?, update_time = ? where order_no = ?
### cause: com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: 
lock wait timeout exceeded; try restarting transaction

-- 查询当前所有正在运行的事务，按启动时间升序排列
select 
    trx_id, 
    trx_state, 
    trx_started, 
    trx_mysql_thread_id, 
    trx_query,
    trx_rows_locked
from information_schema.innodb_trx 
order by trx_started asc;

-- 假设查出的异常 trx_mysql_thread_id 为 88492
kill 88492;

select 
    r.trx_id as waiting_trx_id,
    r.trx_mysql_thread_id as waiting_thread,
    r.trx_query as waiting_query,
    b.trx_id as blocking_trx_id,
    b.trx_mysql_thread_id as blocking_thread,
    b.trx_query as blocking_query
from performance_schema.data_lock_waits w
inner join information_schema.innodb_trx b on b.trx_id = w.blocking_engine_transaction_id
inner join information_schema.innodb_trx r on r.trx_id = w.requesting_engine_transaction_id;

@service
public class orderservice {
    @autowired
    private ordermapper ordermapper;
    @autowired
    private logisticsclient logisticsclient; 
    // 错误：事务边界过大，包含了网络 io
    @transactional(rollbackfor = exception.class)
    public void confirmorder(string orderno) {
        // 1. 更新订单状态（此时 innodb 对该行加 x 锁）
        ordermapper.updatestatus(orderno, "confirmed");
        // 2. 调用物流系统下发发货单（假设耗时 3 秒，若网络抖动可能耗时 60 秒）
        logisticsclient.dispatchdelivery(orderno); 
        // 3. 发送 mq 消息通知下游
        mqproducer.send("order_confirmed_topic", orderno);
        // 直到方法执行完毕，事务提交，行锁才会释放。
        // 若 logisticsclient 超时，行锁将被持有数十秒，导致其他查询或更新该订单的请求全部阻塞。
    }
}

@service
public class orderservice {
    // 正确：事务仅包裹纯粹的 db 操作
    @transactional(rollbackfor = exception.class)
    public void doupdateorderstatus(string orderno) {
        ordermapper.updatestatus(orderno, "confirmed");
    }
    public void confirmorder(string orderno) {
        // 1. 先执行本地事务，快速提交并释放行锁
        doupdateorderstatus(orderno);
        // 2. 事务提交后，再执行外部调用（此时即使耗时，也不会占用 db 行锁）
        logisticsclient.dispatchdelivery(orderno);
        mqproducer.send("order_confirmed_topic", orderno);
    }
}

-- 假设 user_phone 字段是 varchar 类型，且建有普通索引
-- 错误写法：传入数字类型，导致 mysql 对 user_phone 进行隐式转换，索引失效
update user_account set balance = balance - 100 where user_phone = 13800138000;

@transactional(rollbackfor = exception.class)
public void batchupdatestock(list<stockdto> list) {
    // 错误：在循环中频繁获取和释放行锁，且事务持续时间随 list 大小线性增长
    for (stockdto dto : list) {
        stockmapper.deductstock(dto.getskuid(), dto.getcount());
    }
}

@autowired
private transactiontemplate transactiontemplate;
public void batchupdatestock(list<stockdto> list) {
    // 每 200 条提交一次事务
    list<list<stockdto>> partitions = listutils.partition(list, 200);
    for (list<stockdto> batch : partitions) {
        transactiontemplate.execute(status -> {
            stockmapper.batchdeductstock(batch);
            return null;
        });
    }
}

set global innodb_lock_wait_timeout = 120;

-- 更新时携带版本号，若版本号不匹配则更新失败，由应用层重试
update account 
set balance = balance - 100, version = version + 1 
where id = 1 and version = 5;