COMMIT ROLLBACK 和 AUTOCOMMIT

AUTOCOMMIT

MySQL 自动提交不属于事务的语句。任何 UPDATE，DELETE 或 INSERT 语句之前没有 BEGIN 或 START TRANSACTION 的结果将立即对所有连接可见。

AUTOCOMMIT 变量默认设置为 true 。这可以通过以下方式更改，

--->To make autcommit false
SET AUTOCOMMIT=false;
--or
SET AUTOCOMMIT=0;

--->To make autcommit true
SET AUTOCOMMIT=true;
--or
SET AUTOCOMMIT=1;

查看 AUTOCOMMIT 状态

SELECT @@autocommit;

承诺

如果 AUTOCOMMIT 设置为 false 且未提交事务，则仅对当前连接可见更改。

在 COMMIT 语句提交对表的更改后，结果将对所有连接可见。

我们考虑两个连接来解释这一点

连接 1

--->Before making autocommit false one row added in a new table
mysql> INSERT INTO testTable VALUES (1);

--->Making autocommit = false
mysql> SET autocommit=0;

mysql> INSERT INTO testTable VALUES (2), (3);    
mysql> SELECT * FROM testTable;
+-----+
| `tId` |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

连接 2

mysql> SELECT * FROM testTable;
+-----+
| `tId` |
+-----+
|   1 |
+-----+
---> Row inserted before autocommit=false only visible here

连接 1

mysql> COMMIT;
--->Now COMMIT is executed in connection 1
mysql> SELECT * FROM testTable;
    +-----+
    | tId |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    +-----+

连接 2

mysql> SELECT * FROM testTable;
    +-----+
    | tId |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    +-----+
--->Now all the three rows are visible here

ROLLBACK

如果查询执行出错，ROLLBACK 用于恢复更改。请参阅以下说明

--->Before making autocommit false one row added in a new table
mysql> INSERT INTO testTable VALUES (1);

--->Making autocommit = false
mysql> SET autocommit=0;

mysql> INSERT INTO testTable VALUES (2), (3);    
mysql> SELECT * FROM testTable;
+-----+
| `tId` |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

现在我们正在执行 ROLLBACK

--->Rollback executed now
mysql> ROLLBACk;

mysql> SELECT * FROM testTable;
+-----+
| `tId` |
+-----+
|   1 |
+-----+
--->Rollback removed all rows which all are not committed

一旦 COMMIT 被执行，那么 ROLLBACK 将不会产生任何结果

mysql> INSERT INTO testTable VALUES (2), (3);    
mysql> SELECT * FROM testTable;
mysql> COMMIT;
+-----+
| `tId` |
+-----+
|   1 |
|   2 |
|   3 |
+-----+

--->Rollback executed now
mysql> ROLLBACk;

mysql> SELECT * FROM testTable;
+-----+
| `tId` |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
--->Rollback not removed any rows

如果 AUTOCOMMIT 设置为 true ，那么 COMMIT 和 ROLLBACK 就没用了