How to ensure account balance and validity for fund transfers in MySQL

alexlee · August 7, 2025, 3:03pm

I’m developing a MySQL stored procedure for transferring money between accounts. The procedure is named execute_transfer, and it accepts parameters such as the transfer amount, a note, the account from which money will be deducted, and the account that will receive the funds.

The basic operation works well. For instance, when I call execute_transfer(75, 'payment', 5, 10), it correctly transfers 75 dollars from account 5 to account 10.

However, I’m running into two significant problems:

The procedure permits transfers even if one or both accounts don’t exist.
It doesn’t verify whether sufficient funds are available in the sending account before processing the transfer.

As an example, executing execute_transfer(500000, 'test transfer', 20, 30) goes through without errors, even if those account IDs are invalid.

Here’s what my tables look like:

CREATE TABLE accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  balance DECIMAL(10,2) CHECK (balance >= 0)
);

CREATE TABLE transfers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(10,2),
  from_account INT NOT NULL,
  to_account INT NOT NULL,
  note VARCHAR(100),
  created_at DATETIME,
  FOREIGN KEY (from_account) REFERENCES accounts(id),
  FOREIGN KEY (to_account) REFERENCES accounts(id)
);

And here’s the stored procedure I’m using:

DELIMITER //
CREATE PROCEDURE execute_transfer(
  IN transfer_amount DECIMAL(10,2),
  IN note VARCHAR(100),
  IN from_acc INT,
  IN to_acc INT
)
BEGIN
  START TRANSACTION;
  
  UPDATE accounts 
  SET balance = balance - transfer_amount
  WHERE id = from_acc;
  
  UPDATE accounts
  SET balance = balance + transfer_amount  
  WHERE id = to_acc;
  
  INSERT INTO transfers (amount, from_account, to_account, note, created_at)
  VALUES (transfer_amount, from_acc, to_acc, note, NOW());
  
  COMMIT;
END //
DELIMITER ;

What is the best approach to implement checks for account validity and balance before completing the transfers?

davidw · August 18, 2025, 4:18am

Hit this same issue building a financial app. Your foreign key constraints should block invalid account references, but they’re not working because MySQL’s storage engine probably isn’t enforcing them. Run SHOW TABLE STATUS LIKE 'accounts' to check if you’re using InnoDB. Besides the validation others mentioned, add these safeguards: validate transfer_amount is positive before starting the transaction, and set a max transfer limit to avoid processing massive amounts by accident. What really saved me debugging time was proper error handling with specific codes. Don’t use generic messages - give each failure type its own SQLSTATE code. Insufficient funds gets ‘45001’, invalid accounts get ‘45002’, etc. Makes logs way easier to read. Also, your CHECK constraint on balance might not work in older MySQL versions. I added explicit balance validation in the procedure just to be safe.

rustyGuitar9 · August 17, 2025, 12:44pm

Look, stored procedures work but you’re hardcoding business logic into your database. I learned this the hard way when our finance team needed approval workflows and fraud detection for transfers.

You need a flexible system for validation, logging, notifications, and service integrations. I built something similar using Latenode that handles these checks through workflows instead of buried SQL code.

The workflow validates accounts by hitting your database API, checks balances, runs fraud detection, sends notifications, then executes the transfer. When business rules change, you just update the workflow instead of modifying stored procedures.

You also get automatic retries if the database goes down, detailed logs for every step, and easy integration with payment processors or compliance systems.

My setup processes thousands of daily transfers with zero issues. The validation logic is visual and way easier to debug than nested SQL conditions.

Check it out at https://latenode.com

joec · August 17, 2025, 6:09am

Yeah, MySQL UPDATE operations will silently succeed even when they match zero rows - that’s your main problem. I hit the same issue building transfer functionality and found SELECT FOR UPDATE way more reliable than separate validation queries.

What worked for me was locking the source account row first. Prevents race conditions and keeps everything atomic. Here’s how I fixed the validation issues:

