MySQL transaction autocommit automatically commits

The default operating mode of MySQL is autocommit automatic submission mode. This means that unless you explicitly start a transaction, each query is automatically executed as a separate transaction. We can change whether the autocommit mode is automatically submitted by setting the value of autocommit.

You can check the current autocommit mode through the following command

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.04 sec)

From the query results, we found that the value of Value is ON, indicating that autocommit is on. We can change this mode through the following SQL statement

mysql> set autocommit = 0;

The values ​​0 and OFF are the same. Of course, 1 means ON. By setting autocommit=0 above, the user will always be in a transaction until a commit or rollback statement is executed to end the current transaction and start a new transaction.

For example:

Zhang San transfers 500 yuan to Li Si. Then the following operations should be performed in the database:

1, first check whether Zhang San’s account balance is sufficient

2, subtract 500 yuan from Zhang San’s account

3 , add 500 yuan to Li Si's account

The above three steps can be placed in one transaction to execute the commit, either all execution or none execution, if everything is OK, commit to submit the permanent change data; If an error occurs, rollback rolls back to the state before the change. Using transaction processing, it will not happen that Zhang San's money is reduced by Li Si's account but does not increase by 500 yuan, or Zhang San's money is not subtracted from Li Si's account but 500 yuan is added.

The default storage engine of MySQL is MyISAM. The MyISAM storage engine does not support transaction processing, so changing autocommit has no effect. But no error will be reported, so if you want to use transaction processing, you must make sure that the one you are operating supports transaction processing, such as InnoDB. If you don't know the storage engine of the table, you can check whether there is a storage engine that specifies the transaction type when creating the table by looking at the table creation statement. If the storage engine is not specified, the default storage engine is MyISAM that does not support transactions.

Of course, transaction processing is to ensure the atomicity, consistency, isolation, and durability of table data. These all consume system resources, so choose carefully.

The above is the content automatically submitted by MySQL transaction autocommit. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!