Transaction rollback doesn't work

Question

I have made a database wrapper with extra functionality around the PDO system (yes, i know a wrapper around a wrapper, but it is just PDO with some extra functionality). But i have noticed a problem.

The folowing doesn't work like it should be:

<?php
var_dump($db->beginTransaction());

$db->query('
 INSERT INTO test
 (data) VALUES (?)
 ;',
 array(
  'Foo'
 )
);
print_r($db->query('
 SELECT *
 FROM test
 ;'
)->fetchAll());

var_dump($db->rollBack());

print_r($db->query('
 SELECT *
 FROM test
 ;'
)->fetchAll());
?>

The var_dump's shows that the beginTransaction and rollBack functions return true, so no errors.

I expected that the first print_r call show a array of N items and the second call show N-1 items. But that issn't true, they both show same number of items.

My $db->query(< sql >, < values >) call nothing else then $pdo->prepare(< sql >)->execute(< values >) (with extra error handling ofcourse).

So i think or the transaction system of MySQL doesn't work, or PDO's implenmentaties doesn't work or i see something wrong.

Does anybody know what the problem is?

Answer 1

Check if your type of database equals innoDB. In one word you must check if your database supports transactions.

Answer 2

Two possible problems:

1. The table is MyISAM which doesn't support transaction. Use InnoDB.

2. Check to make sure auto-commit is OFF.

http://www.php.net/manual/en/pdo.transactions.php