How anal is too anal?

[DWilliams]

In other words, if I have a piece of code doing an important database update, how many doublechecks do I need to do?

 

The script in question is a PHP script that gets executed by cron every night to accrue interest on all of our accounds. It has been working fine but doesn't really have much error checking in place. It loops through each account in our database and executes the following for each of them:

 

  $query = mysql_query("UPDATE dbase SET accruedinterest=$newAccruedInterest, currentbalance=$newCurrentBalance,
custom58='" . date('j') . "'  WHERE id=" . $row['id']) or die(mysql_error()); 

 

Now, this seems fairly bulletproof. 'id' is the PK of the table and is flagged unique. Would it be a waste of resources to do this update in a transaction and make sure exactly one row is being updated?

[roopurt18]

When money is concerned you can never be too anal about correctness.  You never, ever want the blame finger to land on you when money goes missing from the company's coffers.

 

Interestingly enough there doesn't seem to be a simple way to be sure that update statements work correctly.

 

1) Checking for errors is not enough.  Sure if errors occur you can assume the update did not work.  But just because there are no errors doesn't mean it worked either!  For example if you filtered on the wrong column or value; the query will work without errors but it doesn't do what you wanted.

 

2) You might be tempted to use a function like mysql_affected_rows() to count the number of rows updated.  But that won't necessarily work either!  The reason is because it will only return rows that actually changed.  If no update was necessary (and the row did not change) it won't be included in the tally returned by mysql_affected_rows().

 

It seems to me the only sure way to know the rows were updated is to perform count(*) queries before and after and make sure the number of final rows with those values are what you expect to them to be.

 

You'll also want to sacrifice performance when doing financial calculations and make sure the database is not buffering writes to disk.