面试题：Python结合MySQL处理复杂事务及锁机制

实现思路

连接数据库：使用Python的mysql-connector-python库连接到MySQL数据库。
开启事务：在转账操作开始前，开启一个事务，确保所有相关操作要么全部成功，要么全部失败。
加锁：对涉及的账户（账户A和账户B）进行行级锁，防止并发操作修改账户余额。
检查余额：查询账户A的余额，确保其有足够的金额进行转账。
更新余额：更新账户A和账户B的余额。
提交事务：如果所有操作都成功，提交事务。
处理异常：如果出现任何异常，回滚事务以保证数据一致性。

Python代码实现

import mysql.connector

def transfer_funds(from_account_id, to_account_id, amount):
    try:
        # 连接数据库
        conn = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database'
        )
        cursor = conn.cursor()

        # 开启事务
        conn.start_transaction()

        # 加锁查询账户A的余额
        cursor.execute("SELECT balance FROM accounts WHERE account_id = %s FOR UPDATE", (from_account_id,))
        from_balance = cursor.fetchone()[0]

        # 检查余额是否足够
        if from_balance < amount:
            raise ValueError("Insufficient funds")

        # 更新账户A的余额
        new_from_balance = from_balance - amount
        cursor.execute("UPDATE accounts SET balance = %s WHERE account_id = %s", (new_from_balance, from_account_id))

        # 查询账户B的余额
        cursor.execute("SELECT balance FROM accounts WHERE account_id = %s FOR UPDATE", (to_account_id,))
        to_balance = cursor.fetchone()[0]

        # 更新账户B的余额
        new_to_balance = to_balance + amount
        cursor.execute("UPDATE accounts SET balance = %s WHERE account_id = %s", (new_to_balance, to_account_id))

        # 提交事务
        conn.commit()

        print(f"Transferred {amount} from account {from_account_id} to account {to_account_id} successfully")

    except mysql.connector.Error as err:
        # 回滚事务
        if conn.is_connected():
            conn.rollback()
        print(f"Error: {err}")

    finally:
        if cursor:
            cursor.close()
        if conn.is_connected():
            conn.close()

# 调用函数进行转账
transfer_funds(1, 2, 100)

星途面试题库

面试题：Python结合MySQL处理复杂事务及锁机制

知识考点

面试题答案

实现思路

Python代码实现