MySQL savepoint transaction
One of them immediately reports an error when you insert a large number of statements into the database, and the efficiency of rolling back large volumes of data is extremely poor. How to fix that?
One: Here we suggest the idea of a “save point” transaction.
What is a Save Point, then? In Java, it is approximately equivalent to a “breakpoint”. Configure a breakpoint.
When you insert a piece of data into the database, after this sentence, set the savepoint. It saves a lot of time, increases work performance, and does not need to use too much database resources when you need to rollback, you only need to roll back to this when the savepoint point is adequate.
Two: code demonstration
My database table has three attributes, id name pid, and insert it with sql statement
Start the transaction first
START TRANSACTION;
INSERT into city VALUES(NULL,”pankaj”,”7");
After the insertion is successful, we set a save point here aSAVEPOINT a;
INSERT into city VALUES(NULL,”pankajtest”,”8");
After the insertion is successful, we set a save point here bSAVEPOINT b;
INSERT into city VALUES(“pankaj”,”9");
There is an error in the sql statement hereINSERT into city VALUES(“pankajdoc”,”9")
> 1136 — Column count doesn’t match value count at row 1
5. Here we use the save point to roll backrollback to SAVEPOINT a;
Roll back to a successfulrollback to SAVEPOINT a
> OK
In the middle of inserting a large amount of data, if an error occurs and needs to be rolled back, you can use the save point method to effectively solve this kind of problem. I hope it will help you!
I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.
