MySQL Transaction operation and Demo sample

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