记一次惊心动魄的MySQL死锁排查：高并发下的订单更新陷阱

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

SET GLOBAL innodb_print_all_deadlocks = ON;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-27 15:45:32 0x7f2b2c17b700
*** (1) TRANSACTION:
TRANSACTION 312345678, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 111, OS thread handle 123456, query id 22222 192.168.1.100 api_user updating
UPDATE orders SET status = 'completed', pay_time = NOW() WHERE order_id = 'ORDER10001' AND status = 'unpaid';

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 456 page no 7 n bits 80 index idx_order_id of table `prod_db`.`orders` trx id 312345678 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; ...

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 456 page no 7 n bits 80 index PRIMARY of table `prod_db`.`orders` trx id 312345678 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 8; ...

*** (2) TRANSACTION:
TRANSACTION 312345679, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 112, OS thread handle 123457, query id 22223 192.168.1.100 api_user updating
UPDATE orders SET status = 'cancelled' WHERE order_id = 'ORDER10001' AND status = 'unpaid';

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 456 page no 7 n bits 80 index PRIMARY of table `prod_db`.`orders` trx id 312345679 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 8; ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 456 page no 7 n bits 80 index idx_order_id of table `prod_db`.`orders` trx id 312345679 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; ...

*** WE ROLL BACK TRANSACTION (2)

CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` varchar(32) NOT NULL COMMENT '业务订单号',
  `status` varchar(20) NOT NULL DEFAULT 'unpaid',
  `pay_time` datetime DEFAULT NULL,
  ...其他字段...
  PRIMARY KEY (`id`),            -- 聚簇索引（主键索引）
  UNIQUE KEY `uk_order_id` (`order_id`) -- 唯一二级索引
) ENGINE=InnoDB;

// 支付回调服务
@Transactional
public void onPaySuccess(String orderId) {
    // 直接使用order_id更新
    orderDao.updateStatusByOrderId(orderId, "unpaid", "completed");
}

// 订单取消服务
@Transactional
public void cancelOrder(String orderId) {
    orderDao.updateStatusByOrderId(orderId, "unpaid", "cancelled");
}

// 支付回调服务
@Transactional
public void onPaySuccess(String orderId) {
    // 1. 先通过order_id查询出主键id和当前status（非必须，也可在后续update条件中直接使用status判断）
    Order order = orderDao.selectByOrderId(orderId);
    if (order != null && "unpaid".equals(order.getStatus())) {
        // 2. 使用主键id作为更新条件
        orderDao.updateStatusById(order.getId(), "unpaid", "completed");
    }
}

// OrderDao.xml中的SQL映射
<update id="updateStatusById">
    UPDATE orders
    SET status = #{newStatus},
        pay_time = NOW() <!-- 支付更新才有这个 -->
    WHERE id = #{id}
      AND status = #{oldStatus} <!-- 乐观锁思想，防止状态覆盖 -->
</update>

@Transactional
public void onPaySuccess(String orderId) {
    // 1. 使用FOR UPDATE提前且明确地锁定主键记录
    Order order = orderDao.selectByOrderIdForUpdate(orderId);
    if (order != null && "unpaid".equals(order.getStatus())) {
        // 2. 再更新
        orderDao.updateStatusById(order.getId(), "unpaid", "completed");
    }
}
// SQL: SELECT * FROM orders WHERE order_id = #{orderId} FOR UPDATE