Martin AJ Martin AJ - 4 months ago 12
MySQL Question

How PDO transaction api works?

Here is my code structure:

// db connection here

try {
$dbh_conn->beginTransaction();

$stm = $dbh_conn->prepare("SELECT user_id FROM resend_pass WHERE token = ?");
$stm->execute(array('value'));
$num_rows = $stm->fetch(PDO::FETCH_ASSOC);

if($num_rows) {
echo 'one'; die;

} else {
echo 'two'; die;

}

$dbh_conn->commit();

} catch(PDOException $e) {
$dbh_conn->rollBack();

echo 'three'; die;

}


When exactly that query executes? You know, my script works exactly as expected. But I'm wonder how? As you see there is an
if - else
statement before
commite()
; Also both
if
and
else
have
die;
in their blocks. So as far as I know, this line never executes:

$dbh_conn->commit();


Because there is surly a
die
before it. But surprisingly my code works as well. Here is all possible outputs:


  • It prints
    one
    if
    value
    exists as a token in the
    resend_pass
    table.

  • It prints
    two
    if
    value
    doesn't exist as a token in the
    resend_pass
    table.

  • It prints
    three
    if there is an error (like syntax SQL error)



See? All fine. But how? When exactly
commit()
function executes? Before those
die
s ?

Note: The engine of
resend_pass
is innoDB.

Answer

Queries execute when execute() is called (note that query() calls execute()).

If the query changes data in a table that supports transactions, the change is rolled back if the PDO object is freed, either because the object goes out of scope or the script terminates and cleans up. This is due to the PDO code, not MySQL.

If the query changes data in a table that does not support transactions (e.g. a MyISAM table), the change is permanent at the time it is executed, and it cannot be rolled back.

There are some SQL statements that perform an implicit commit. They would be made permanent as soon as they executed, even if your script dies before calling commit().

As @MarcB mentioned, your example shows a read-only SELECT statement. It would be easier to test the behavior if you use an INSERT/UPDATE/DELETE.