Best way to prevent a value from going negative in mysql

Question

Hi all,

We have a table that maintains account balances by recording transactions in that table. i.e. the most recent row is the account balance.

When recording a withdrawal, we would like to ensure that the balance can never go negative. Our proposed solution is something like:

INSERT INTO `txns`
  (`account_id`, `prev_balance`, `txn_type`, `new_balance`,  `amount`, `description`)
SELECT 
  t.account_id, t.new_balance, $txn_type, t.new_balance - $amount, $amount, $description
FROM`txns` t
WHERE t.account_id = '$account'
  AND (select new_balance 
        FROM txns 
        WHERE account_id = '$account' 
        ORDER BY txn_id desc limit 1) >= $amount
ORDER BY txn_id desc LIMIT 1;"

But we are a bit concerned about the performance if the ANDed subquery (we had subquery performance issues on a previous project). None of the developers here are sql specialists. Deposits do not have the additional clause.

This is all on MySQL 5.0

Answer 1

Why don't you just do:

INSERT INTO `txns`
  (`account_id`, `prev_balance`, `txn_type`, `new_balance`,  `amount`, `description`)
SELECT *
FROM (
 SELECT
  t.account_id, t.new_balance, $txn_type, t.new_balance - $amount, $amount, $description
 FROM `txns` t
 WHERE t.account_id = '$account'
 ORDER BY txn_id desc
 LIMIT 1
)
WHERE new_balance - $amount > 0

Answer 2

I have to agree with the trigger idea. If this is an accounting rule that must be followed no matter how the data is entered, it needs to be in a trigger.

If this is true only for this particular case, then do it in the SQL code. I don't know mySQL but in SQL server Iwould put the check in an if statment and fail the transaction if the IF condition is met. The critical thing here isn't to ignore the data but to actively fail the transaction,otherwise the user thinks the data has been entered when it has not met the criteria to be entered. I would never write any cdode for a finacial system that isn't encapsulated in transactions which would rollback the entire transaction and send an error to the user if the business rules are not met. Business rules are extremely critical to financial applications (and should usually be in triggers so that they are never missed no matter how the data is put into the system) and data integrity can be a real problem if all the steps do not succeed and you are not in a transaction and rolling back when there is a problem.