Why a implict commit (implicitly committed transaction) is required in the database

Look at a SQL first, the last SQL output what do you think it is?

SET autocommit = 1; Begin;insert into T1 VALUES (1); CREATE TABLE T2 (PK int primary key); INSERT into T2 VALUES (2); ROLLBACK; SHOW TABLES;

The answer is: T1, t2 all exist!

Mysql> Show tables;+----------------+| Tables_in_test |+----------------+| T1             | | T2             |+----------------+2 rows in Set (0.00 sec)

What's even weirder: 1 of T1 is also inserted successfully!

Mysql> SELECT * FROM t1;+----+| PK |+----+|  1 |+----+1 row in Set (0.00 sec)

Why did rollback not take effect? The answer is: implict Commit
The previous transaction was implicitly committed before the CREATE TABLE statement was executed. Because of the autocommit=1, it will not automatically create any new transactions after the commit, the INSERT statement is executed immediately after execution, rollback will not function on any transaction, so we have the last seen results.

A little change, let autocommit=0, what will happen?

SET autocommit = 0; Begin;insert into T1 VALUES (1); CREATE TABLE T2 (PK int primary key); INSERT into T2 VALUES (2); ROLLBACK; SHOW TABLES;

The answer is: T1, t2 all exist! 1 inserted into the T1 is committed (inserted successfully) but inserted into T2 2 is rolled back (no insert succeeded). The reason why T1 1 is submitted is because create table leads to implictcommit (note that it is a commit, not a rollback!). ), when the insert is executed, a new transaction is automatically opened (the behavior of the autocommit=0 semantics requirement). So 2 of T2 was rollback rolled back.

Why does part of the operation cause implict Commit? Why is this design?
In order to ensure the intuitive atomicity. Suppose you don't do implict Commit and see what the above statement looks like: The user's psychological expectation is to roll back the T1 insert operation, as well as the T2 create operation, insert operation. If we have the ability to do that, it is perfect. But in fact it's hard to do it, especially in distributed systems! Because the CREATE table operation involves a large number of operations, not only the operation of the core table, but also a large number of memory data structure updates (such as schema), as well as storage system changes (such as the creation of corresponding data blocks), the project is difficult to make these operations atomic.

So, what should we do? The tradeoff is to make a pact with the user: the CREATE table operation always defaults to commit its previous transaction, which is implict commit.

From the MySQL documentation, they encountered a lot of problems when they made this piece, at least two pits were trampled here. And, as the version evolves, they continue to allow more statements to trigger implict commits. In the end, please refer to the MySQL documentation for which statements will cause implict commit: http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

Why a implict commit (implicitly committed transaction) is required in the database