Update a huge number of rows without lock entire table in Informix

Question

I need to update an huge number of rows in Informix, but when a run the update statement, it locks everyone works with the table for the entire updates process.

The sql statement:

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)

Is It possible to use a stored procedure to an huge update with periodically commit or with select for update using rowid inside dbaccess in order to avoid to lock everyone for the entire updates process ?

Answer 1

using SELECT FOR UPDATE with ROWID can help in managing concurrency.

CREATE PROCEDURE update_article_qta_ord()
    DEFINE commit_interval INT;
    DEFINE row_count INT;

    -- Set the number of rows to process in each batch
    LET commit_interval = 100;

    -- Declare cursor with SELECT FOR UPDATE
    DECLARE article_cursor CURSOR FOR
        SELECT article_code, ROWID
        FROM informix.article
        WHERE article_qta_ord IS NULL; -- Add any additional conditions as needed

    OPEN article_cursor;

    -- Loop through the cursor
    FETCH article_cursor INTO article_code, article_rowid;
    WHILE (SQLCODE = 0) DO
        BEGIN
            -- Your update logic here
            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 CURRENT OF article_cursor;

            -- Commit periodically
            LET row_count = row_count + 1;
            IF row_count % commit_interval = 0 THEN
                COMMIT;
            END IF;

            -- Fetch the next row
            FETCH article_cursor INTO article_code, article_rowid;
        EXCEPTION
            WHEN OTHERS THEN
                -- Handle exceptions as needed
                ROLLBACK;
                CLOSE article_cursor;
                RETURN;
        END;

    END WHILE;

    -- Commit any remaining changes
    COMMIT;

    CLOSE article_cursor;

END PROCEDURE;

This stored procedure uses a cursor with SELECT FOR UPDATE to lock each row during processing. The COMMIT statement is used periodically to release locks and reduce contention. Adjust the commit_interval variable to control how frequently commits occur.