How can a user start new MySQL transaction?

By running the command START TRANSACTION, a user can start new MySQL transaction. The behavior of the transaction will depend upon the SQL AUTOCOMMIT mode. The default mode is ‘AUTOCOMMIT ON’ mode where each MySQL statement is considered as a complete transaction and committed by default when it finishes. It can be started by setting the session variable AUTOCOMMIT to 1 as follows −

SET AUTOCOMMIT = 1

mysql> SET AUTOCOMMIT = 1;
Query OK, 0 rows affected (0.07 sec)

If a user wants to change such kind of behavior of MySQL transaction then he/she can set ‘AUTOCOMMIT OFF’ SQL mode in which the subsequent series of MySQL statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued. In this mode, the first executable statement of a new session will implicitly start a new multi-statement transaction. It can be started by setting the session variable AUTOCOMMIT to 0 as follows −

SET AUTOCOMMIT = 0

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

For Transaction in InnoDB instead of using SET AUTOCOMMIT = 0, commit with COMMIT command. 

In both of the SQL modes, the transaction will be started with the START TRANSACTION command as follows −

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

Actually, the above query notifies MySQL that the statements that follow should be treated as a single work unit until the transaction has been ended.