Mysql database — transaction isolation level and lock relationship

Time:2020-11-9

MySqldatabase——Transaction isolation level and lock relation learning

 

introduction:

For transactions, there are four levels of isolation. In this paper, through the understanding and summary of multiple blogs, plus the actual effect display and personal understanding, theMySqlFour transaction isolation levels and locks are analyzed.

 

One.Transaction isolation level:

Transaction hasThe higher the isolation level of transaction, the more consistent the data can be, but the more serialization, the lower the concurrency performance and the lower the efficiency.

Four transaction isolation levels:

Uncommitted read(RU)

Read committed(RC)

Repeatable(RR)

serialize/Serialization(Serializable)

These four isolation levels can solve the problems of transaction concurrency in different degrees——Dirty reading, non repeatable reading, unreal reading.

1.Dirty reading: YesABTwo things,BA transaction modifies a piece of data but does not commit; andAAfter that, the transaction reads the same piece of data. If it reads uncommitted modified data, it indicates that dirty reading has occurred.

2.Non repeatable:There is oneAAffairs and a number of other matters,ATransaction to a piece of data(Or multiple data in accordance with a query condition)Multiple read operations; other transaction pairsAThe data read by the transaction has been modified (this does not include the insertion of new data). If it is not submitted, in theRUThe modified data can be read in theRCThe modified data can be read at level and below; this may result inAWhen a transaction reads data many times, the data values read are inconsistent. This phenomenon is called non repeatable read.

3.Fantasy reading:It is difficult to find a comprehensive and completely correct explanation of the concept of fantasy reading on the Internet. The explanation here is my personal understanding, which may be biased.

There are two kinds of unreal reading

Unreal reading1: the same query statement, two times before and after reading, found that the number of data has changed. (RU,RCLevels don’t solve this problem,RRAnd serialization can solve this problem.)

Unreal reading2In the second case, there are two casesABTwo things,ATransactions query data by a certain condition,BBusiness inAA match is inserted after the transaction queryATransaction query condition data, hereBWhether the transaction is committed or not is consistent with that of non repeatable read. In theBAfter the transaction has finished its operation,ATransactions also want to be insertedBThe transaction inserts the data just now, but it is found that the insertion is not successful. If the data that was not read for the first time is not inserted successfully, this is called phantom readingABTwo things,ATransactions query data by a certain condition,BBusiness inAAfter transaction query, delete a previous oneAThe transaction queries the data in theBAfter the operation is completed,ABusiness also want toBThe data deleted just now is deleted, but the number of rows affected is0It is clearly found, but it can not be deleted. This situation is also illusory reading, and in theRRUnder the isolation level of, not only the number of rows is affected0The reason for this phenomenon is that the data still existRRLevel uses snapshot read (explained below). (RRIsolation level can avoid this situation to some extent, serialization can avoid it completely)

 

Two.The concept of lock:

1.By function:

Shared lock(Read lock):YesAIf a transaction locks a certain data, other transactions can only read the data, but cannot modify it.

Exclusive lock(Write lock):There is businessAIf a write lock is applied to some data, then onlyATransactions can manipulate this data, and other transactions can neither read nor change.

In fact, there are two kinds of intention locks, but the content of this paper is not related to intention locks, so we will not introduce them for the moment.

2.By scope:

Row lock:mysqlDatabaseInnoDBThe engine supports row locking to lock a row of data.

Watch lock:mysqlDatabaseMyISAMandInnoDBThe engine supports table locking, locking an entire table.

Page locks are not introduced here

3.By use:

Optimistic lock:In fact, from the perspective of implementation, optimistic locking does not perform locking operation, but uses version number to control data consistency. For example, when submitting modified data, it will judge whether the version number of the modified data is greater than the version number before modification. If it is greater than, it will be modified. Because optimistic lock does not have the cost of locking and unlocking, it is quite efficient. However, the disadvantage of optimistic lock is that it can not operate across applications, so the concept of pessimistic lock comes into being.

Pessimistic lock:Pessimistic locking is the real operation of locking and unlocking the data to be operated to ensure the concurrency of transactions. Although locking the data can reduce errors, locking and unlocking will increase the cost and reduce the efficiency.

Three.Relationship between transaction isolation level and lock:

This part mainly introducesmysqlHow are the four transaction isolation levels implemented in, which concurrency problems can be solved, and what locks are used to implement these four isolation boundaries.

Isolation level

Dirty reading

Non repeatable

Illusory reading

Uncommitted read(RU)

probably

probably

probably

Submitted degree(RC)

impossible

probably

probably

Repeatable(RR)

impossible

impossible

probably

Serializable(Serializable)

impossible

impossible

impossible

Before you start, you need to knowmysqTwo different kinds ofselectMethods:

Snapshot read:The read is the historical version of the record. In an unfinished transaction, the information read by the snapshot is consistent with the information read for the first time in this transaction (without read lock).

Current reading:The latest version of the record is read (with read lock).

 

1.Uncommitted read(RU)

1.1Implementation mechanism:

The transaction uses the current read when reading data

When a transaction modifies data, it adds a shared lock and releases it after it is committed (solves the problem that data is deleted or modified during modification)

1.2Dirty reading: will happen

The first is to open two sessions, which will be used laterAAffairs andBTransaction to refer to the two sessions, and then set the transaction isolation level toRU

The following operations are carried out in order:

ATransaction query information of a table:

BTransaction inserts new data but does not commit:

 

ATransaction queries this table again: readBDirty read occurs when the transaction is uncommitted

 

1.3Non repeatable reading: will occur

