MySQL transaction-detailed explanation of using retention points and changing the default commit behavior

Using retention points

Simple ROLLBACK and COMMIT statements can write or undo the entire transaction. However, this is only possible for simple transactions; more complex transactions may require partial commit or rollback.

For example, the process of adding an order described earlier is a transaction. If an error occurs, just go back to before adding the orders row; there is no need to fall back to the customers table (if it exists).

In order to support rolling back part of the transaction, placeholders must be placed at appropriate locations in the transaction block. This way, if you need to roll back, you can fall back to a placeholder.

These placeholders are called retention points. To create a placeholder, use the SAVEPOINT statement as follows:

Enter:

savepoint deletel;

Each save point is given a unique name that identifies it so that when it rolls back, MySQL knows to roll back Where to. In order to fall back to the retention points given in this example, you can proceed as follows:

Enter:

rollback to deletel;

The more retention points, the better. You can set as many retention points as you want in the MySQL code. The more the merrier. why? Because the more retention points you have, the more flexibility you have to roll back as you wish.

Release the reservation point The reservation point is automatically released after the transaction is completed (execute a ROLLBACK or COMMIT). Since MySQL 5, it is also possible to explicitly release a retain point with RELEASESAVEPOINT.

Change the default commit behavior

As stated, the default MySQL behavior is to automatically commit all Change. In other words, any time you execute a MySQL statement, the statement is actually executed against the table, and the changes take effect immediately. To instruct MySQL not to automatically commit changes, you need to use the following statement:

Input:

set autocommit = 0;

Analysis: The autocommit flag determines whether to automatically commit changes, regardless of whether there is a COMMIT statement. Setting autocommit to 0 (false) instructs MySQL not to automatically commit changes (until autocommit is set to true).

Flag is connection-specific The autocommit flag is for each connection rather than the server.

【Related recommendations】

1. mysql free video tutorial

2. MySQL transaction-ROLLBACK, detailed explanation of COMMIT usage

3. MySQL transaction processing example explanation

4. MySQL UPDATE trigger (update) and trigger in-depth analysis

5. MySQL delete trigger (delete) usage details

The above is the detailed content of MySQL transaction-detailed explanation of using retention points and changing the default commit behavior. For more information, please follow other related articles on the PHP Chinese website!