MySQL transaction-ROLLBACK, detailed explanation of COMMIT usage

USE ROLLBACK

Now that we already know what transaction processing is, let’s discuss the issues involved in transaction management.

The key to managing transaction processing is to break down groups of SQL statements into logical chunks and clearly specify when data should and should not be rolled back.

MySQL uses the following statement to identify the start of a transaction:

Input:

start transaction

MySQL's ROLLBACK command is used to roll back (undo) the MySQL statement, please see the following statement :

Input:

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

Analysis: This example starts by displaying the contents of the ordertotals table. First execute a SELECT to show that the table is not empty. Then start a transaction and delete all rows in ordertotals with a DELETE statement. Another SELECT statement verifies that ordertotals is indeed empty. At this time, a ROLLBACK statement is used to roll back all statements after START TRANSACTION, and the last SELECT statement shows that the table is not empty.

Obviously, ROLLBACK can only be used within a transaction (after executing a START TRANSACTION command).

Which statements can be rolled back? Transactions are used to manage INSERT, UPDATE, and DELETE statements. You cannot roll back a SELECT statement. (There's little point in doing this either.) You can't roll back a CREATE or DROP operation. These two statements can be used within a transaction block, but they will not be undone if you perform a rollback.

Using COMMIT

General MySQL statements are executed and written directly against the database table. This is the so-called implicit commit, that is, the commit (write or save) operation is performed automatically. However, within a transaction block, commits do not occur implicitly. To make an explicit commit, use the COMMIT statement as follows:

Input:

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

Analysis: In this example, order 20010 is completely removed from the system. Because it involves updating two database tables, orders and orderItems, a transaction block is used to ensure that orders are not partially deleted. The final COMMIT statement only writes out the changes without error. If the first DELETE works but the second fails, the DELETE is not committed (in fact, it is automatically revoked).

Implicit transaction closure When the COMMIT or ROLLBACK statement is executed, the transaction is automatically closed (future changes will be implicitly committed).

【Related recommendations】

1. mysql free video tutorial

2. MySQL transaction processing example explanation

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

4. MySQL delete trigger (delete) usage detailed explanation

5. Detailed explanation of insert trigger (insert) in MySQL

The above is the detailed content of MySQL transaction-ROLLBACK, detailed explanation of COMMIT usage. For more information, please follow other related articles on the PHP Chinese website!