Introduction to MySQL transaction management (with examples)

This article brings you an introduction to MySQL transaction management (with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Transaction processing is used to maintain the integrity of the database and ensure that mysql operations either succeed or fail (myisam does not support transactions)

1. Keywords

1. Transaction refers to a group of SQL statements;

2. Rollback refers to the process of undoing the specified SQL statement;

3. Commit refers to writing the unstored SQL statement results into the database table;

4. The savepoint refers to the temporary placeholder (place-holder) set in transaction processing ), you can issue a rollback to it (as opposed to rolling back the entire transaction).

2. Use rollback

select * from orderitems;
START TRANSACTION;
DELETE FROM orderitems;
select * from orderitems;
ROLLBACK;
select * from orderitems;

3. Use commit

START TRANSACTION;
DELETE FROM orderitems where order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT

Assuming that the second deletion fails, rollback and undo the transaction in the transaction block Statement

4. Use retention points

Complex transaction processing may require partial commit or rollback.
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 SAVEPOINT

Create a retention point

SAVEPOINT delete1

Fall back to a retention point

ROLLBACK TO delete1

tips

The more reservation points, the better, it is convenient and flexible to use, but there is no need to come and just come! Everything is done in moderation
Release retention points

1. The retention points are automatically released after the transaction is completed (execute a ROLLBACK or COMMIT)

2. release savepoint delete1Clearly release the retention point

5. Change the default to commit behavior

mysql automatically commits all changes.
Do not commit changes automatically

set autocommit = 0;

The above is the detailed content of Introduction to MySQL transaction management (with examples). For more information, please follow other related articles on the PHP Chinese website!