phpBB sql transaction function not working

Question

I'm using phpBB and this code is not working as expected. It's simply using the phpBB sql_transaction function to start an sql transaction, then commit it. But the exception should be thrown half way through and issue a rollback using the same function.

However, the rollback never happens. Queries 1 and 2 take effect, and I can't get them to rollback.

The documentation states that if there is an sql error, it will automatically issue a rollback, but I'm trying to roll it back if there is a php error, such as a timeout or something.

I'm using MySQL 5.7, phpBB 3.0.11, and php 5.6.

Can someone point out the issue?

$db->sql_transaction('begin');

try {
    $sql = 'UPDATE aaa_temp SET method = "a" WHERE id = 1';
    $db->sql_query($sql);

    $sql = 'UPDATE aaa_temp SET method = "b" WHERE id = 2';
    $db->sql_query($sql);

    throw new Exception('OMG TOTAL ERROR');

    $sql = 'UPDATE aaa_temp SET method = "c" WHERE id = 3';
    $db->sql_query($sql);

} catch (Exception $ex) {
    $db->sql_transaction('rollback');
    trigger_error($ex->getMessage(), E_USER_ERROR);
}

$db->sql_transaction('commit');

Thanks in advance! I know it's got to be something really simple and stupid, but let me know if more detail is needed.

EDIT: Just so it's clear, I'm talking about SQL transactions in MySQL.

Answer 1

It turns out that the table I was trying to rollback updates to was using the MyISAM storage engine, which doesn't support transactions.

Ironically, that is specifically how phpBB builds them. sql_transaction('rollback') returned true because the rollback did indeed work, it just didn't pick up on the SQL warning about data not being rolled back.

Figured this out by enabling the general_query_log in MySQL to see exactly what this function was doing in the background:

SET AUTOCOMMIT=0;

UPDATE aaa_temp SET method = "a" WHERE id = 1;

UPDATE aaa_temp SET method = "b" WHERE id = 2;

ROLLBACK;

SET AUTOCOMMIT=1;

So since I had a mixture of MyISAM and InnoDB tables, changes to the latter ones were being rolled back while changes to the formers were not.