MySQL transaction autocommit automatic commit operation

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

You can view the current autocommit mode by using 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 find that the value of Value is ON, which means autocommit is turned on. We can change this mode with the following SQL statement

mysql> set autocommit = 0;

The value 0 is the same as OFF, and of course, 1 means ON. By setting autocommit=0 above, the user will remain in a transaction until a commit or rollback statement is executed to end the current transaction and start a new one.

For example, Zhang San transfers 500 yuan to Li Si. Then the operations in the database should be as follows:

1. First check whether Zhang San’s account balance is sufficient

2. 500 yuan is deducted from Zhang San's account

3. Add 500 yuan to Li Si’s account

The above three steps can be put into one transaction for commit. Either all or none of them are executed. If everything is OK, commit to permanently change the data; if there is an error, rollback to the state before the change. By using transaction processing, it will not happen that Zhang San's money is reduced but Li Si's account is not increased by 500 yuan, or Zhang San's money is not reduced but Li Si's account is increased by 500 yuan.

The default storage engine for MySQL is MyISAM, which 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, make sure that the database you are operating supports transaction processing, such as InnoDB. If you don't know the storage engine of the table, you can check the table creation statement to see if there is a storage engine with a transaction type specified when the table was created. If no storage engine is specified, the default storage engine is MyISAM, which does not support transactions.

Of course, transaction processing is to ensure the atomicity, consistency, isolation, and persistence of table data.

These will consume system resources, so choose carefully.

Supplement: MySQL transaction processing (Transation) and automatic execution (AutoCommit) and submission type (Completion)

1. Transaction

In computer terms, a transaction refers to a unit of program execution that accesses and possibly updates various data items in a database. Transactions are mainly used to process data with large operation volume and high complexity. If you want to delete a piece of information in a master table, and the master table has multiple slave tables, you need to delete the details step by step and then delete the master table information. This process is extremely error-prone. In this case, it is most appropriate to use a transaction to handle it.

2. Usage of Transactions

Start transaction or begin

Commit

Rollback

3. Engines supported by MYSQL (InnoDB)

show engines;

4. AutoCommit and Completion

There are two ways to use transactions: implicit transactions and explicit transactions. Implicit transactions are actually automatic commits. Oracle does not automatically commit by default, and you need to write COMMIT manually. In MySQL, autocommit is enabled in engines that support transactions. If autocommit=true, statements are directly submitted without committing to make permanent changes. MySQL turns on autocommit by default, and it can also be set through configuration.

set autocommit=0;(AutoCommit Off)
set autocommit=1;(AutoCommit On)
set completion_type=0;(No Chain)
set completion_type=1;(Chain)
set completion_type=2;(Release)

You can also query the current configuration through statements

show variables like '%autocommit%';
show variables like '%completion%'; 

5. Verification example

mysql> BEGIN;
 -> INSERT INTO test SELECT 'Guan Yu';
 -> COMMIT;
 -> BEGIN;
 -> INSERT INTO test SELECT '张飞';
 -> INSERT INTO test SELECT '张飞';
 -> ROLLBACK;
 -> SELECT * FROM test;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1062 (23000): Duplicate entry '张飞' for key 'PRIMARY'

mysql> select * from test;//

Current window results:

New window result:

Conclusion: Due to the uniqueness constraint of name in the test table, looking at the code, after the two "Zhang Fei" are executed (regardless of whether the primary key constraint is triggered or not), the second "Zhang Fei" actually triggers the uniqueness constraint exception, so I think the transaction will jump out. Therefore, in the current connection, one Zhang Fei can be seen in the test table. In fact, the second transaction was not submitted successfully.

Next, let's try submitting data normally.

mysql> BEGIN;
 -> INSERT INTO test SELECT 'Guan Yu';
 -> COMMIT;
 -> BEGIN;
 -> INSERT INTO test SELECT '张飞';
 -> INSERT INTO test SELECT 'Liu Bei';
 -> ROLLBACK;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.04 sec)


mysql> select * from test;//

Current window results:

New window result:

Conclusion: Compared with the test results this time, I think that the first test did not execute rollback, but jumped out of the transaction processing exception mechanism.

Because MySQL turns on autocommit by default, I want to verify how the two "Zhang Fei" deal with each other when there is no explicit transaction (that is, no begin)?

mysql> BEGIN;
 -> INSERT INTO test SELECT 'Guan Yu';
 -> COMMIT;
 -> INSERT INTO test SELECT '张飞';
 -> INSERT INTO test SELECT '张飞';
 -> ROLLBACK;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

ERROR 1062 (23000): Duplicate entry '张飞' for key 'PRIMARY'
mysql> select *from test;//

Current window results:

New window result:

Conclusion: In fact, you can tell from the execution results that the first "Zhang Fei" has been successfully executed as a separate transaction, and the second "Zhang Fei" failed because the primary key constraint was triggered. In addition to displaying the transaction, is there any way to merge the two "Zhang Fei" into one transaction before ending the transaction (without Commit)?

You can set completion_type=1;(chain), which means that no matter how many "Zhang Fei"s there are, they are all one transaction before they are submitted.

The code is the same as above.

mysql> SET @@completion_type = 1;
 -> BEGIN;
 -> INSERT INTO test SELECT 'Guan Yu';
 -> COMMIT;
 -> INSERT INTO test SELECT '张飞';
 -> INSERT INTO test SELECT '张飞';
 -> ROLLBACK;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1062 (23000): Duplicate entry '张飞' for key 'PRIMARY'

Current window results:

New window result:

Conclusion: Because I set completion_type = 1 (chain), which is equivalent to adding begin before the first "Zhang Fei", the second "Zhang Fei" triggered the primary key constraint, causing the transaction to fail and data insertion to fail.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.