如何在PHP中管理嵌套数据库事务？

Question

I have a method deleteUser(..) that executes a mysql query and calls another method logAction(..) that calls another mysql query.

What is the best way to handle transactions inside such methods?

Example pseudo code:

function deleteUser($userId) {
    $db->execute('BEGIN TRANSACTION');

    $userCount = $db->execute("DELETE FROM users WHERE id=$userId");

    logAction('USER DELETED');

    $db->execute('INSERT INTO ... another action');

    $db->execute('COMMIT');
}

function logAction($action) {
    $db->execute('BEGIN TRANSACTION');

    $db->execute('INSERT INTO logtable ... ACTION');
    if (error) { 
        $db->execute('ROLLBACK');
    }

    $db->execute('COMMIT');
}

As you can see, I'm calling logAction from deleteUser() method, and encountered 'BEGIN TRANSACTION' twice. Method logAction() calls 'COMMIT' prematurely before deleteUser() is done.

Is there a way to manage transactions inside such nested methods?

Answer 1

MySql does not supprot nesting transaction. Also

Beginning a transaction causes any pending transaction to be committed

http://dev.mysql.com/doc/refman/5.5/en/commit.html

But you need to execute those queries inside one transaction or execute them separately. You insist to run your methods inside transaction.

function deleteUser($userId) {
    if ($this->notInTransation()) throw new NotInTransactionException('not in transaction');

    $userCount = $db->execute("DELETE FROM users WHERE id=$userId");

    logAction('USER DELETED');

    $db->execute('INSERT INTO ... another action');

}

function logAction($action) {
    if ($this->notInTransation()) throw new NotInTransactionException('not in transaction');
    $db->execute('INSERT INTO logtable ... ACTION');
}

Thats would make you sure that your queries runes inside one transaction. With this approach you should put begin, rollback, commit higher in hierarchy, or put queries lower.

P.S. You could use autocommit variable to check transaction being started.