Depending on whether “autocommit” is enabled or disabled, the transaction start / end method changes.
When auto commit is enabled(It is enabled by default)
The transaction in this case is started implicitly at the execution of single SQL statement or explicitly by the START TRANSACTION statement.
The behavior changes depending on whether you execute the START TRANSACTION statement or not in this.Generally, there are the following differences.
1. When START TRANSACTION statement is executed
It does not commit when a single SQL statement is executed, but commits only when a COMMIT statement is executed. Roll back if you execute the ROLLBACK statement.
2. If you did not execute the START TRANSACTION statement
Commit automatically when a single SQL statement is executed. You can not roll back.
Case 2 is similar to the operation is MyISAM table which is always committed immediately.
If you disable autocommit
The transaction in this case is started implicitly by the execution of the SQL statement. Or, explicitly start by executing the START TRANSACTION statement. In either case, it does not commit automatically, but is committed only when the COMMIT statement is executed. Also, roll back if you execute the ROLLBACK statement.
The meaning of “a transaction starts implicitly by the execution of a SQL statement” means that a transaction is automatically started even if you forget to execute a START TRANSACTION statement.
Disabling auto-commit always assumes that the user has a transaction open. Therefore, the user explicitly COMMITs the behavior that the current transaction is ended and then a new transaction is started.
SELECT @@autocommit;
[vagrant@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.41 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> SELECT @@autocommit;
+————–+
| @@autocommit |
+————–+
| 1 |
+————–+
1 row in set (0.06 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)
mysql> select @@autocommit;
+————–+
| @@autocommit |
+————–+
| 0 |
+————–+
1 row in set (0.00 sec)
