0

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.

2
  • Is their any way to preventing locking. Prevent at all - no. But you may decrease the consecutive time when the table is locked by dividing the update process by chunks (for example, 100 users per chunk) with some timegaps between chunks execution. an option uses a correlated subquery to compute the balance I'd recommend user-defined variables usage.
    – Akina
    May 22, 2020 at 10:50
  • Another solution - create additional table, perform SELECT into it , then perform working table UPDATE (maybe by chunks too). One more solution - create a BEFORE INSERT/UPDATE trigger which calculates the balance value if new value is set to NULL or 0, then perform fake update for all the table under ORDER BY transaction_time and set balance to NULL or 0 - it will be recalculated (and in future this triggers will set correct balance values for newly inserted records and store your table in actual state).
    – Akina
    May 22, 2020 at 10:56

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.