Transaction rollback does not work

Question

I created a database wrapper with extra functionality around the PDO system (yes, I know a wrapper around the shell, but it's just a PDO with some additional features). But I noticed a problem.

The following does not work as it should:

<?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());
?>

var_dump shows that the beginTransaction and rollBack functions return true, so there are no errors.

I expected the first call to print_r to display an array of N elements, and the second to N-1. But this is not so, both of them show the same number of elements.

My query $ db-> (<sql>, <values>) does not call anything other than $ pdo-> prepare (<sql>) → execute (<values>) (with additional error handling in the course).

, , MySQL , PDO implenmentaties - .

- , ?

Answer 1

, innoDB. , , .

Answer 2

:

• MyISAM, . InnoDB.

• , .

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

Answer 3

, , :

PDO - . , PDO. MySQL , - . MyISAM , , :

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>

, MyISAM, .

Answer 4

MySQL MyISAM, , .

, InnoDB.

Answer 5

, , , SQL . , , . , , ALTER TABLE .

, :

• , , ALTER TABLE, CREATE TABLE ..
• , , , ALTER USER SET PASSWORD
• ,
• , ANALYZE TABLE, FLUSH CACHE INDEX
• Replication management statements, for example, anything related to a slave or master

Further information and a complete list can be found here: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

If you have this problem with only a specific script and you are sure that you are using InnoDB, you might want to see if any SQL statements in your script match this.