Mysql transaction isolation mechanism

A premise.

It was the first time to write a blog, and I didn’t know what to write. I thought about mysql, the database I used most at work. So prepare yourself to summarize the isolation features of mysql things. Before you discuss this topic, first of all, you need to have mysql 5.5 or later. Now just remember that InnoDB supports things) and the engine has to be InnoDB. For those of you who don’t know, check the version number by selecting version() and check the driver by running Show Engines

Copy the code

It is clear that InnoDB is the default database after 5.5

Two. What kind of things?

Things can be understood as a logical set of operations, which are either performed or not performed

Three. What are the characteristics of things?

ACID: Atomicity, consistency, isolation, and persistence, respectively, referred to as the ACID properties of transactions

Level of thing isolation?
Transaction Isolation level Dirty read Unrepeatable read Phantom read
READ UNCOMMITTED may may may
READ COMMITTED Can’t be may may
REPEATABLE READ (REPEATABLE READ) Can’t be Can’t be may
Serializable Can’t be Can’t be Can’t be

  1. Dirty read: Data that has been changed by another transaction but has not yet been committed.

  2. Non-repeatable read: when data is queried or modified multiple times within a transaction scope, but the result is not expected.

  3. Phantom read: a transaction scope in which data is queried multiple times and the data is found to increase or decrease.

4. Simulate the influence of different levels of things on data?

Query the current system transaction isolation level. The system is in repeatable read state

  1. So do dirty reads exist?

This is the raw dataSo here we open up something AAnd then in the other object B to see if the data has been modified? It is obvious that the value of Quantity =1 was not READ, so it is clear that REPEATABLE READ does not cause a dirty READ problem

  1. Does it cause unrepeatable read problems

We only need to submit the modification operation of thing A on the basis of the previous one and then check the result of thing B, and we will find that the result of REPEATABLE READ does not change. Therefore, REPEATABLE READ will not cause this problem

  1. Does it cause magic reading problems?

First, the query operation is performed on object A

Second, insert into object B and commit the object

Then we insert into object A

Here a very common primary key conflict is reported, and then we look at the data where id=2 is not found

At this time, in order to make a further guess, we tried to add the data with ID =3, and found that the data was added successfully, but there was no query for the data with ID =2, so phantom read was generated.

.

This is the end of the first blog post, the rest of the situation can be modified according to this, what did not write clearly please point out (the first time to write hahaha)