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 will run in autocommit mode, which means that no small command will be executed as a single command.
if you want MySQL to support transactions, you only need to modify 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 : users can use the SavePoint name command to set some savepoint in the object, and later when the user ends things with rollback to savepoint name, The data before the name is saved, and then the data is not saved.
MySQL using the transaction's keyword
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 // Modifying the Data engine
Example:
Begin
Update person Set name=' efgh ' where id =ten
Select * from person
Rollback
Select * from person
Example:
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
The save point must be used for the above part submission
Save Points Note:
1. can only be canceled 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
Transaction SavePoint Use Example
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;