A transaction is a series of steps for a logical unit of work.
Transactions are used to ensure the security of data operations
characteristics of a transaction :
atomicity ( Atomic Nature )
Consistency ( stability , consistency )
Isolation ( isolation )
Durability ( Reliability )
"Transactions are valid only for statements that have an impact on data data"
Show Engines // View data engines supported by MySQL Lock
MyISAM do not support things. InnoDB Support Things
by default, MySQL executes in its own active commit mode, which means that no small command will be executed as a single command-only thing.
if you want MySQL to support transactions, you just need to change the data engine (ALTER TABLE person type=INNODB)
Use the start transaction or the BEGIN command to start a thing , use commit, or rollback to end things.
the end of things: things except commit , rollback will end outside , The use of DDL or DCL statements will also end.
save point: Through the savepoint mechanism : the user can set some savepoint in the object with the savepoint name command , and later the user ends the thing with rollback to SavePoint name . The data before the name is saved, and then the data is not saved.
MySQL keyword of using transactions
1.begin // open a transaction
2.commit // submit to Database
3.rollback // cancel Operation
4.savepoint // Save, partial cancel, partial submit
ALTER TABLE person type = INNODB // changing the Data engine
Demo Sample:
Begin
Update person Set name=' efgh ' where id =ten
Select * from person
Rollback
Select * from person
Demo Sample:
ALTER TABLE person type = INNODB
Begin
Update person Set name=' efgh ' where id =ten
Select * from person
Commit
Select * from person
Begin
Delete from the person where ID = +
Update person Set name=' efgh ' where id =ten
Commit / rollback
Submitted for the above section. Must be used to save point
Save Points Note:
1. can only Cancel to a savepoint rollback to savepoint p1
2. cannot submit a save commit to SavePoint p2// wrong wording
3. Final Commit to not commit to the data without canceling the save point
Example of using a transaction save point
1. begin;
2. Update score set score= max where Scoreid=1 ;
3. savepoint s1;
4. Update score set score= where Scoreid=2;
5. Select * from score;
6. rollback to savepoint s1;
7. Select * from score;
8. commit.
MySQL Transaction operation and Demo sample