当前位置：网站首页>MySQL InnoDB transaction

MySQL InnoDB transaction

2022-04-23 15:09:00 【InfoQ】

Business  ACID  characteristic

Atomicity （Atomicity）
： The smallest unit of work , The entire unit of work is either submitted successfully together , Either all failures roll back .

Uniformity （Consistency）
： The data and state changes of operations in transactions are consistent , That is, the result of writing data must fully comply with the preset rules , It will not lead to inconsistent status due to system accidents and other reasons .

Isolated type （Isolation）
： Data operated by a firm before submission , Visibility settings for other transactions （ Normally set to invisible ）.

persistence （Durability）
： The changes made by the firm will be permanently saved , The system will not cause accidental data loss .

What problems do transaction concurrency bring

Dirty reading

null
such as  user  There is a user data in the table , Do the following ：

  • Business B to update  id=1  The data of ,age  Updated to 18, Do not commit transaction
  • Business A Inquire about  id=1  The data of
  • Business B Rollback the update operation just now

This is the time , Business A Found in  id=1  The data of ,age  The value of is 16 
《 A big factory Java Analysis of interview questions + Back end development learning notes + The latest architecture explanation video + Practical project source code handout 》 Free open source Prestige search official account 【 Advanced programming 】
  still 18？

It can't be read repeatedly

null
such as  user  There is a user data in the table , Do the following ：

  • Business A Inquire about  id=1  The data of
  • Business B to update  id=1  The data of ,age  Updated to 18, And commit the transaction
  • Business A Query again  id=1  The data of

This is the time , Business A Found twice  id=1  The data of ,age  The value of is 16 still 18？

Fantasy reading

null
For example  user  The following operations are performed in the table ：

  • Business A Inquire about  age>15  The data of
  • Business B Add a piece of data ,age=22, And commit the transaction
  • Business A Query again  age>15  The data of

This is the time , Business A The data queried twice , The number is 1 Article or 2 strip ？

There are four levels of transaction isolation

SQL92 ANSI/ISO standard
：http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Read Uncommitted（ Uncommitted read ）
?--
Unresolved concurrency issue

The uncommitted transaction is also visible to other transactions , Dirty reading （dirty read）

Read Committed（ Submit to read ）
?--
Solve the dirty reading problem

After a transaction starts , You can only see the changes made by the firm you submitted , It can't be read repeatedly （non repeatable?read）

Repeatable Read（ Repeatable ）
?--
Solve the problem of non repeatable reading

Reading the same data multiple times in the same transaction results in the same , This isolation level is not defined to solve the problem of unreal reading

Serializable（ Serialization ）
?--
Solve all the problems

Highest isolation level , By forcing the serial execution of a transaction

InnoDB  The degree to which the engine supports the isolation level

null
Concurrency at transaction isolation level ： Uncommitted read  >  Read committed  >  Repeatable  >  Serialization

InnDB  The default transaction isolation level of the engine is repeatable （Repeatable Read）, At this level, it also solves the problem of unreal reading .InnDB  The transaction isolation level in is achieved by locking 、MVCC  Realization .

Use the following statement to view  InnoDB  Default transaction isolation level for ：

/*  overall situation 、 Isolation level of the current session  */SELECT @@global.tx_isolation, @@tx_isolation;

null
Set... With the following statement  InnoDB  Transaction isolation level for ：

/*  Set the global isolation level  
/set global transaction isolation level read committed;/
  Set the isolation level of the current session  */set session transaction isolation level read committed;

Next let's test  InnoDB  Default transaction isolation level for （Repeatable Read） Whether the problem of dirty reading has been solved 、 It can't be read repeatedly 、 The problem of unreal reading .

Data preparation ：

CREATE TABLE 
user
 (
id
 int(11) NOT NULL,
name
 varchar(32) NOT NULL,
age
 int(11) NOT NULL,PRIMARY KEY (
id
)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into 
user
 (
id
, 
name
, 
age
) values('1','Ada','16');

Whether the problem of dirty reading has been solved ？

null
Open two  MySQL  client , Simulate transaction A And transaction B The operation of , The steps are as follows ：

  • Business B Turn on manual transactions , to update  id=1  The data of ,age  Updated to 18, Do not commit transaction
  • Business A Inquire about  id=1  The data of
  • Business B Rollback the update operation just now

The numbers in the figure are the execution steps , As can be seen from the figure below, the transaction A The result of the implementation of 16,InnDB  The default transaction isolation level perfectly solves the problem of dirty reading .

null

Whether the problem of non repeatable reading has been solved ？

null
Open two  MySQL  client , Simulate transaction A And transaction B The operation of , The steps are as follows ：

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204231408164896.html

边栏推荐

猜你喜欢

随机推荐