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 就沒用了