One . Problems in concurrent scenarios

Compared to serial processing , Concurrent transaction processing can significantly improve the transaction throughput of the database 、 Improve resource utilization . stay MySQL Practical application , Depending on the scene , It can be divided into the following categories :

  • Read concurrent
  • Read write concurrency
  • Write concurrent

In these scenarios , There may be a loss of updates 、 Dirty reading 、 Non repeatability 、 The problem of unreal reading .

  • Update missing : When multiple transactions update a 1/n Line data , The last committed transaction will override the previously committed update .
  • Dirty reading : A transaction is inserting / Update a row of data , Before the transaction is committed , This data is in “ atypism ” state . Other transactions read this “ Dirty data ” Based on this, further treatment is carried out , This creates a dependency on uncommitted data , This phenomenon is called dirty reading .
  • It can't be read repeatedly : A transaction queries a piece of data after a certain period of time , But found that the data has been updated or deleted , This phenomenon is known as unrepeatable reading .
  • Fantasy reading : A transaction queries data twice with the same query criteria , The second query result shows new data that the first query didn't have , This is called Unreal reading .

among , The problems in various concurrency scenarios are as follows :

  • Reading concurrency scenarios does not cause data inconsistency , Therefore, no special treatment is required .
  • Dirty reading may occur in read-write concurrency scenarios 、 It can't be read repeatedly 、 The problem of unreal reading .
  • Writing concurrent scenarios may result in the loss of updates .

Two . Transaction isolation level

In view of the problems described above ,MySQL Different transaction isolation levels are used to solve some of the above problems , As follows :

Isolation level meaning Read data consistency Update missing Dirty reading It can't be read repeatedly Fantasy reading
Read uncommitted (Read Uncommitted) Changes in transactions , Even if it's not submitted , It's still visible to other things . The lowest level , We can only guarantee that we will not read physically damaged data .   no   yes     yes   yes  
Read submitted (Read Committed) Only changes made by the submitted firm are visible to other transactions . Sentence level   no no yes yes
Repeatable (Repeatable Read ) The result of reading the same record multiple times in the same transaction is consistent . Transaction level   no no no no
Serialization (Serializable) Lock every line read , Force transactions to execute serially , Concurrency is not supported . Transaction level   no   no   no no

In the above isolation levels , From top to bottom, concurrency performance decreases in turn , Security is improved in turn .InnoDB The default transaction isolation level in the storage engine is RR, Through the following SQL Query transaction isolation level .

select @@global.tx_isolation;

3、 ... and . Implementation of transaction isolation level

InnoDB Implementation of transaction isolation level , Basically can be divided into the following two or a combination of two :

1. Multi version concurrency control (MVCC,Multiversion Currency Control)

MVCC It can be used to solve the problem of dirty reading in the context of read-write concurrency 、 Unrepeatable read problem . Specific applications under different isolation levels are as follows :

  • RC: It can solve the problem of dirty reading .
  • RR: It can solve the problem of dirty reading 、 Unrepeatable read problem .

Specific implementation can refer to here , Here is a simple supplement to the article in the link :

Compare with RC,RR The reason why it can solve the problem of non repeatable reading , The reason lies in RR Read at isolation level Read-View It's transaction level ,RC It's statement level . namely :RR Read only once at the beginning of a transaction Read-View, So multiple queries in a transaction depend on the same Read-View, Can achieve repeatable reading .RC Each query under the level is read      Abreast of the times Read-View, There is no guarantee that the read in the same transaction will be sequential Read-View It's the same , This is also the reason why there are differences in the data read one after another .

2. lock

A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently . How to ensure the consistency of data concurrent access in database 、 Validity is a problem that all databases must solve , Lock conflicts are also an important factor affecting the performance of concurrent database access . From this perspective , Lock is very important for data .

Lock can solve the problem of unreal reading in the situation of concurrent reading and writing described above 、 The problem of update loss in the scenario of concurrent writing . The following is about locks 、 The types of locks and their application scenarios are described in detail .

2.1 The basic principle of lock

http://mysql.taobao.org/monthly/2016/01/01/

https://mp.weixin.qq.com/s/yq5Erdv5Dft3foJEVE2dxA

https://i6448038.github.io/2019/02/23/mysql-lock/

https://www.163.com/dy/article/G0S7FSGG05319WXB.html

duplicate key check When checking, you need to add... To the secondary unique index gap lock( edition :5.6 Before ,5.7.26-29 Then add record lock)

https://juejin.cn/post/6844903597029720072

https://my.oschina.net/hebaodan/blog/3045246

Deadlock scenarios :

  • Business 1,2 All get a line of data s After locking , All want to get x lock .
  • Business 1,2 After obtaining a certain clearance lock , Want to insert , That is to add the insert intent lock .
  • Business 1,2 Operate on the same data . Due to different search conditions , Business 1,2 The cluster index and secondary index are locked respectively , Then transaction 1 Unable to operate secondary index ( Delete first , Insert again ), Business 2 Unable to operate cluster index , There's a deadlock .( Reference resources http://mysql.taobao.org/monthly/2016/01/01/)

