Mysql – Update large number of row in MySQL table

MySQLupdate

I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount

|id|customer_id |type     |amount|
|--|------------|---------|------|
|1 |44          |Credit   |50    |
|2 |44          |Credit   |20    |
|3 |44          |Debit    |30    |
|4 |10          |Credit   |30    |

now i am introduce a new balance column(current balance) on this table like below. 

|id|customer_id |type     |amount|balance|
|--|------------|---------|------|-------|
|1 |44          |Credit   |50    |50     |
|2 |44          |Credit   |20    |70     |
|3 |44          |Debit    |30    |40     |
|4 |10          |Debit    |30    |-30    |

The problem is, on the customer transaction table, their was nearly millions of row and all balance column was 0.00.

So i want to re-sync all balance data. But i'm confused how to recalculate and update all those row. Can i update this by MySQL query or calculate and update from my application (Laravel-PHP).

Solution 1: suggested by @GMB

In MySQL 5.x, where window functions are not available, an option uses a correlated subquery to compute the balance:

update customer_transaction ct
inner join (
    select 
        id, 
        (
            select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
            from customer_transaction ct2
            where ct2.customer_id = ct1.customer_id and ct2.id <= ct1.id
        ) balance
    from customer_transaction ct1
) ctx on ctx.id = ct.id
set ct.balance = ctx.balance

But their is a problem. We are doing those process on a live server and it take's some times to execute. i think, during that execution time, my transaction table remains locked. Is their any way to preventing locking.

Best Answer

Look at this:

-- this trigger needed for initial balance calculation 
-- or emergency re-calculation (generally for one customer)
CREATE TRIGGER tr_bu_customer_transaction
BEFORE UPDATE
ON customer_transaction
FOR EACH ROW
SET NEW.balance = COALESCE( ( SELECT balance 
                              FROM customer_transaction
                              WHERE customer_id = NEW.customer_id
                                AND id < NEW.id
                              ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount;

-- this trigger needed for balance calculation for newly inserted records
CREATE TRIGGER tr_bi_customer_transaction
BEFORE INSERT
ON customer_transaction
FOR EACH ROW
SET NEW.balance = COALESCE( ( SELECT balance 
                              FROM customer_transaction
                              WHERE customer_id = NEW.customer_id
                              ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount;

Usage sample: fiddle.

PS. Of course the whole table will be locked during initial calculation. But I think this will take less time then the technique with correlated query usage. And this allows to perform initial calculation for separate user or users list per chunk easily.

PPS. I assume that the transaction records (more precisely - their base data) are updated NEVER.

Related Question