©2020-2024 ioDraw All rights reserved
When not controlling creation , to update , read , When deleting , What will happen ?
The data needs to be updated in time after purchase , Avoid errors .
What is a transaction ?
A transaction is a set of database operation statements （DML）, One group or all succeed , Or all fail .
A transaction is a whole composed of multiple operations to realize a thing .
One MySQL database , It's definitely not a transaction running , So when there's a conflict , Those half implemented sql Transactions consisting of statements , What should I do ?
remarks ：
sql sentence ：
* DDL（ Define database objects , Table and column ）,
* DML（ Used to manipulate record data in database tables ）,
* DQL（ Query data ）,
* DCL（ Define access rights and security levels ）
therefore , Transactions cannot be just sql Combination of statements , The following attributes are also required .
* Atomicity
： A transaction （transaction） All operations in , Or finish it all , Or not all , It won't end in the middle . An error occurred during the execution of the transaction , Will be rolled back （Rollback） To the state before the start of the transaction , It's like this business has never been performed .
* uniformity
： Before the transaction starts and after the transaction ends , The integrity of the database was not compromised . This means that the written data must fully comply with all preset rules , This includes the accuracy of the data , Tandem and subsequent databases can spontaneously complete the predetermined work .
* Isolation
： The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time , Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently . Transaction isolation is divided into different levels , Include read uncommitted （
Read uncommitted ）, Read commit （ read committed ）, Repeatable reading （ repeatable read ） And serialization （
Serializable ）
* persistence ： After transaction , The modification of data is permanent , Even if the system fails, it will not be lost .
Why do transactions occur
Transactions are designed when an application accesses a database , Can simplify our programming model .
Version support for transactions
MySQL Only used in innodb Only the database or table of the database engine supports transactions ,myisam I won't support it .
Transaction submission method
There are two common transaction submission methods ：
* Auto submit
* Manual submission
View transaction submission method
use SET To change MySQL Automatic submission mode , Remember to change it back
Common operation modes of transaction
Create test table
Proving the start and rollback of a transaction
Transaction isolation level
In database , In order to ensure that there is no interference in the execution of transactions , There is isolation .
In database , Allow transactions to be disturbed to varying degrees , There is an isolation level .
Isolation level
* Read uncommitted 【Read Uncommitted】： At this isolation level , All transactions can see the execution results of other transactions that are not committed .
* Read commit 【Read Committed】 ： This isolation level is the default isolation level for most databases （ no MySQL
default ）. It satisfies the simple definition of isolation : A transaction can only see the changes made by other committed transactions .
* Repeatable reading 【Repeatable Read】： This is MySQL
Default isolation level , It ensures the same transaction , In execution , When reading operation data multiple times , You will see the same data row . But there will be unreal reading problems .
* Serialization 【Serializable】: This is the highest isolation level for transactions , It does this by forcing transaction sequencing , Make it impossible to conflict with each other , Thus, the problem of unreal reading is solved .
Isolation is basically achieved through locks .
View global isolation level
View the global isolation level of the current session
Set current session or Global isolation level syntax
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ
COMMITTED |REPEATABLE READ | SERIALIZABLE}
Set the current session isolation level to serialization
Let's look at the change of session isolation level
Read uncommitted
Open transaction
Open another terminal B, adopt B visit
A transaction is in progress , Read an update from another transaction in progress ( Or other operations ) But not commit Data , This phenomenon is called dirty reading (dirty read).
Read commit
Repeatable reading
Serialization
Isolation level comparison
Technology