through “ lock ” Look at things InnoDB Processing of concurrency ? More articles about

  1. Java Concurrent ( Nine )【 Reprint 】 I can't help saying that Java“ lock ” things

    Reprinted from Meituan technical team , Link to the original text   I can't help saying that Java“ lock ” things Preface Java Provides a wide variety of locks , Each lock has its own characteristics , Can show very high efficiency in the appropriate scene . This article aims at lock related source code ( The source code in this article comes from JDK ...

  2. Did you use the right lock ? Talking about Java “ lock ” things

    Every era , Will not treat those who can learn badly Hello everyone , I am a yes. I was going to keep writing about message queuing , But recently I've been bringing in new colleagues , I found that my new colleagues have some misunderstandings about locks , So let's talk about it today " lock " Things and things Java in ...

  3. InnoDB High concurrency principle

    One . concurrency control Why concurrency control ? Concurrent tasks operate on the same critical resource , If no measures are taken , May lead to inconsistency , So concurrency control is necessary (Concurrency Control). technical , How to control concurrency in general ? ...

  4. Netty Those of ” lock ” things

    Netty The five key points of lock up : ① Care about the object and scope of the lock   --> Reduce granularity ②   Note the size of the lock object itself    --> Reduce space occupation ③ Pay attention to the speed of the lock --> Increase speed ④ Different scenes have different choices ...

  5. MySQL lock :02.InnoDB lock

    Catalog InnoDB lock InnoDB Row lock implementation mechanism InnoDB Implicit . Explicit lock InnoDB Lock type Shared lock Exclusive lock Intent locks InnoDB Lock compatibility InnoDB Row lock range . Particle size InnoDB Row lock granularity list I want to plug in ...

  6. MySQL lock :03.InnoDB Row lock

    Catalog InnoDB Row lock The views and data dictionaries that can be used for lock checking InnoDB Row lock compatibility InnoDB Shared lock of row lock Shared lock : see InnoDB lock InnoDB Row lock implementation mechanism Lock the normal index InnoDB Implicit . display ...

  7. What do you think innodb Of B+TREE The layer number ?

    What do you think innodb Of B+TREE The layer number ?, Let's say sysbench_testdata.sbtest2 For example, check the number of index layers : - Check out related systems root@localhost [sysbench_testdata ...

  8. [ turn ] through Linux The kernel looks at lock free programming

    Nonblocking synchronization (Non-blocking Synchronization) brief introduction How to protect the shared data correctly and effectively is a difficult problem for writing parallel programs , The usual means is synchronization . Synchronization can be divided into blocking synchronization (Blocking ...

  9. MySQL MyISAM/InnoDB High concurrency optimization experience

    A recent application , The functional requirements are very simple , Namely key/value Formal storage , ordinary INSERT/SELECT, No complex queries , The only problem is that the volume is very large , If it's in use right now , The early single watch insert Frequency about ...

  10. MySQL InnoDB Configure concurrent threads ( innodb_thread_concurrency)

    http://www.ywnds.com/?p=9821 One .thread_concurrency First , The most important point , This parameter is already in the latest version of MySQL Has been removed from , Official latest 5.7 Version of doc It's right up there t ...

Random recommendation

  1. About linux curl The problem of address parameter

    for example  url by http://mywebsite.com/index.PHP?a=1&b=2&c=3web Visit in the form of url Address , Use $_GET You can get all the parameters, but in Linux Next ...

  2. 【 turn 】Unity3D in Layers and LayerMask analysis

    http://blog.csdn.net/yupu56/article/details/50441151 Unity Medium used int32 To express 32 individual Layer layer .int32 It means that the binary system has a total of 32 position (0-31 ...

  3. Study jsp(1)

    My development environment is :jdk1.7;netbean ;tomcat7. Trying to write the first program : ublic class myServlet extends HttpServlet{    @Override ...

  4. C# Java Between RSA Encryption and decryption interaction ( Two )

    original text :C# Java Between RSA Encryption and decryption interaction ( Two ) Then the previous article C# Java Between RSA Encryption and decryption interaction , Continue to explore this issue . in front , Although it has been realized C# Java Between RSA Encryption and decryption interaction , But still with ...

  5. 《Python Learning manual 4th》 Chapter nine Tuples 、 Documents and others

    ''' Time : 9 month 5 Japan - 9 month 30 Japan requirement : 1. The content of the book is summarized , Organize and upload notes in blog Garden 2. Finish all the exercises after class notes :“#” The following is the remarks ( Every day to see 42 Page content , It's guaranteed to finish reading the book by the end of the month ) “ a key ...

  6. Set up qualified DataGridView There are two ways to change the color of the line

    Private Sub dgvInfo_RowPrePaint(sender As Object, e As DataGridViewRowPrePaintEventArgs) Handles dgv ...

  7. 4.IIC Bus

    One .IIC Bus description :      IIC The bus timing only has the duration of high and low levels, which is generally greater than us/ms.      iic sequential :            Start : When SCL For high voltage ,SDA Switch from high level state to low level state ...

  8. kafka On the modification of the number of copies and partitions of the number of cases of actual combat ( Can also be used as leader Node equilibrium case )

    kafka On the modification of the number of copies and partitions of the number of cases of actual combat ( Can also be used as leader Node equilibrium case ) author : Yin Zhengjie Copyright notice : Original works , Declined reprint ! Otherwise, the legal liability will be investigated . One . About topic Modification of partition number 1>. establish 1 branch ...

  9. [LeetCode] 1. Two Sum_Easy

    Given an array of integers, return indices of the two numbers such that they add up to a specific ta ...

  10. 【BZOJ】2120: Number color

    Answer key Practice with Mo team First, sort by the block of the left endpoint , Then sort by the right end of the block , And then in chronological order Each modification operation can be operated in reverse order by saving the value at this position before modification Code #include <bits/stdc++.h ...