Why does the database require ImplictCommit (implicit commit of transactions)

Why does the database require ImplictCommit (implicit commit of transactions)

First look at an SQL statement. What do you think is the output of the last SQL statement?

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 and t2 both exist!

mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1             || t2             |+----------------+2 rows in set (0.00 sec)

What's more strange is that 1 in t1 is also inserted successfully!

mysql> select * from t1;+----+| pk |+----+|  1 |+----+1 row in set (0.00 sec)

Why does ROLLBACK not take effect? The answer is: Implict Commit.
Before executing the create table statement, the previous transaction is implicitly committed. Because AUTOCOMMIT = 1, no new transactions will be automatically created after the commit, and the INSERT statement will be submitted immediately after execution. ROLLBACK will not function in any transactions, so we get the final result.

How can I change AUTOCOMMIT to 0?

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 and t2 both exist! 1 inserted to t1 is submitted (inserted successfully) but 2 inserted to t2 is rolled back (not inserted successfully ). The reason why 1 in t1 is submitted is that create table causes ImplictCOMMIT (Note: It is COMMIT, not ROLLBACK !), When an INSERT statement is executed, a new transaction is automatically started (the behavior of AUTOCOMMIT = 0 ). SO 2 in t2 is rolled back by ROLLBACK.

Why is Implict Commit caused by some operations? Why is this design?

To ensure the atomicity. Assume that Implict Commit is not performed. Let's see what the preceding statement will do: the user's mental expectation is to roll back the INSERT operation of t1, the CREATE operation of t2, And the INSERT operation. If we have the ability to do this, it is indeed perfect. But in fact, it is hard to do it, especially in distributed systems! Because the create table operation involves a lot of operations, not only core TABLE operations, but also Memory Data Structure updates (such as Schema ), as well as changes to the storage system (such as creating corresponding data blocks), it is difficult for the project to make these operations atomic.

So what should we do? The method of comparison is to make an agreement with the user: the create table operation always COMMIT the transactions before it by default, which is implict commit.

From the MySQL documentation, they encountered many problems when doing this, at least two pitfalls. In addition, with the evolution of the version, they continue to make more statements can trigger implict commit.