Transaction rollback doesn't work

Question

Hi all!

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

MySQL doesn't support transactions on the MyISAM table type, which is unfortunately the default table type.

If you need transactions, you should switch to the InnoDB table type.

Answer 3

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

Answer 4

I'm entering this as an answer, as a comment is to small to contain the following:

PDO is just a wrapper around the various lower level database interface libraries. If the low-level library doesn't complain, either will PDO. Since MySQL supports transactions, no transaction operations will return a syntax error or whatever. You can use MyISAM tables within transactions, but any operations done on them will be done as if auto-commit was still active:

mysql> create table myisamtable (x int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table innodbtable (x int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into myisamtable (x) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into innodbtable (x) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from myisamtable;
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from innodbtable;
Empty set (0.00 sec)

mysql>

As you can see, even though a transaction was active, and some actions were performed on the MyISAM table, no errors were thrown.