premise : The database engine must be InnoDB type .
stay mysql in ,MyISAM and InnoDB Are the two most commonly used engines ,( among MyISAM Is the default engine ), among MyISAM Support full text search , Transactions are not supported ; and
InnoDB Full text search is not supported , But it supports transactions . therefore , Here we define the table engine to be operated as InnoDB.

Transaction processing can be used to maintain the integrity of the database , It's guaranteed to be in batches MySQL The operation is either fully executed , Or not at all .

Now? , Let's start with these terms :
1, affair (transaction): Refers to a group SQL sentence
2, Back off (rollback): Revoking a designated SQL Statement procedure
3, Submit (commit): That will not be stored SQL The result of the statement is written to the database
4, Reservation point (savepoint): Refers to a temporary placeholder set in a transaction , You can use it to post fallbacks ( It's different from rolling back the entire transaction ).

Now imagine a little problem : There are two tables ,orders and orderdetail orders Inside the watch user ID and order number ,orderdetail
It stores the commodity information corresponding to the order number . If there is an accident when saving two tables ,orders The watch was saved successfully , but orderdetail
Save failure , An empty order was generated , No meaning .orderdetail It's a success , but orders Save failed , There are no orders , Where is the order information from ?

How to solve this problem ? We need to use transactions , Make sure both tables succeed at the same time , Or fail at the same time .

First build table , The table engine is InnoDB:
//orders surface CREATE TABLE orders( id INT(11) NOT NULL AUTO_INCREMENT, user_id INT(
//orderdetail surface CREATE TABLE orderdetail( id INT(11) NOT NULL AUTO_INCREMENT,
Put some data in it first ...
INSERT INTO orders(user_id,order_no) VALUES(10,'123456'),(11,'456789'),(12,
'123789'); INSERT INTO orderdetail(order_no,detail) VALUES('123456',' pancake rolled with crisp fritter '),(
'456789',' shredded pancake '),('123789',' Chinese hamburger ');
One , The beginning of the transaction :
We use the following sentence to mark the beginning of a transaction :
Two , use ROLLBACK:
MySQL Of ROLLBACK The command is used to back off ( revoke )MySQL sentence , Here's an example :
SELECT * FROM orders; START TRANSACTION;// Open transaction DELETE FROM orders;
// delete orders Table all the data SELECT * FROM orders;// The return here is empty , Because all the data has been cleared before . ROLLBACK;
// Back off MySQL To the previous security state SELECT * FROM orders;// Return three pieces of data
From the example above , We can use it ROLLBACK To cancel our Mysql sentence .
obviously ,ROLLBACK Can only be used within one transaction ( In the implementation of an article START TRANSACTION After the order ).
be careful : Transactions are used to manage INSERT,UPDATE,DELETE sentence , You can't go back SELECT sentence .

Three , use commit
General MySQL Statements are executed and written directly on the data table , This is called implicit submission , Submit ( Write and save ) The operation is automatic .
however , When you turn on transaction processing , Commit does not commit implicitly , It's when we do it commit After the command, it will be submitted to the database for operation .
For example, the following example :
orderdetailWHERE order_no='123456'; // Delete the order number of two tables as 123456 Orders for COMMIT;// Submit delete operation

ad locum , Because the two tables are either deleted at the same time , Or not delete at the same time , Therefore, transactions are used to ensure that the order is not partially deleted . final COMMIT Statement to commit changes only if there are no errors , If the first one DELETE It works , But the second failed , be DELETE The operation will not be committed , actually , It was automatically revoked .

Implied transaction closure :
When COMMIT or ROLLBACK After statement execution , The transaction will close automatically , This means that the MYSQL Operation implicit commit .

Four , Use reserve points
ordinary ROLLBACK and COMMIT Statement can write or undo the entire transaction ,
That is to say, the influence is from the START TRANSACTION reach ROLLBACK(COMMIT) This code , But what do we do when we use partial commit or partial fallback ?

I'll build one first customer surface :
INT(3) NULL, PRIMARY KEY (id) )ENGINE=INNODB; // The engine is InnoDB, Because transactions are needed

Now imagine this question , When a new user buys something from me , I want to deposit separately customer surface ,orders surface ,orderdetail surface , If a fault prevents the stored procedure , So what happens to the database ?
1,customer Failed to save , Then we can't let it go on orders and orderdetail Table storage .

2,customer succeed , but orders The table failed , that customer It can be allowed to succeed , After all, it's perfectly legal for a user to have no order , however orders failed ,orderdetail You can't save it , because orders and orderdetail It's synchronous .

How ? Here we use the reservation point to implement the partial fallback transaction .
Create retention points :
SAVEPOINT ins;// Create a ins The reservation point of , Keep the roll call unique
Back to hold point :
Let's solve the problem :
// Open transaction START TRANSACTION; INSERT INTO customer(name,age) VALUES(LSGO laboratory ,20);
// I made a mistake here SAVEPOINT err1;// Create retention points INSERT INTO orders(user_id,order_no) VALUES(10,
'456123'); INSERT INTO orderdetail(order_no,detail) VALUES('456123',' Snail powder ');
ROLLBACK TO SAVEPOINT err1; // If not commit, Then the data is not written to the database , It's just written to the cache , Querying the database directly does not have inserted values
Execute the above SQL sentence , You'll find out , None of the watches succeeded , Because it's the first sentence INSERT failed , The rest of the operations were taken back .
Look at the next one :
// Open transaction START TRANSACTION; INSERT INTO customer(name,age) VALUES('LSGO laboratory ',20);
SAVEPOINT err2;// Create retention points INSERT INTO orders(user_id,order_no) VALUES(10,'456123');
INSERT INTO orderdetail(order_no,detail) VALUES('456123', Snail powder );// I made a mistake here
ROLLBACK TO SAVEPOINT err2; // If not commit, Then the data is not written to the database , It's just written to the cache , Querying the database directly does not have inserted values
The result is ,customer The watch was saved successfully , however orders and orderdetail The table failed . This also solves the above problem .

Five , Change the default commit behavior :
default MYSQL The behavior of submitting is automatic , Which means yours SQL As soon as a statement is executed , It will take effect immediately , In order to achieve MYSQL Do not auto submit , Can be set sutocommit = 0:
SET autocommit = 0;
autocommit Flag determines whether to commit changes automatically , Whether or not COMMIT sentence .

Because I'm right MYSQL Process control statements are not very familiar , So it's impossible to write more advanced examples here , For example, when will it be ROLLBACK , when COMMIT. In my other blog, I used
PHP To achieve mysql Transaction processing for .

This blog reference from 《mysql Know and know 》

©2019-2020 Toolsou All rights reserved,
Send love - A little romance for programmers VHDL—— Design of frequency divider Python Implementation of Hanoi Tower code It's over , Starting salary 30khtml+css+js Make a simple website home page QQ Login interface implementation Hill sorting of sorting algorithm ——c++ realization 【 Wechat applet learning 】 Netease music cloud code page implementation details Resume the 13th session python Blue Bridge Cup 2022 Solution to the 13th Blue Bridge Cup ( whole )