MySql transaction operations and example transactions are a series of steps of a logical unit of work. Transactions are used to ensure the security of data operations.
Transaction features:
Atomicity(Atomicity)
Consistency(Stability,Consistency)
Isolation(Isolation)
Durability(Reliability)
[The transaction is only valid for statements that affect data]
Show engines // view data engines supported by mysql locks
MyISAM does not support transactions. InnoDB supports transactions.
By default, MySQL runs in the automatic submission mode, which means that no small command is executed as a single command.
To enable mysql to support transactions, you only need to modify the data engine (alter table person type=INNODB)
Use the start transaction or begin command to start a transaction,Use commit,Or rollback to end the transaction.
The end of a transaction: except for commit,Rollback ends,The use of DDL or DCL statements also ends.
Save Point: save point mechanism:You can use the savepoint name command in a transaction to set some storage points.,When a user uses rollback to savepoint name to end a transaction, the data before the name is saved, and the subsequent data is not saved.
Mysql uses transaction keywords
1. begin // open a transaction
2. commit // submit to database
3. rollback // cancel the operation
4. savepoint // Save, partially cancel, partially submit
Alter table person type=INNODB // modify the data engine
Example:
Begin
Update person set name='Efgh' where id=10
Select*From person
Rollback
Select*From person
Example:
Alter table person type=INNODB
Begin
Update person set name='Efgh' where id=10
Select*From person
Commit
Select*From person
Begin
Delete from person where id=21
Update person set name='Efgh' where id=10
Commit/Rollback
The save point must be used for the preceding submission.
Note:
1. you can only cancel rollback to savepoint p1 from a storage point.
2. a commit to savepoint p2 file cannot be submitted. // incorrect syntax
3. finally, commit does not submit uncanceled storage points to the data.
Example of transaction retention point
1. begin;
2. update score set score=40 where scoreid=1;
3. savepoint s1;
4. update score set score=50 where scoreid=2;
5. select*From score;
6. rollback to savepoint s1;
7. select*From score;
8. commit;