mysql Four characteristics of transaction and four isolation levels of transaction
One , Four attributes of transaction
They are atomic , uniformity , Isolation , persistence .
<>1, Atomicity （Atomicity）
Atomicity means that all operations contained in a transaction are successful , Or all failed and rolled back , Therefore, if the transaction operation is successful, it must be fully applied to the database , If the operation fails, there is no impact on the database .
<>2, uniformity （Consistency）
Consistency means that the transaction must change the database from one consistent state to another , In other words, a transaction must be in a consistent state before and after execution . for instance , Hypothetical users A And users B The sum of the two is 1000, So no matter A and B How to transfer money between , How many transfers , After the transaction ends, the sum of the money of the two users should be 1000, This is transaction consistency .
<>3, Isolation （Isolation）
Isolation is when multiple users access the database concurrently , For example, when operating the same table at the same time , Transactions opened by the database for each user , Cannot be disturbed by the operation of other transactions , Multiple concurrent transactions should be isolated from each other . The isolation database provides multiple isolation levels for transactions , I'll talk about it later .
<>4, persistence （Durability）
Persistence is when a transaction is committed , Changes to the data in the database are permanent , Even in the case of database system failure, the operation of committing transaction will not be lost . For example, we are using JDBC When working with the database , After committing the transaction method , Prompt user to complete transaction operation , When our program is finished until we see the prompt , You can assume that the transaction has been committed correctly , Even if there is a problem with the database at this time , We also have to complete our transaction . Otherwise, we will see that the transaction is finished , However, the database failed to perform a transaction due to a failure . This is not allowed .
<> Two , Isolation level of the transaction
<>1, Why set isolation level
In database operations , In the case of concurrency, the following problems may occur ：
Update missing （Lost update）
If multiple threads operate , Modify the records in the table based on the same query structure , Then, the modified record will cover the previous modified record , The previous changes were lost , This is called Update loss . This is because the system does not perform any locking operations , Therefore, concurrent transactions are not isolated .
The first 1 Class missing update ： affair A On revocation , Transfer the transaction that has been committed B The updated data of .
The first 2 Class missing update ： affair A Overlay transaction B Submitted data , Cause business B The operation was lost .
resolvent ： Lock rows , Only one update transaction can be concurrent .
Dirty reading （Dirty Reads）
Dirty reading （Dirty Read）：A Transaction read B Data that has not yet been committed by a transaction and operates on it , and B Transaction execution rollback , that A The data read is dirty data .
terms of settlement ： If before the first transaction is committed , No other transaction can read its modified value , This problem can be avoided .
Non repeatable （Non-repeatable Reads）
A transaction repeatedly reads the same row of data twice , But different results have been achieved . affair T1 After reading a certain data , affair T2 It has been modified , When business T1 When you read the data again, you get a different value than the previous time .
terms of settlement ： If the data can only be read after the modified transaction is fully committed , This problem can be avoided .
Refers to the execution of the same article twice select
Statement will have different results , The second read adds a row of data , It is not said that the two executions are in the same transaction . Normally , Phantom reading should be exactly what we need . But sometimes it's not , If the cursor is open , When operating on a cursor , You do not want new records added to the dataset hit by the cursor . The isolation level is
Of , Can prevent phantom reading . for example ： The current salary is 1000 Employees of 10 people . So business 1 Read all wages as 1000 Employees of , Got it 10 Records ; At this time, business 2 An employee record was inserted into the employee table , The salary is 1000; So business 1 Read all wages again as 1000 The total number of employees read 11 Records .
terms of settlement ： If the , No other transaction can add new data , This problem can be avoided .
It is to solve the above situation , The database provides several isolation levels .
<>2, Isolation level of the transaction
The isolation levels of database transactions are 4 individual , From high to low Read uncommitted( Unauthorized read , Read uncommitted ),Read
committed（ Authorized read , Read submit ）,Repeatable
read（ Repeatable read ）,Serializable（ serialize ）, These four levels can solve dirty reading one by one , Non repeatable , The problems of phantom reading .
* Read uncommitted( Unauthorized read , Read uncommitted )：
If a transaction has already started to write data , The other transaction is not allowed to write at the same time , However, other transactions are allowed to read this row of data . The isolation level can be passed through “ Exclusive writing lock ” realization . This avoids the loss of updates , But dirty reading may occur . That is to say, affairs B Transaction read A Uncommitted data .
* Read committed（ Authorized read , Read submit ）：
The transaction that reads the data allows other transactions to continue accessing the row data , However, uncommitted write transactions will prevent other transactions from accessing the row . This isolation level avoids dirty reads , But it can't be read repeatedly . affair A Data was read in advance , affair B Then the data was updated , And committed the transaction , And business A When the data is read again , The data has changed .
* Repeatable read（ Repeatable read ）：
Repeatable read is defined as being within a transaction , Read the same data multiple times . Before the end of the transaction , Another transaction also accesses the same data . that , Between two reads in the first transaction , Even if the second transaction modifies the data , The first transaction reads the same data twice . In this way, the data read twice in a transaction is the same , So it's called repeatable reading . Transactions that read data will disable writing transactions （ But transactions are allowed to be read ）, No other transactions are allowed to write . This avoids non repeatable and dirty reads , But sometimes phantom reading may occur .（ Transactions that read data ） This can be done through “ Shared read lock ” and “ Exclusive writing lock ” realization .
* Serializable（ serialize ）：
Provides strict transaction isolation . It requires transaction serialization execution , Transactions can only be executed one after another , But it cannot be executed concurrently . If only through “ Row level lock ” Transaction serialization is not possible , Other mechanisms must be used to ensure that the newly inserted data will not be accessed by the transaction that just performed the query operation . Serialization is the highest level of transaction isolation , And it costs the most , Very low performance , It is rarely used , At this level , Sequential execution of transactions , Not only can you avoid dirty reading , Non repeatable , It also avoids phantom reading .
The higher the isolation level , The more data integrity and consistency can be guaranteed , But the greater the impact on concurrency performance . For most applications , Priority should be given to setting the isolation level of the database system to Read
Committed. It avoids dirty reads , And it has good concurrent performance . Although it can lead to non repeatable reading , These concurrency problems are phantom read and loss update of the second kind , Where such problems may arise , It can be controlled by the application using pessimistic or optimistic locks . The default level for most databases is Read
committed, such as Sql Server , Oracle.MySQL The default isolation level of is Repeatable read.
<> Three , Pessimistic lock and optimistic lock
Although the isolation level of the database can solve most problems , But the flexibility is poor , Therefore, the concepts of pessimistic lock and optimistic lock are put forward .
<>1, Pessimistic lock
Pessimistic lock , It refers to the fact that the data is （ Including other current transactions of the system , And transactions from external systems ） The revision is conservative . therefore , In the whole data processing process , Keep data locked . Implementation of pessimistic lock , Often rely on the lock mechanism provided by the database . Only the lock mechanism provided by the database layer can guarantee the exclusivity of data access , otherwise , Even if the lock mechanism is implemented in the data access layer of the system , There is no guarantee that the external system will not modify the data .
* Examples of use scenarios ： with MySQL InnoDB take as an example
commodity t_items There is a field in the table status,status by 1 Represents that the product has not been placed ,status by 2 Represents that the product has been placed （ At this time, the product cannot be placed again ）, So when we place an order for an item, we have to make sure that it is status by 1. Hypothetical commodity id by 1.
If the lock is not used , Then the operation method is as follows ：
//1. Find out the product information select status from t_items where id=1; //2. Generate orders based on product information , And insert the order form
t_ordersinsert into t_orders (id,goods_id) values (null,1); //3. Modify product status by 2
update t_itemsset status=2;
However, the above scenario is likely to have problems in the case of high concurrent access . For example, in the first step of operation , Products found status by 1. But when we go to step three Update During operation , It is possible that other people will place an order for the goods first t_items Medium status Revised as 2 了 , But we didn't know the data had been modified , This may cause the same product to be ordered 2 second , Make the data inconsistent . So it's not safe .
* Use pessimistic locks to solve problems
In the scene above , Commodity information from query to modification , There is an order processing process in the middle , The principle of pessimism lock is that , When we find out t_items After the information, lock the current data , We won't unlock it until we've modified it . So in the process , because t_items Locked , There will be no third party to modify it . It should be noted that , To use pessimistic locks , We have to close down mysql Auto commit properties for databases , because MySQL Default use autocommit pattern , in other words , When you perform an update operation ,MySQL The results will be submitted immediately . We can use the command settings MySQL For the wrong autocommit pattern ：
Set up autocommit after , We can carry out our normal business . The details are as follows ：
//0. Start transaction begin;/begin work;/start transaction; ( Choose one of the three ) //1. Find out the product information select
statusfrom t_items where id=1 for update; //2. Generate orders based on product information insert into t_orders
(id,goods_id)values (null,1); //3. Modify product status by 2 update t_items set status=2;
//4. Commit transaction commit;/commit work;
above begin/commit For the start and end of a transaction , Because we closed down in the previous step mysql Of autocommit, Therefore, it is necessary to manually control the transaction commit .
In the first step above, we perform a query operation ：select status from t_items where id=1 for update;
Unlike normal queries , We used it select…for update
The way , In this way, the pessimistic lock is realized through the database . At this time in the t_items In the table ,id by 1 We've locked that data , Other transactions can only be executed after the transaction is committed . In this way, we can ensure that the current data will not be modified by other transactions . It should be noted that , In a transaction , only
SELECT ... FOR UPDATE or LOCK IN SHARE MODE When operating the same data, it will wait for other transactions to finish before executing , commonly SELECT ...
Is not affected by this . Take the example above , When I execute select status from t_items where id=1 for update;
after . If I execute it again in another transaction select status from t_items where id=1 for update;
The second transaction will always wait for the first transaction to commit , At this point, the second query is in a blocked state , But if I'm executing in the second transaction select status from t_items
where id=1; The data can be queried normally , Not affected by the first transaction .
* Row Lock And Table Lock
use select…for update It will lock up the data , However, we need to pay attention to some lock levels ,MySQL InnoDB default Row-Level
Lock, So only 「 to make clear 」 Specifies the primary key or index ,MySQL Will be executed Row lock ( Lock only selected data ) , otherwise MySQL Will be implemented Table Lock
( Lock the entire data form ). Examples are as follows ：
1,select * from t_items where id=1 for update;
This statement specifies the primary key explicitly （id=1）, And there is this data （id=1 Data exists for ）, Then the row lock. Only the current data is locked .
2,select * from t_items where id=3 for update;
This statement specifies the primary key explicitly , However, there is no such data , It will not occur at this time lock（ There is no metadata , Again lock Who ?）.
3,select * from t_items where name=' mobile phone ' for update;
This statement does not 指定数据的主键,那么此时产生table lock,即在当前事务提交前整张数据表的所有字段将无法被查询.
4,select * from t_items where id>0 for update; 或者select * from t_items where
id<>1 for update;（注：<>在SQL中表示不等于）
5,select * from t_items where status=1 for update;（假设为status字段添加了索引）
6,select * from t_items where status=3 for update;（假设为status字段添加了索引）
乐观锁（ Optimistic Locking ）