MySQL - transaction

Mysql—— Business

One 、 Overview of database transactions
Transaction is one of the important characteristics that distinguish database from file system , When we have transactions, we will keep the database Uniformity , At the same time, we can also use the transaction mechanism Back to a point in time , This ensures that the changes submitted to the database will not be lost due to system crash .

1、 Storage engine support
show engines Command to view the current MYSQL What are the supported storage engines , And whether these storage engines support transactions .

Can see in Mysql in , Only innoDB It's transactional .

2、 Basic concepts
Business ： A set of logical operating units , It is the transition of data from one state to another .

Principles of transaction processing ： Ensure that everything is done as A unit of work To execute , There was an immediate failure , Can't change this way of execution . When multiple operations are performed in a transaction , Or all the affairs will be Submit , So these changes are permanent It's preserved ; Or the database management system will give up All that has been done modify , The entire transaction is rolled back to its original state .

3、 The transaction ACID characteristic

• Atomicity （atomicity）:
  Atomicity means that a transaction is an indivisible unit of work , Or submit it all , Either all failures roll back . That is, the transfer is successful , Or transfer failed , There is no middle state . What if there's no guarantee of atomicity ? There will be data inconsistency ,A Account minus 100 element , and B Account increase 100 Meta operation failed , The system will be lost for no reason 100 element .

• Uniformity （consistency）:
  According to the definition , Consistency refers to before and after transaction execution , Data from a Legitimacy status Change to another Legitimacy status . This state is semantics Not grammatical , Related to specific business .

What is the legal data state ? Satisfy Predetermined constraints The state of is called legal state . More popular , This state is defined by you ( For example, meet the constraints in the real world ). Satisfy this state , The data is consistent , Not satisfied with this state , The data is inconsistent ! If an operation in a transaction fails , The system will automatically undo the currently executing transaction , Return to the state before the transaction operation .

give an example 1:A The account has 200 element , Transfer accounts 300 Yuan goes out , here A The account balance is -100 element . You will naturally find that the data is inconsistent at this time , Why? ? Because you define a state , The balance column must >=0.

give an example 2:A Account 200 element , Transfer accounts 50 Yuan to B Account ,A The money in the account is deducted , however B Account because of all kinds of accidents , The balance has not increased . You also know that the data is inconsistent at this time , Why? ? Because you define a state , requirement A+B The total balance of must remain unchanged .

give an example 3: In the data sheet, we will full name Field set to Uniqueness constraint , At this time, when the transaction is committed or rolled back , If the name in the data sheet is not unique , It destroys the consistency requirements of transactions .

• Isolation, （isolation）:
  Transaction isolation means that the execution of one transaction cannot be interfered by other transactions , That is, the operations and data used within a transaction are isolated from other concurrent transactions , Transactions that execute concurrently cannot interfere with each other .

What happens if isolation cannot be guaranteed ？ hypothesis A The account has 200 element ,B Account 0 element .A Account to B Account transfer twice , The amount of each time is 50 element , Execute in two transactions . If isolation cannot be guaranteed , The following situations will occur ：

• persistence （durability）:
  Persistence means that once a transaction is committed , The change it makes to the data in the database is Permanence Of , Other subsequent operations and database failures should not have any impact on it .

Persistence is through Transaction log To ensure that the . Including the log Redo log and Rollback log . When we modify data through transactions , First, the change information of the database will be recorded in the redo log , Then modify the corresponding row in the database . The advantage of this is , Even if the database system crashes , After the database is restarted, you can also find the redo log that has not been updated to the database system , Re execution , This makes the transaction persistent .

4、 The state of the transaction
We know that transaction is an abstract concept , It actually corresponds to one or more database operations ,MySQL The transaction is roughly divided into several states according to the different stages of these operations ：

• Activities of the （Active）:
  When the database operation corresponding to the transaction is in progress , Let's say the office Activities of the state .
• Partially submitted (partially committed):
  When the last operation in the transaction is completed , But because the operations are all performed in memory , The impact is not No refresh to disk when , Let's say the office Partial submission The state of .
• The failure of the (failed):
  When the affairs office Activities of the perhaps Partially submitted In the state of , You may have encountered some errors （ Error in the database itself 、 Operating system error or direct power failure, etc ） And cannot continue , Or artificially stop the execution of the current transaction , Let's say the office The failure of the state .
• Suspended （aborted）:
  If the transaction is partially executed and becomes The failure of the state , Then you need to restore the operations in the modified transaction to the state before the transaction is executed . let me put it another way , To undo the impact of failed transactions on the current database . We call this revocation process Roll back . When Roll back When the operation is completed , That is, the database is restored to the state before the transaction is executed , Let's say the office Suspended state .
• The submitted （committed）:
  When one is in Partially submitted The transaction in status will all the modified data Sync to disk After that , We can say that the office The submitted state .

A basic state transition diagram is shown below ：

Two 、
1、 The completion process of the transaction ：

• Open transaction
• A series of DML operation
• The state of the end of the transaction ： The status of the submission （commit）、 Suspended status （rollback）

2、 According to the transaction
Use keywords start transaction or begin

start transaction It can be followed by ：read only/ read write（ Default ）/ with consistent snapshot

Save it （savepoint）: You can set a savepoint somewhere in the transaction , When an error is found, you can rollback to the savepoint .

3、 Implicit transaction
1、 keyword ：autocommit （ The default is on）

2、 Turn off auto submit

The way ①：set autocommit = false;

The way ②：