How does a user start a new MySQL transaction?

Users can start a new MySQL transaction by running the command START TRANSACTION. The behavior of the transaction will depend on the SQL AUTOCOMMIT mode. The default mode is "AUTOCOMMIT ON" mode, where each MySQL statement is treated as a complete transaction and commits by default on completion. It can be started by setting the session variable AUTOCOMMIT to 1 as shown below -

SET AUTOCOMMIT = 1

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

If the user wants to change this behavior of MySQL transactions, then he/she can set the "AUTOCOMMIT OFF" SQL mode, in In this mode, the subsequent series of MySQL statements acts like a transaction, and no activity is committed until an explicit COMMIT statement has been issued. In this mode, the first executable statement of a new session implicitly starts a new multi-statement transaction. It can be started by setting the session variable AUTOCOMMIT to 0 as shown below -

SET AUTOCOMMIT = 0

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

For transactions in InnoDB, do not use SET AUTOCOMMIT = 0, instead use the COMMIT command to commit.

In both SQL modes, transactions will be started using the START TRANSACTION command, as follows -

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

In fact, the above query informs MySQL that the following statements should be treated as a single unit of work , until the transaction ends.

The above is the detailed content of How does a user start a new MySQL transaction?. For more information, please follow other related articles on the PHP Chinese website!