mysql 跨库事物

import pika
import pymysql

# 连接数据库
db1_conn = pymysql.connect(host='localhost', user='user1', password='pass1', db='db1')
db2_conn = pymysql.connect(host='localhost', user='user2', password='pass2', db='db2')

# 连接消息队列
rabbitmq_conn = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
channel = rabbitmq_conn.channel()

# 声明队列
channel.queue_declare(queue='order_queue')

def create_order(user_id, product_id):
    try:
        # 开启事务
        db1_conn.begin()
        db2_conn.begin()

        # 插入订单信息到db2
        cursor2 = db2_conn.cursor()
        cursor2.execute("INSERT INTO orders (user_id, product_id) VALUES (%s, %s)", (user_id, product_id))
        db2_conn.commit()

        # 更新用户信息到db1
        cursor1 = db1_conn.cursor()
        cursor1.execute("UPDATE users SET order_count = order_count + 1 WHERE id = %s", (user_id,))
        db1_conn.commit()

        # 发送消息到队列
        channel.basic_publish(exchange='', routing_key='order_queue', body=f'Order created for user {user_id}')

    except Exception as e:
        # 回滚事务
        db1_conn.rollback()
        db2_conn.rollback()
        print(f"Error creating order: {e}")

# 消费消息队列
def callback(ch, method, properties, body):
    print(f"Received message: {body}")

channel.basic_consume(queue='order_queue', on_message_callback=callback, auto_ack=True)

print('Waiting for messages. To exit press CTRL+C')
channel.start_consuming()