A brief discussion on MySQL transaction management (basics)

The content of this article is a brief discussion of MySQL transaction management (basics). 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 set of SQL statements;

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

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

4. The savepoint (savepoint) refers to the settings during transaction processing A temporary place-holder to which you can issue a rollback (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

Assume that the second deletion fails, roll back, and undo the statements in the transaction processing block

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 as follows

Create a retention point

SAVEPOINT delete1

Fallback 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 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 automatically submit changes

set autocommit = 0;

The above is the entire content of this article, I hope it will be helpful to everyone's learning. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of A brief discussion on MySQL transaction management (basics). For more information, please follow other related articles on the PHP Chinese website!