Informix中批量更新大量行时避免全表锁定的方案咨询

针对全表更新锁表的问题，分批提交和结合rowid使用SELECT FOR UPDATE都是可行的解决方案，以下是具体实现方式：

一、存储过程分批提交实现 #

通过存储过程每次处理小批量数据，每批处理完成后立即提交事务，避免长时间持有全表锁。示例代码如下（默认每批处理1000行，可根据业务调整）：

CREATE PROCEDURE update_article_batch()
    DEFINE v_count INT;
    DEFINE v_batch_size INT;

    LET v_batch_size = 1000;
    LET v_count = 1;

    WHILE v_count > 0
        -- 分批更新指定数量的行
        UPDATE informix.article
        SET article_qta_ord = NVL(
            (SELECT SUM(CASE WHEN (qta_ordered - NVL(qta_loaded, 0)) > 0 
                             THEN (qta_ordered - NVL(qta_loaded, 0)) 
                             ELSE 0 END)
             FROM informix.order_table 
             WHERE order_article_code = article_code AND whsId = '5'),
            0
        )
        WHERE rowid IN (
            SELECT rowid
            FROM informix.article
            ORDER BY rowid
            LIMIT v_batch_size
        );

        -- 获取本次更新行数，判断是否继续循环
        LET v_count = DBINFO('sqlca.sqlerrd2');
        -- 提交当前批次事务
        COMMIT WORK;
    END WHILE;

END PROCEDURE;

调用存储过程执行更新：

EXECUTE PROCEDURE update_article_batch();

二、dbaccess结合rowid的脚本实现 #

如果不想使用存储过程，可在dbaccess中编写脚本，通过游标获取rowid单条更新，累计一定数量后提交：

BEGIN WORK;

DECLARE cur_article CURSOR FOR
    SELECT rowid, article_code
    FROM informix.article
    ORDER BY rowid;

DEFINE v_rowid ROWID;
DEFINE v_article_code VARCHAR(100); -- 按实际字段类型调整
DEFINE v_counter INT;
DEFINE v_qta_sum INT; -- 按实际字段类型调整

LET v_counter = 0;

OPEN cur_article;
FETCH cur_article INTO v_rowid, v_article_code;

WHILE SQLCODE = 0
    -- 计算当前行的目标值
    SELECT NVL(SUM(CASE WHEN (qta_ordered - NVL(qta_loaded, 0)) > 0 
                        THEN (qta_ordered - NVL(qta_loaded, 0)) 
                        ELSE 0 END), 0)
    INTO v_qta_sum
    FROM informix.order_table
    WHERE order_article_code = v_article_code AND whsId = '5';

    -- 更新单条记录
    UPDATE informix.article
    SET article_qta_ord = v_qta_sum
    WHERE rowid = v_rowid;

    LET v_counter = v_counter + 1;

    -- 每1000行提交一次事务
    IF v_counter MOD 1000 = 0 THEN
        COMMIT WORK;
        BEGIN WORK;
    END IF;

    FETCH cur_article INTO v_rowid, v_article_code;
END WHILE;

-- 提交剩余未处理的行
COMMIT WORK;
CLOSE cur_article;

将代码保存为update_article.sql，在dbaccess中执行：

dbaccess your_database_name update_article.sql

注意事项 #

• 批量大小（如1000）需根据业务并发情况测试调整，太小会增加提交开销，太大可能仍存在锁冲突。
• 若业务有强一致性要求，需评估分批提交带来的部分更新风险，确保业务逻辑允许该操作。
• 执行前建议备份数据，避免更新异常导致数据损坏。

内容的提问来源于stack exchange，提问作者famedoro