长事务因素


1.定时任务
2.Python 脚本访问数据库批量更新数据

          
#! /usr/bin/env python

import pymysql
import time
import re
import decimal

def conn_wms_mysql():
    conn_wms = pymysql.connect(host="", port=3306, user="", passwd="", db="")
    return conn_wms

def conn_oms_mysql():
    conn_oms = pymysql.connect(host="", port=3306, user="", passwd="", db="")
    return conn_oms


# 修复仓配系统订货单商品单价
def fix():

    conn_wms = conn_wms_mysql()
    conn_oms = conn_oms_mysql()

    cursor = conn_wms.cursor(cursor=pymysql.cursors.DictCursor)
    cursor_oms = conn_oms.cursor(cursor=pymysql.cursors.DictCursor)

    # 查询出问题时间段所有订货单
    sql = """
SELECT
    scod.customer_order_details_id,
    scod.warehouse_owner_goods_no,
    scod.goods_name,
    scod.goods_unit_price,
    scod.order_item_code,
    sco.customer_order_code,
    sco.store_order_code,
    sco.create_time
FROM
    store_customer_order_details scod
    LEFT JOIN store_customer_order sco ON scod.customer_order_id = sco.customer_order_id
WHERE
    sco.customer_order_source = 30
    AND sco.create_time >= '2024-10-30 09:30:00'
    AND sco.create_time <= '2024-10-30 14:55:00'
    """

    cursor.execute(sql)
    all_data = cursor.fetchall()

    for data in all_data:

        # 获取正确的商品单价
        sql = """
        SELECT
            oi.sell_price
        FROM
            order_center.order_item oi
        WHERE
            oi.order_item_code = '%s'
        """
        sql = sql % (data['order_item_code'])

        cursor_oms.execute(sql)
        price_data = cursor_oms.fetchall()

        sell_price = str(price_data[0]['sell_price'])

        # 更新订货单明细里的商品单价
        sql = """
        UPDATE store_customer_order_details SET goods_unit_price = %s WHERE customer_order_details_id = %s
        """
        sql = sql % (sell_price, data['customer_order_details_id'])

        cursor.execute(sql)

        # 更新一条提交一次
        conn_wms.commit()

    # 如果事务耗时很久, 在该处提交事务, 会造成事务长时间持有锁资源
    # conn_wms.commit()

if __name__ == '__main__':
    fix()
          
        

3.SQL语句批量更新数据

          
UPDATE store_batch_details_log t LEFT JOIN store_storage_details j ON t.storage_details_id = j.storage_details_id
SET t.enterprise_id = j.enterprise_id,t.warehouse_id = j.warehouse_id
WHERE t.enterprise_id IS NULL;

可以添加时间范围,缩小更新范围
UPDATE store_batch_details_log t LEFT JOIN store_storage_details j ON t.storage_details_id = j.storage_details_id
SET t.enterprise_id = j.enterprise_id,t.warehouse_id = j.warehouse_id
WHERE t.enterprise_id IS NULL AND t.create_time <='2022-02-01';
          
        

4.错误业务代码 - 锁

          
def doBusiness():
    1.开启事务
    2.获取分布式锁
    ...
          
        

如果因为获取分布式锁失败被阻塞, 便可能出现长事务. 应该先获取分布式锁再开启事务.

5.错误业务代码 - MQ

          
def doBusiness():
    1.开启事务
    2.MQ网络带宽较低,发送MQ慢(约20秒)
    ...
          
        

如果因为发送MQ慢耗时久, 便可能出现长事务.

6.错误业务代码 - 数据库连接

          
def doBusiness():
    1.开启事务
    2.获取不到数据库连接
    ...
          
        

数据库连接池已满,无法立即获取数据库连接被阻塞.