MySQL transaction autocommit Auto-commit

The MySQL default mode of operation is autocommit auto-commit mode. This means that unless a transaction is explicitly started, each query is executed automatically as a separate transaction. We can change whether the autocommit mode is auto-committed by setting the value of autocommit.

Use the following command to view the current autocommit mode

Mysql> Show variables like ' autocommit ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| autocommit | On |

+---------------+-------+

1 row in Set (0.04 sec)

From the query results, we find that value is on, indicating that autocommit is turned on. We can change this pattern with the following SQL statement

1

mysql> Set autocommit = 0;

The value 0 and off are the same, of course, 1 means on. With the above setting autocommit=0, the user will remain in a transaction until a commit commit or ROLLBACK statement is executed to end the current transaction and start a new transaction again.

As an example:

Zhang San to John Doe transfer 500 Yuan. The following actions should be in the database:

1, first check whether the Zhang San account balance is sufficient

2, minus $500 on Zhang San's account.

3, plus $500 on John Doe's account.

The above three steps can be placed in a transaction to execute the commit, either all execution or all do not execute, if all OK commits to commit the permanent change data, if the error rollback rollback to the state before the change. Using transaction processing will not appear Zhang San money John Doe account but no increase of 500 yuan or Zhang San money without subtracting John Doe account but added 500 yuan.

The MySQL default storage engine is the Myisam,myisam storage engine does not support transactional processing, so changing the autocommit has little effect. But will not error, so to use transaction processing of children's shoes be sure to determine what you are doing to support transactional processing, such as InnoDB. If you do not know that the storage engine for the table can view the build table by looking at the table statement, there is no storage engine that specifies the transaction type, and if you do not specify the storage engine by default, the MyISAM does not support transactions.

Of course, transaction processing is to ensure the atomicity, consistency, isolation and persistence of table data. These are to consume system resources, be careful to choose.

MySQL transaction autocommit Auto-commit