Atomic transactions and logging

user_10991804 0
Viewed 113

Let's say I have a function that modifies a database. This could be anywhere from one query to multiple queries in a transaction. In any of these modifications we want to make sure all queries are successful. Thankfully for transactions, if one of these fails I have a way of making sure none of the changes are made permanent.

Now let's say I also have a log that needs to be written to to make note of the change (s). How could I make sure that both the modifications are made and the log written?

I could always do a try/catch on either but say for instance my queries are successful but my log is unsuccessful. How could I then go back and undo all of the modifications to the database?

Would something like this be effective and/or advisable?

try {
    $db->connect();
    $db->mysqli->begin_transaction();

    // series of queries
    ...
    // log to file

    $db->mysqli->commit();
} catch (exception $e) {
    $db->mysqli->rollback();
    $db->mysqli->close();
}

Wanted to make note of some behaviors I observed while testing this. If you place the commit() before the log, the database changes will not roll back even if an exception is caught while logging.

mysqli transactions exception php
1 Answers

The data will be stored in the database once you call commit() or trigger an implicit commit. For example, this will not make any changes to the database because there is no commit:

$mysqli->begin_transaction();
$mysqli->query('INSERT INTO test1(val) VALUES(2)');

// end of script's execution

In your little example, the transaction will be rolled back as long as your logging functionality stops the execution or throws an exception.

$db->connect();
try {
    $db->mysqli->begin_transaction();

    // series of queries
    ...
    // log to file
    throw new \Exception(); // <-- This will prevent commit from executing. 

    $db->mysqli->commit();
} catch (\Exception $e) {
    // An exception? That's ok, we'll try something different instead.
    $db->mysqli->rollback();
}

You need to call rollback only if you want to recover from the exception and clear the unsaved buffer. The only time you should ever catch exceptions is if you want to somehow recover from it and do a different thing. Otherwise, your code could be simplified by removing try-catch and rollback altogether.

However, it is a good idea to catch exceptions in transactions and roll it back explicitly as soon as possible, even when you do not want to recover. It can prevent an issue if you catch the exception somewhere else in your code and you try to execute other DB actions. The unsaved data is still in the buffer until you close the session or roll back! This is why you would often see code like this:

$db->connect();
try {
    $db->mysqli->begin_transaction();

    throw new \Exception(); // <-- Something throws an exception

    $db->mysqli->commit();
} catch (\Exception $e) {
    // rollback unsaved data, but rethrow the exception as we do not know how to recover from it here
    $db->mysqli->rollback();
    throw $e;
}
user_18394390
Related