Tips and steps for using mysql transaction processing mechanism

Transaction Control
MySQL supports local transactions through statements such as SET AUTOCOMMIT, START TRANSACTION, COMMIT and ROLLBACK.
Syntax:
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}
By default, MySQL is autocommit. If you need to commit and rollback transactions through explicit commit and rollback, you need to start the transaction through explicit transaction control commands. This is in conjunction with Oracle's transaction management. The obvious difference is that if the application is migrated from an Oracle database to a MySQL database, you need to ensure that transactions are clearly managed in the application.
START TRANSACTION or BEGIN statement can start a new transaction.
COMMIT and ROLLBACK are used to commit or rollback transactions.
CHAIN ​​and RELEASE clauses are used to define operations after transaction submission or rollback respectively. chain will immediately start a new thing with the same isolation level as the previous transaction, and release will disconnect from the client.
SET AUTOCOMMIT can modify the submission method of the current connection. If SET AUTOCOMMIT=0 is set, all transactions after setting need to be submitted or rolled back through explicit commands.
If we only need transaction control for certain statements, it is more convenient to use START TRANSACTION to start a transaction, so that after the transaction ends, we can automatically return to the automatic submission method. If we want all our transactions
not to be automatically submitted, Then it is more convenient to control transactions by modifying AUTOCOMMIT, so that START TRANSACTION does not need to be executed at the beginning of each transaction.
Therefore, in the same transaction, it is best not to use tables with different storage engines. Otherwise, special processing is required for non-transaction type tables during rollback, because commit and rollback can only commit and rollback transaction type tables. .
Normally, only committed transactions are recorded in the binary log, but if a transaction contains non-transactional tables, the rollback operation will also be recorded in the binary log to ensure the update of the non-transactional tables. Can be copied to the slave database.
Same as Oracle's transaction management, all DDL statements cannot be rolled back, and some DDL statements will cause implicit submission.
In a transaction, you can specify a part of the transaction to be rolled back by defining a savepoint, but you cannot specify a part of the transaction to be committed. For complex applications, multiple different savepoints can be defined, and different savepoints can be rolled back when different conditions are met. It should be noted that if a savepoint with the same name is defined, the savepoint defined later will overwrite the previous definition. For savepoints that are no longer needed, you can use the release savepoint command to delete the savepoint. After deleting the savepoint, you can no longer execute the rollback to savepoint command.
Our example below is a part of simulating a rollback transaction, by defining a savepoint to specify the location of the transaction that needs to be rolled back.

The above are the technical steps for using the MySQL transaction processing mechanism. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!