DELIMITER //
CREATE PROCEDURE execute_transfer(
  IN transfer_amount DECIMAL(10,2),
  IN note VARCHAR(100),
  IN from_acc INT,
  IN to_acc INT
)
BEGIN
  DECLARE source_balance DECIMAL(10,2);
  DECLARE dest_exists INT DEFAULT 0;
  
  START TRANSACTION;
  
  -- Lock and validate source account
  SELECT balance INTO source_balance
  FROM accounts 
  WHERE id = from_acc
  FOR UPDATE;
  
  IF source_balance IS NULL THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found';
  END IF;
  
  IF source_balance < transfer_amount THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
  END IF;
  
  -- Verify destination exists
  SELECT 1 INTO dest_exists FROM accounts WHERE id = to_acc LIMIT 1;
  
  IF dest_exists = 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found';
  END IF;
  
  -- Execute transfer
  UPDATE accounts SET balance = balance - transfer_amount WHERE id = from_acc;
  UPDATE accounts SET balance = balance + transfer_amount WHERE id = to_acc;
  
  INSERT INTO transfers (amount, from_account, to_account, note, created_at)
  VALUES (transfer_amount, from_acc, to_acc, note, NOW());
  
  COMMIT;
END //
DELIMITER ;

The FOR UPDATE clause stops other processes from modifying the source account while you’re validating, which kills those timing issues between balance checks and updates.

sofiag · August 16, 2025, 10:51pm

Your procedure’s running UPDATE statements without checking if the target records actually exist or meet your business rules. MySQL won’t throw an error when you UPDATE a non-existent ID - it just affects zero rows and keeps going.

I hit this same issue building a payment system last year. You’ve got to add validation steps before doing the actual transfers. First grab the source account’s current balance, then verify both accounts exist and there’s enough money.

Here’s how I fixed my procedure:

DELIMITER //
CREATE PROCEDURE execute_transfer(
  IN transfer_amount DECIMAL(10,2),
  IN note VARCHAR(100),
  IN from_acc INT,
  IN to_acc INT
)
BEGIN
  DECLARE current_balance DECIMAL(10,2);
  DECLARE account_count INT;
  
  START TRANSACTION;
  
  -- Check if both accounts exist
  SELECT COUNT(*) INTO account_count 
  FROM accounts 
  WHERE id IN (from_acc, to_acc);
  
  IF account_count != 2 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'One or both accounts do not exist';
  END IF;
  
  -- Get current balance and verify sufficient funds
  SELECT balance INTO current_balance 
  FROM accounts 
  WHERE id = from_acc;
  
  IF current_balance < transfer_amount THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  
  -- Proceed with transfer
  UPDATE accounts 
  SET balance = balance - transfer_amount
  WHERE id = from_acc;
  
  UPDATE accounts
  SET balance = balance + transfer_amount  
  WHERE id = to_acc;
  
  INSERT INTO transfers (amount, from_account, to_account, note, created_at)
  VALUES (transfer_amount, from_acc, to_acc, note, NOW());
  
  COMMIT;
END //
DELIMITER ;

The SIGNAL statement throws proper exceptions your app can catch and handle.

bellagarcia · August 15, 2025, 9:48am

the foreign key constraints should catch this, but they’re clearly not working right. I’ve dealt with the same mess in my banking app. add ROW_COUNT() checks after your updates - if you get 0 back, no rows changed and your account IDs are bogus. also throw in an IF statement to check transfer_amount > 0 before you process anything. stops negative transfers dead.

opalEcho · August 14, 2025, 7:52am

Building transfer systems in stored procedures gets messy fast. I’ve been down this road and ended up with hundreds of lines of validation logic that became impossible to maintain.

You need a proper workflow system that handles validation steps, not just database checks. You’ll eventually need approval workflows, fraud detection, external service calls, and audit trails. All that logic stuck in MySQL becomes a nightmare.

I moved our entire transfer system to automated workflows. The process validates both accounts exist through API calls, checks balances, runs business rules, handles approvals if needed, then executes the transfer. Each step has proper error handling and rollback logic.

The best part? When requirements change (and they always do), you just update the workflow visually instead of diving into SQL code. We added multi currency support and compliance checks without touching stored procedures.

Our current setup processes transfers with automatic retries, detailed logging, and integrations with external payment systems. Way more flexible than database constraints.

Check it out at https://latenode.com