Transaction rollback doesn't work

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


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


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.