MySQL’s default TRANSACTION auto-commit mode is enabled, meaning that every SOL statement is automatically executed as a separate TRANSACTION unless it is explicitly started (BEGIN or START TRANSACTION). However, in some cases, we need to turn off transaction auto-commit to ensure data consistency. The following describes how to set the transaction auto-commit mode. In MySQL, you can view the current auto-commit mode of the transaction by using the SHOW VARIABLES statement, as shown below:

mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.04 SEC)Copy the code

If the value of autocommit is ON, the system enables the automatic commit mode. In MySQL, you can use the SET autocommit statement to SET the automatic commit mode of a transaction.

SET autocommit = 0|1|ON|OFF;

Description of the value: \

  • A value of 0 and a value of OFF disables automatic transaction commit. If auto commit is turned off, the user will remain in a transaction until the current transaction is finished and a new one is started after a commit or rollback.
  • The value 1 and the value ON enable automatic transaction commit. If auto commit is enabled, the transaction is committed once for each SQL statement executed.

The sample

Let’s turn off automatic transaction commit and simulate a bank transfer. SET autocommit = SET autocommit = SET autocommit = SET autocommit = SET autocommit

mysql> SET autocommit = 0; ; Query OK, 0 rows affected (0.00 SEC) mysql> SELECT * FROM mybank.bank; + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | customerName | currentMoney | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | zhang SAN 1000.00 | | | |, dick, and harry 1.00 | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC) mysql > UPDATE bank set currentMoney = currentMoney - 500 WHERE customerName=' customerName '; Query OK, 1 row affected (0.02sec) Rows matched: 1 Warnings: 1 Warnings: 1 0 mysql> UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName=' customerName '; Query OK, 1 row affected (0.00 SEC) Rows matched: 1 Changed: 1 Warnings: 0Copy the code

Then open a CMD window again and check the balance of Zhang SAN and Li Si in the bank data table. The SQL statement and run result are as follows:

mysql> SELECT * FROM mybank.bank; + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | customerName | currentMoney | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | zhang SAN 1000.00 | | | |, dick, and harry 1.00 | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

The result shows that the balance of Zhang SAN and Li Si is the data before the transaction execution. Select * from ‘bank’ where ‘bank’ = ‘bank’ and ‘bank’ = ‘bank’;

mysql> COMMIT; Query OK, 0 rows affected (0.07 SEC) mysql> SELECT * FROM mybank.bank; + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | customerName | currentMoney | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | zhang SAN 500.00 | | | |, dick, and harry 501.00 | + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

The result shows that the data of bank data table is updated successfully. In this case, after autocommit is turned off, this location is used as the starting point for a transaction that does not end until the COMMIT and ROLLBACK statements are executed. After that, this is where the next transaction starts. After the automatic COMMIT function is disabled, MySQL commits the data in the data table to the database only after the COMMIT command is executed. If you run the ROLLBACK command, the data will be rolled back. If you terminate the MySQL session without committing the transaction, the database will automatically perform the rollback operation. After a TRANSACTION is started with BEGIN or START TRANSACTION, automatic COMMIT remains disabled until the TRANSACTION is finished with COMMIT or ROLLBACK. After that, autocommit mode reverts to the previous state, that is, if autocommit = 1 before BEGIN, autocommit remains 1 after the transaction completes. If AUTOCOMMIT = 0 before BEGIN, autoCOMMIT remains 0 after the transaction is completed.