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(
11) NULL, order_no VARCHAR(32) NULL, PRIMARY KEY (id) )ENGINE=INNODB;
//orderdetail surface CREATE TABLE orderdetail( id INT(11) NOT NULL AUTO_INCREMENT,
order_noVARCHAR(32) NULL, detail TEXT NULL, PRIMARY KEY (id) )ENGINE=INNODB;
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 ：
START TRANSCATION; DELETE FROM orders WHERE order_no='123456'; DELETE FROM
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 ：
CREATE TABLE customer( id INT(11) NOT NULL AUTO_INCREMENT, name TEXT NULL, age
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 ：
ROLLBACK TO SAVEPOINT ins;
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 》