Tips ： When the article is finished , Directories can be generated automatically , How to generate it, please refer to the help document on the right

Catalog

Preface

One 、SQL Business

Two 、 features

3、 ... and 、 Transaction control statement

Four 、 Transaction processing method

5、 ... and 、 example

Preface

This is about SQL Articles on business

Tips ： The following is the main body of this article , The following cases can be used for reference

One 、SQL Business

MySQL Transactions are mainly used to handle large amount of operations , Data with high complexity . for instance , In the people management system , You delete a person , You need to delete the basic information of the personnel , Also delete information related to the person , Such as mailbox , Articles, etc , such , These database operation statements constitute a transaction ！

• stay MySQL Only use Innodb The database or table of the database engine supports transactions .
• Transactions can be used to maintain database integrity , Make sure that there are lots of SQL Statement or all execution , Or none at all .
• Affairs are used to manage insert , update , delete sentence .

Two 、 features

Generally speaking , Transactions must be satisfied 4 Conditions （ACID）： Atomicity（ Atomicity or indivisibility ）、Consistency（ Uniformity ）、Isolation（ Isolation or independence ）、Durability（ persistence ）

• 1、 Atomicity ：** A set of transactions , Or success ; Or withdraw , That is, if an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction .
• 2、 Uniformity  ： A transaction, whether before or after the beginning , The integrity of the database has not been destroyed . Therefore, the written data must fully comply with all preset rules （ Data accuracy 、 Serialization and subsequent databases can spontaneously complete the scheduled work ）.
• 3、 Isolation, ：** The database allows multiple transactions to read, write and modify its data simultaneously , Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently . Transaction isolation can be divided into ：Read uncommitted（ Read uncommitted ）、Read committed（ Read the submission ）、Repeatable read（ Repeatable ）、Serializable（ Serialization ）.
• 4、 persistence ：** The changes made to the data after the transaction is completed are permanent , Cannot lose .

3、 ... and 、 Transaction control statement

1, Start a transaction explicitly

start transaction

 perhaps 

begin

2, Make a save point , There can be multiple savepoints in a transaction

savepoint  Save point name 

3, Commit transaction , And make all changes made in the database permanent

commit
 perhaps 
commit work

4, Rollback ends the user's transaction , And undo all pending changes

rollback
 perhaps  
rollback work

5. Delete a transaction savepoint , If no save point is specified , Executing this statement will throw an error

release savepoint  Save point name 

6. Roll the transaction back to the marked point

rollback to  Marker points 

7. Set the isolation level of the transaction .InnoDB The isolation levels that the storage engine provides for transactions are READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ and SERIALIZABLE

set transaction

Four 、 Transaction processing method

1. use begin , rollback , commit To implement transactions .

2. use set To change MySQL The auto submit mode of .

• set autocommit = OFF （ Disable auto submit ）.
• set autocommit = ON （ Turn on auto submit ）.

5、 ... and 、 example

--  Turn off auto submit 
SET autocommit = OFF;
SHOW VARIABLES LIKE 'autocommit';
--  Open transaction 	（ A set of transactions ）
START TRANSACTION; 
UPDATE shop SET money = money + 500 WHERE NAME = 'A';
UPDATE shop SET money = money + 10000 WHERE NAME = 'B';

COMMIT; --  Commit transaction   Once submitted, it is persisted 
ROLLBACK; --  Roll back 

SET autocommit = on; --  Restore defaults