What is business
* In short, it is ： An event must have a beginning and a ending , Once it starts , There are only two results , Or fail , Or success , And there can't be success, part failure, part failure .
* Professionally speaking, it is ： A transaction must have ACID attribute , The so-called ACID Namely ：
1. Atomicity-- Atomicity :
Transaction must be an atomic unit of work ; For its data modification , Or it's all done , Or not at all .
2. Consistency-- uniformity
After transaction and before execution , All the data are consistent （ There are many understandings of consistency , It looks like this ）
3. Isolation-- Isolation
Concurrent transactions do not affect each other ,
4. Durability-- persistence
A transaction succeeded , So his change should be permanent .
Why business
You know the importance of this by thinking about it with your toes , Take a simple example ：
A user submitted an order , So this data contains two pieces of information , User information And purchased Commodity information , I need to save them separately User table and
Commodity list , If you don't use transactions , It may appear , Product information inserted successfully , And the user information is not , At this point, there will be no owner goods , The user paid for it , But they don't get the goods , This is embarrassing , And if you use transactions , Can guarantee , User information
and Commodity information Must be inserted successfully , This transaction is a success , That would not have happened
Transaction engine
Sometimes things don't have to be necessary , In order to improve the query efficiency , Some mysql The engine does not support transactions , such as MyISAM, Now, of course （ It seems to be 5.6 in the future ） The default engine is
innoDB It supports transactions , Generally for promotion mysql speed , Can also do read-write separation , Because transactions are generally written .
Use of transactions
We start with the following terms ：
*
affair （transaction） Execute a group SQL sentence ;
start transaction You can start a transaction
*
Back off （rollback） Revocation of designation SQL Statement procedure ;
rollback You can roll back a transaction ：rollback It can only be used in a transaction , No business , We can't talk about going back
*
Submit （commit） Will not be stored SQL Statement results are written to the database table ;
commit use commit To end a transaction ： General usage services , We need to submit it manually ,mysql The default is auto submit , So we need to set it up set
autocommit = 0 To change the submission mode . Among them, it is worth noting that rollback The commit transaction is also triggered
*
Reservation point （savepoint） Refers to a temporary placeholder set in a transaction （placeholder）, Then you can go back to that point .
savepoint s1 Generate a retention point , Then you can go through the rollback to s1 Back and forth s1 This reservation
example
So much has been said , Do not come to an instance to operate , It's not comfortable .... Now you can open your mysql library , Follow the steps below , Deepen understanding ：
* truncate info：
Clear the table first , Remember to use the test table , Don't rely on me when the data is gone
* select * from info：
Check it out , The watch here should be empty
* start transaction：
Start a transaction , Our official test
* insert into info values(1,"s1 before",20181019)：
Insert a piece of data “s1 before”
* savepoint s1：
Create a retention point
* insert into info values(1,"s1 after",20181019)：
Insert another piece of data “s1 after”
* select * from info：
Look up the data , At this point, we can see the two pieces of data we inserted above . But you have to know , These two data are not officially stored in the database , They only have this for you session inside , Because our business hasn't been committed yet ... Don't believe it ? You restart a client , Take a look at this chart , It's still empty .
* rollback to s1：
Roll back to s1 Reservation point .
* select * from info：
At this time, you should only be able to see “s1 after” This is the data , And the transaction is not committed , Verification , Or restart a client to view it
* rollback：
Rollback transaction , in other words , I don't want any of the operations we did before , Roll back to the state at which the transaction started , And close the transaction .
* insert into info values(1,"no transaction",20181019)：
Insert another piece of data
* select * from info：
Another client can query directly , Immediately see the data inserted above “no transaction”, It also verifies that the transaction does end
* set autocommit=0：
Above we insert data , Another client immediately found it , The description is automatically submitted to our insert , Now we set no auto submit .
* insert into info values(1,"autocommit=0",20181019)：
Insert a piece of data “autocommit=0”
* select * from info;
Query on another client , You'll find out , The top one "autocommit=0" The data is not inserted
* commit
Submit , You can check again at this time , In order to find the data you submitted "autocommit=0"
What needs to be noted here is that , Don't confuse non auto commit with transactions , Non auto submit , It is generally for batch submission to improve efficiency , But it's not transactional , The transaction ensures the transaction based on batch commit , So there is still a certain difference
* an account of happenings after the event being told ：
Finish the final practice , It's better to make a comparison Transactional ACID Pondering over the four principles , Deepen your understanding , More research on the subject , Then you need to work on it slowly ... hey
!!! Finally, I hope this article is helpful to you !!!
Technology