MySql transaction usage and example _ MySQL

In MySql, transactions are a series of steps in a logical unit of work. Transactions are used to ensure the security of data operations.

Transaction features:

1. Atomicity (Atomicity)
2. Consistency (stability and Consistency)
3. Isolation (Isolation)
4. Durability (reliability)

Note: transactions are 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, and use commit or rollback to end the transaction.

The end of a transaction:In addition to commit, rollback ends, and DDL or DCL statements end.

Save point:Save point mechanism: you can use the savepoint name command in a transaction to set some save points. later, when you use rollback to savepoint name to end a transaction, data before name is saved, subsequent data is not saved.

Mysql uses the transaction keyword begin // to open a transaction commit // submit it to the database rollback // cancel the operation savepoint // save, partially cancel, partially submit alter table person type = INNODB // modify data engine

Example:

beginupdate person set name='efgh' where id =10select * from personrollbackselect * from person

Example:

alter table person type=INNODBbeginupdate person set name='efgh' where id =10select * from personcommitselect * from personbegindelete from person where id=21update person set name='efgh' where id =10commit/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:

begin;update score set score=40 where scoreid=1;savepoint s1;update score set score=50 where scoreid=2;select * from score;rollback to savepoint s1;select * from score;commit;