I have two tables having the following schema.
CREATE TABLE users(
`id` INT AUTO_INCREMENT,
`balance` DECIMAL(10, 2),
PRIMARY KEY(`id`)
);
CREATE TABLE user_transactions(
`id` INT AUTO_INCREMENT,
`user_id` INT,
`previous_balance` DECIMAL(10,2) COMMENT 'Balance before transaction',
`amount` DECIMAL(10,2) COMMENT 'It will be either positive or negative and added to the previous balance',
`updated_balance` DECIMAL(10, 2) COMMENT 'Balance after transaction',
`created` BIGINT,
PRIMARY KEY(`id`)
);
I am using MySQL version 5.7. That's a minimal DB structure and indicates all the indexes going to be used. There will be more columns in both tables such as who carried out the transaction etc. I have to keep a record of the transaction in the user_transactions
table and update the balance in the users
table.
The transactions can be carried out by the user, the parent user, and the system. Therefore, I have to query the balance every few seconds and show it to the user. There will be no delete operations on both tables. Just insert and update on the users
table, and only insert on the user_transactions
table. Since there's going to be thousands of users and thousands of transactions every day, most probably at the same time, these insert and update queries should be optimized.
There are three approaches I could think of:
Run three queries in a stored procedure as follows.
CREATE PROCEDURE `update_balance` (user_id INT, amount DECIMAL(10,2)) BEGIN DECLARE previous_balance DECIMAL(10,2); -- fetch previous balance SELECT balance INTO previous_balance FROM users WHERE id = user_id; -- create an entry in user_transactions table INSERT INTO user_transactions(user_id, previous_balance, amount, updated_balance, created) VALUES (user_id, previous_balance, amount, previous_balance+amount, UNIX_TIMESTAMP()); -- updating the balance in users table UPDATE users SET balance = previous_balance+amount WHERE id = user_id; END
Create a trigger after updating the balance in the
users
table.Don't use the balance column in the
users
table at all and fetch balance from theuser_transactions
table instead.
1st and 2nd approach will lock the row and prevent any update. It won't be feasible if there is another column in the users
table being updated. 3rd approach appears not feasible to me as there's going to be a million records at some point.
This question points at writing schema. While I already have the schema, I need to write an optimized query(or queries) to populate the tables.
So, how to write queries to update balance while keeping every record of the transaction and the balance synced?
START TRANSACTION
andCOMMIT
.