PDO Transaction committing instead of rolling back

Question

So there are 2 InnoDB tables, employees and companies. I use a try/catch block to insert 2 queries in a transaction. However, when I make an explicit mistake in the first query (I put an incorrect table name, employee instead of employees), the database doesn't insert the rows for that query of course, but the second query runs and gets committed to the database instead of rolling back (since the first query failed to be inserted).

The result is an emply employees table, but companies table with the new record. What am I missing here? Shouldn't it rollback since the 1st query was not inserted?

$employee_id = 2;
$employee_name = 'Marky Mark';

try {
   $dbh->beginTransaction();
   $query = "INSERT INTO employee (employee_name) VALUES (:employee_name)";
   $insert_emp = $dbh->prepare($query);
   $insert_emp->execute(array(':employee_name' => $employee_name));

   $Employee_id = $dbh->lastInsertId();


   $query = "INSERT INTO companies (company_name,employee_id) VALUES ('SO',:Employee_id)";
   $insert_emp_comp = $dbh->prepare($query);
   $insert_emp_comp->execute(array(':Employee_id' => $Employee_id));
   $dbh->commit();
} catch (Exception $e) {
   $dbh->rollBack();
   echo "Failed: " . $e->getMessage();
}

Obvoiusly execute does not throw exception, since the code is executed up to the companies insert statement — Maxim Krizhanovsky

jnrbsn jnrbsn · Accepted Answer · 2012-04-13T18:30:52

PDO doesn't throw exceptions by default. You have to configure it to throw exceptions:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Check out this page for more info on how PDO handles errors.

PDO Transaction committing instead of rolling back

1 Answers