MySQL Transaction COMMIT , ROLLBACK and AUTOCOMMIT
Example
AUTOCOMMIT
MySQL automatically commits statements that are not part of a transaction. The results of any UPDATE,DELETE or INSERT statement not preceded with a BEGIN or START TRANSACTION will immediately be visible to all connections.
The AUTOCOMMIT variable is set true by default. This can be changed in the following way,
--->To make autcommit false
SET AUTOCOMMIT=false;
--or
SET AUTOCOMMIT=0;
--->To make autcommit true
SET AUTOCOMMIT=true;
--or
SET AUTOCOMMIT=1;
To view AUTOCOMMIT status
SELECT @@autocommit;
COMMIT
If AUTOCOMMIT set to false and the transaction not committed, the changes will be visible only for the current connection.
After COMMIT statement commits the changes to the table, the result will be visible for all connections.
We consider two connections to explain this
Connection 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 |
+-----+
Connection 2
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
+-----+
---> Row inserted before autocommit=false only visible here
Connection 1
mysql> COMMIT;
--->Now COMMIT is executed in connection 1
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
Connection 2
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
--->Now all the three rows are visible here
ROLLBACK
If anything went wrong in your query execution, ROLLBACK in used to revert the changes. See the explanation below
--->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 |
+-----+
Now we are executing ROLLBACK
--->Rollback executed now
mysql> ROLLBACk;
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1 |
+-----+
--->Rollback removed all rows which all are not committed
Once COMMIT is executed, then ROLLBACK will not cause anything
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
If AUTOCOMMIT is set true, then COMMIT and ROLLBACK is useless
