What are the methods of MySQL transaction management
The editor will share with you the methods of MySQL transaction management. I hope you will gain a lot after reading this article. Let's discuss it together.
Transaction processing is used to maintain the integrity of databases, etc., to ensure that mysql operations either succeed or fail (myisam does not support transactions)
1. Key words
A transaction refers to a set of SQL statements
Rollback refers to the process of undoing a specified SQL statement
Commit means writing the result of an unstored SQL statement to a database table
A savepoint refers to a temporary placeholder (place-holder) set in a transaction to which you can issue a fallback (unlike rollback of the entire transaction).
2. Use rollbackselect * from orderitems;START TRANSACTION;DELETE FROM orderitems;select * from orderitems;ROLLBACK;select * from orderitems;3, and use commitSTART TRANSACTION;DELETE FROM orderitems where order_num = 20010 _ delete FROM orders WHERE order_num = 20010 _ commit
Suppose the second item fails to delete, rollback, and undo the statements in the transaction block
4. Use the reservation point
Complex transactions may require partial commit or fallback.
In order to support fallback of some transactions, placeholders must be placed in the appropriate place in the transaction block. This way, if you need to fallback, you can fallback to a placeholder.
These placeholders are called retention points. To create placeholders, use SAVEPOINT as follows
Create a retention point
SAVEPOINT delete1
Fall back to the reservation point
ROLLBACK TO delete1tips
Keep as many points as possible, convenient and flexible to use, there is no need for but even if you come! Enough is enough.
Release retention point
The reservation point is automatically released after the transaction completes (executing a ROLLBACK or COMMIT)
Release savepoint delete1 explicitly releases the retention point
5. Change the default to submit behavior
Mysql automatically commits all changes.
Do not automatically commit changes
Set autocommit = 0; after reading this article, I believe you have some understanding of the methods of MySQL transaction management, want to know more about it, welcome to follow the industry information channel, thank you for reading!
