CODEWITHSUNDEEP
mysqlsql
Jacobian
Jacobian

Reputation: 10872

Rollback does not work in MySQL

I'm using InnoDb engine by default. And this is what looks strange:

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

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test_1(id int);
Query OK, 0 rows affected (0.07 sec)

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

mysql> show tables;
+------------------+
| Tables_in_reestr |
+------------------+
| test_1          |
+------------------+
1 rows in set (0.00 sec)

It looks strange, because I started transaction and rollbacked, but to no avail. So, what I'm doing wrong?

Upvotes: 2

Views: 2180

Answers (1)

marmarta
marmarta

Reputation: 838

To expand on the comment above: in MySQL, basically all operations that alter database objects perform auto-commit. The main categories are:

  1. any DDL on your objects, like CREATE/ALTER/DROP TABLE/VIEW/INDEX...,
  2. anything that modifies the system database mysql, like ALTER/CREATE USER,
  3. any administrative commands, like ANALYZE,
  4. any data loading/replication statements.

Actually, I find it best to assume that INSERT, UPDATE and DELETE are safe, and anything else is not.

Source: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Upvotes: 2

Related Questions