MySQL+RabbitMQ死信队列完整解决支付库存方案

if(productId == null || quantity <= 0){

  throw new IllegalArgumentException("参数错误");

}

START TRANSACTION;

SELECT stock FROM products WHERE id=123 FOR UPDATE;

-- 返回：当前库存10件

UPDATE products SET stock=stock-1 WHERE id=123 AND stock>=1;

-- 影响行数：1（成功）

String orderNo = "O" + System.currentTimeMillis();

INSERT INTO orders 

(order\_no,user\_id,status,total\_amount)

VALUES 

('O168123456',1001,'待支付',299.00);

COMMIT;

{

  "orderNo": "O168123456",

  "amount": 299,

  "userId": 1001

}

// 设置30分钟过期

message.getMessageProperties().setExpiration("1800000"); 

rabbitTemplate.send("order.pay.exchange", "pay.routing.key", message);

@RabbitListener( queues = "queue.order.pay")

public void process(OrderMessage message){

  // 处理逻辑...

}

WXPayResponse response = wxPay.unifiedOrder(

  message.getOrderNo(),

  message.getAmount()

);

UPDATE orders SET status='已支付' 

WHERE order\_no='O168123456' AND status='待支付';

@RabbitListener: queues = "dlq.order.cancel")

public void handleTimeoutOrder(OrderMessage message){

  // 回滚库存...

}

UPDATE products SET stock=stock+1 WHERE id=123;

UPDATE orders SET status='已取消' 

WHERE order\_no='O168123456' AND status='待支付';

ALTER TABLE orders ADD COLUMN mq\_processed TINYINT DEFAULT 0;

-- 每小时执行的SQL

UPDATE orders SET status='已取消'

WHERE status='待支付'

AND create\_time < NOW() - INTERVAL 30 MINUTE

AND mq\_processed=0;

graph LR

    A[用户下单] --> B[MySQL扣库存]

    B --> C[创建订单]

    C --> D[发支付消息]

    D --> E[正常支付处理]

    E --> F[更新为已支付]

    D -->|30分钟超时| G[死信队列]

    G --> H[回滚库存]

    G --> I[标记已取消]

    H --> J[定时任务兜底]