MySQL transaction operation examples and precautions

This article mainly introducesMySQL transactionThe following describes some operation examples and precautions.

Transaction Features:

• Atomicity (Atomicity)
• Consistency (stability, Consistency)
• Isolation (Isolation)
• Durability)

Note:Transactions are only valid for statements that affect data.

Show engines // view the Data Engine 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: Except for commit, rollback ends and DDL or DCL statements ends.

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

Mysql uses transaction keywords

• Begin // 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 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. // The incorrect syntax is as follows.

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； 
 
 

Here is an introduction to MySQL transaction operations. If you want to know