什么是大事务

MySQL中的“大事务”通常指的是那些执行时间长、涉及数据行数多的事务。它主要依赖于两个维度来定义：时间和数据大小。

时间：如果一个事务从开启到提交需要花费很长时间，我们称之为大事务。这里的“时间”可能包含实际的数据库操作时间（例如，查询或更新记录）以及应用逻辑处理时间。
数据大小：如果一个事务需要修改或访问的数据行数特别多，也可以被认为是大事务。

举例来说，假设你正在运行一个电子商务网站，并且你需要对过去一年的所有订单进行一次价格调整。如果你尝试在一个单独的事务中完成这个操作，那么根据你的订单数量，这可能会变成一个大事务。

为什么要避免大事务

在MySQL中，大事务经常会给系统带来性能和稳定性的问题，以下是一些具体的大事务可能带来的问题或危害：

锁竞争：长时间运行的事务可能会更频繁地涉及到数据库锁的争用，这可能导致其他事务被阻塞，影响并发性能。
容易造成死锁：大事务会锁住更多的资源，在并发环境下更容易出现死锁问题。
影响恢复速度：如果数据库崩溃，那么恢复到崩溃前的状态所需要的时间将会更长，因为需要回滚大事务中未提交的数据。
占用大量内存：MySQL中的InnoDB引擎利用Undo log来实现MVCC以及事务的原子性，长事务意味着系统不能及时清理掉已经完成的版本，导致旧版本数据堆积，占用大量内存。
影响主从同步：在主从复制情况下，一个长事务在主服务器上的提交，可能会导致从服务器延迟，影响主从同步。
阻塞 purge 线程：在 InnoDB 引擎中，存在一个后台线程叫做 purge 线程，它负责清理不再需要的 undo 记录。如果有一个非常长的事务存在，即使其他事务的 undo 记录都可以被 purge，但由于这个长事务，那些空间也不能回收。

因此，通常来说，在设计系统时，应尽量避免大事务的出现，或者对可能产生的影响有充分准备和应对策略。

具体案例

数据批量修改

考虑一个电子商务网站需要对所有产品进行一次价格调整。如果你尝试在一个单独的事务中完成这个操作，那么就形成了一个大事务。

try:
    cursor.execute("START TRANSACTION")
    cursor.execute("UPDATE products SET price = price * 0.9") # 所有产品打9折
    cursor.execute("COMMIT")
except Exception as e:
    cursor.execute("ROLLBACK")

长时间处理

例如，在处理上传的大CSV文件并插入数据库时，可能会使用一个大事务。

with open('large_file.csv', 'r') as file:
    try:
        reader = csv.reader(file)
        cursor.execute("START TRANSACTION")
        for row in reader:
            cursor.execute("INSERT INTO table_name VALUES (?, ?, ?)", row)
        cursor.execute("COMMIT")
    except Exception as e:
        cursor.execute("ROLLBACK")

事务中包含外部接口调用：事务的执行时间取决于外部接口响应时间

try:
    cursor.execute("START TRANSACTION")
    # 先在数据库中预留该笔交易
    cursor.execute("INSERT INTO payments (user_id, amount, status) VALUES (?, ?, 'PENDING')", (payment.user_id, payment.amount))
    cursor.execute("COMMIT")

    # 调用外部支付服务进行支付处理
    result = external_payment_service.process(payment)

    cursor.execute("START TRANSACTION")
    # 根据支付服务的回应更新数据库中交易的状态
    if result.success:
        cursor.execute("UPDATE payments SET status = 'SUCCESS' WHERE id = ?", (payment.id,))
    else:
        cursor.execute("UPDATE payments SET status = 'FAILED' WHERE id = ?", (payment.id,))

    cursor.execute("COMMIT")
except Exception as e:
    cursor.execute("ROLLBACK")

优化方案

分批处理

在每个小事务中处理一部分数据。将一次大量的插入操作分成多个小事务进行：

# 假设有一个包含10000条记录的列表 need_to_insert_list
BATCH_SIZE = 100  # 用来设定每次操作的记录数
for i in range(0, len(need_to_insert_list), BATCH_SIZE):
    try:
        cursor.execute("BEGIN TRANSACTION")
        for record in need_to_insert_list[i: i + BATCH_SIZE]:
            cursor.execute("INSERT INTO my_table VALUES (?)", (record,))
        cursor.execute("COMMIT")
    except Exception as e:
        cursor.execute("ROLLBACK")

网络调用尽可能放在事务外，并考虑利用补偿机制

try:
    cursor.execute("START TRANSACTION")
    cursor.execute("UPDATE table1 SET column1 = 'value1' WHERE id = 1")
    cursor.execute("UPDATE table2 SET column2 = 'value2' WHERE id = 2")
    cursor.execute("COMMIT")

    # 外部网络调用
    result = external_service.call()

except Exception as e:
    cursor.execute("ROLLBACK")

    if result.failed:
        try:
            # 补偿事务：将数据回滚到原始状态
            cursor.execute("START TRANSACTION")
            cursor.execute("UPDATE table1 SET column1 = 'original_value1' WHERE id = 1")
            cursor.execute("UPDATE table2 SET column2 = 'original_value2' WHERE id = 2")
            cursor.execute("COMMIT")
        except Exception as e:
            cursor.execute("ROLLBACK")

使用异步处理

如果一个事务中涉及三步操作：

查询订单
支付
发送邮件

此时可以把发送邮件放在mq中，利用mq的自动重试在一定程度上保证邮件的发送成功。

Mysql 大事务调优

什么是大事务

为什么要避免大事务

具体案例

优化方案