2

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?

Jacobian
  • 10,122
  • 29
  • 128
  • 221
  • 1
    Check: [13.3.3 Statements That Cause an Implicit Commit](https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html). – wchiquito Dec 02 '15 at 10:20
  • Goodness, what?! Such a long list of statements. It is unbelievable. – Jacobian Dec 02 '15 at 10:25
  • BTW. I've just tested it, that if I add `INSERT` to the table right after `CREATE`, then after rollback I still can see that data was added to the table - contrary to what I wanted to achive. Does it mean that MySQL is so counterintuitive and in fact rubbish?? – Jacobian Dec 02 '15 at 10:43
  • You would have to start a new transaction after the `CREATE` and before the `INSERT`, because the previous transaction was committed, and thus, it's over. – marmarta Dec 02 '15 at 10:46

1 Answers1

2

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

marmarta
  • 838
  • 5
  • 20
  • Thank you! That is absolutely right. I've just checked it and it works like you are saying. – Jacobian Dec 02 '15 at 10:49
  • By the way, it seems like in many other popular databases, the behaviour of transactions is more intuitive. – Jacobian Dec 02 '15 at 10:50
  • Well, that depends. SQL Server, yes - but Oracle behaves very much like MySQL in this case. – marmarta Dec 02 '15 at 10:56
  • As for Oracle, if I understand it correct from here - http://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql - then it behaves in a different manner, than MySQL. At least, modern versions of Oracle. – Jacobian Dec 02 '15 at 11:05
  • Yes and no. By default, modern versions of Oracle (including 11.2 and 12.1) perform implicit commit before all DDL operations, see https://docs.oracle.com/database/121/SQLRF/statements_4011.htm#SQLRF01110 . "edition-based redefinition" is a bit different and requires some work from the DBA. – marmarta Dec 02 '15 at 11:19