What should I do if anything related to database communication goes wrong?

Question

Here's the problem: When a script starts modifying the database and something goes wrong, the database is usually corrupted. For example, lets say we have a User table and a Photos table.

A script creates a user dataset and in the next lines it attempts to create a photo dataset. The photo has a user_id column. Now lets assume something goes wrong and PDO's lastInserId() doesn't return the id of the user. So what happens in worst case: We get a user with no photo, and a photo with no valid user_id. Broken reference. 3 weeks to debug.

Are there any good strategies to follow, to prevent exactly this kind of problems? In my code below, you can see that I at least try to log that to a file and quit the script execution to prevent more damage and db curruption.

public function lastInsertId() {
    $id = $this->dbh->lastInsertId();
    if (!is_numeric($id)) {
        $this->logError("DB::lastInsertId() did not return an id as expected!");
        die();
    }
    return $id;
}

Maybe I have to use Transactions all over the place, at any time where an query B depends on a query A, and so forth? Is that the solution to go?

Should I do a "precaution rollback" before the die() call? I guess it would not hurt much at this point, would it? I'm not sure...

Answer 1

You should keep a log of all transactions, so if the automated process goes wrong (even your rollbacks, fallback procedures, etc), you still can revert all effects back manually.

Answer 2

The solution would be to use transactions each time you have several queries for which it should be "all or none", yes -- that's the A of ACID : Atomicity.

You can do a rollback before your die, if you want ; it won't change much (a transaction that is not commited will automatically be rolled-back by the DB engine), but it will make your code more clear, and easier to understand.

As a sidenote : using die this way is probably not the "right" way to deal with errors : it'll prevent you from displaying any kind of "nice" error page, for instance.

A solution that's more often used is to have some kind of exception be thrown when there is such kind of problem -- and in a higher layer of your application (in one single place) deal with those exceptions, to display an error page.

Answer 3

Outside of using a transactional engine (InnoDB if you're using MySQL, or just use PostgreSQL, etc.) and wrapping the relevant atomic activities there's not a great deal you can do.

As @Seb says, you can create a transactional log and you could even use a master/slave database setup, but this won't really add much in terms of coverage.