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:
one if value exists as a token in the resend_pass table.two if value doesn't exist as a token in the resend_pass table.three if there is an error (like syntax SQL error)See? All fine. But how? When exactly commit() function executes? Before those dies ?
Note: The engine of resend_pass is innoDB.
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.