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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.