ATransaction queryidby30Data for:

BTransaction pairidby30Whether the data is submitted or not does not affect the results

 

ATransaction query againidby30Data: with the first readnumData values are different, non repeatable reading occurs

1.4Phantom reading: it will happen

ATransaction queryid<30Data for:

BTransaction inserts an entryid<30Data: whether submitted or not does not affect the results

ATransaction query againid<30Data: more data than the first time, unreal reading1happen

2.Read committed(RC)

2.1Implementation mechanism:

When the transaction reads the data, it adds the read lock (current read), and releases the shared lock after reading

When a transaction modifies some data, it will add a write lock until the end of the transaction. This mechanism ensures thatRCThere is no dirty read at the isolation level. Only committed transactions can be seen by other transactions

2.2Dirty reading: it won’t happen

The first is to open two sessions, which will be used laterAAffairs andBTransaction to refer to the two sessions, and then set the transaction isolation level toRC

The following operations are carried out in order:

ATransaction query information of a table:

 

BTransaction inserts new data but does not commit:

 

ATransaction queries this table again: not readBDirty reading of data inserted by transactions did not occur

2.3Non repeatable reading: will occur

RCNon repeatable read cases andRUIt’s similar, justBTransactions need to be committed when modifying data.

2.4Phantom reading: it will happen

RCThe situation of unreal reading under isolation level andRUIt’s similar, justBTransactions need to be committed when inserting data.

 

3.Repeatable(RR)

3.1Implementation mechanism:MVCCMysqlUnder theInnoDBEngine as an example)

Transactions use snapshot reading when reading data (to solve the problem of non repeatable reading)

Transactions add write locks when modifying data, andMysqlGap lock is adopted, but the premise of triggering gap lock is (the query condition column cannot be the only index and primary key). After the gap lock is triggered, the data in a certain range will be locked to prevent inserting data in this range. This mechanism can reduce the occurrence of phantom reading to a certain extent2Maybe.

3.2Dirty reading: it won’t happen

The first is to open two sessions, which will be used laterAAffairs andBTransaction to refer to the two sessions, and then set the transaction isolation level toRR

RRThe situation of dirty read under isolation level andRCequally

3.3Non repeatable reading: will not happen

ATransaction queryidby30Data for:

BTransaction pairidby30After modification, the transaction is committed

ATransaction query againidby30Data: with the first readnumThe data values are the same, and there is no case of non repeatable reading

 

 

 3.4Unreal reading: unreal reading will occur2There will be no phantom reading1(although other matters may stillinsertData, but due toRRSnapshot read is adopted, so it will not be readinsertData)

Unreal reading2: will happen

ATransaction query all data

BTransaction inserts a new piece of data: transaction needs to be committed

 

 

   ABecause I don’t knowBThe transaction has inserted this data, so I also want to insert this data:AThe transaction will fail to insert, and when all the data are read again, the data failed to be inserted due to the primary key constraint has not been found. In this case, a phantom read occurs2

Unreal reading2: will happen

ATransaction query all data

BTransaction deletionAOne of the data in the transaction query: the transaction needs to be committed

ABecause I don’t knowBTransaction has deleted this data, so I also want to delete this data:AAfter the transaction deletion statement is executed, it is found that the number of rows affected is0And when reading all the data again, it is found that the data has not been deleted. In this case, phantom reading also occurs2

3.5Occurrence of gap lock: if the query column has an index (and is not a unique index) and is not a primary key, the gap lock will trigger a certain range of locks, as shown in the following example; if the query column has no index and is not a primary key, then the gap lock will lock the entire table

ATransaction query all data:

ATransaction modificationnum=30Data for(numIt’s a general index)nameChange totest

 

BTransaction inserts an entrynum=32Data of: can be inserted successfully

BTransaction inserts an entrynum=29Data: found that the insertion of blocking, resulting in this phenomenon is due to the role of the gap lock, due toABusiness tonum=30The index column for the condition changes the data, so the gap locks the handlenum=30The range data at both ends is locked up, i.enum = (5,30)All data in this range are locked. Note that this is an open range. Data outside this range can be manipulated.

4.serialize(Serializable)

Implementation mechanism:

When a transaction reads data, it locks the whole table and releases it after committing or rolling back the transaction

When the transaction modifies the data, it adds a write lock to the whole table, and releases it after the transaction is committed or rolled back

This is the highest isolation level, which can solve dirty read, non repeatable read and unreal read, but at the same time, the efficiency is also the worst. The way to solve these problems caused by concurrent transactions is to turn these operations into serial operations. Once they do not meet the requirements, they will be blocked, so the efficiency is very poor.

 

 

Reference blog:

Virtual world Er:https://blog.csdn.net/qq_37960007/article/details/90644635?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~all~first_rank_v2~rank_v25-1-90644635.nonecase

Three stone rain: https://www.cnblogs.com/exceptioneye/p/5450874.html

Li junyang https://www.cnblogs.com/ljy-skill/p/10622865.html

Niu Chu Jiu: https://www.cnblogs.com/boboooo/p/12370770.html#4506259

Luke:https://zhuanlan.zhihu.com/p/109414420

Recommended Today

Api: tiktok: user video list

Tiktok tiktok, tiktok, tiktok, Api, Api, jitter, voice, bullet screen comments, jitter, jitter, and jitter. Tiktok tiktok tiktok data, jitter data acquisition, live broadcast of shaking sound Titodata: professional short video data acquisition and processing platform. For more information, please contact:TiToData Massive data collection Collect 500 million pieces of data for customers every day Tiktok […]