This is the fourth day of my participation in Gwen Challenge

preface

Mysql is a relational database is widely used by everyone, and when it is in the case of high concurrency, there will be some dirty read, unrepeatable read, unreal read and other problems, may be online blogs to introduce this problem is everywhere, but I still want to write an article to deepen the impression and summary.

ACID: atomicity, consistency, isolation and persistence.

Atomicity: All operations in a transaction are indivisible in the database and either complete or none are performed.

Consistency: Several transactions executed in parallel, the results of which must be the same as those executed sequentially in a certain order.

Isolation: The execution of transactions is not interfered by other transactions. When the database is concurrently accessed by multiple clients, their operations are isolated to prevent dirty reads, phantom reads, and unrepeatable reads.

Persistence: For any committed transaction, the system must ensure that changes made to the database by that transaction are not lost.

READ UNCOMMITTED: UNCOMMITTED data can be READ. In RU mode, magic, unrepeatable, and dirty reads are allowed.

READ COMMITTED: Only COMMITTED data can be READ. Magic and unrepeatable reads are allowed. Dirty reads are not allowed.

REPEATABLE READ: The same transaction executes the same SELECT for many times and the data READ is not changed (in this case, the unrepeatable READ is only query). RR level solves the problem of unrepeatable READ but phantom READ.

SERIALIZABLE: SERIALIZABLE Unreal, unrepeatable, and dirty reads are not allowed at this level.

If you select the serialization level, it will cause performance degradation. Serialization will result in table-level locking, which will not be able to modify all records, and will not support multiple transactions to modify a table at the same time.Copy the code

Practice environment

Mysql 5.7.25, storage engine InnodbCopy the code

RR level illusion reading practice

Mysql set the isolation level of the current session to RR and set session Transaction Isolation Level REPEATABLE READCopy the code

When the second transaction is committed, the data whose ID_text is 2 cannot be queried in the first transaction, but when the id_text is 2 is inserted in the first transaction, the primary key repeat exception is reported, which is called phantom read. Obviously, the data cannot be queried, but the modification tells me that the data exists. At this level, there is no unrepeatable read. You can verify that the data queried in step 3 and step 5 are consistent. If they are inconsistent, there will be an unrepeatable read problem.Copy the code

RU level dirty read practices

Mysql set the isolation level of the current session to RU. Set session Transaction Isolation Level READ UNCOMMITTEDCopy the code

If the first transaction commits without any problems, it will probably do so. But if I Rollback, transaction 2 will read dirty data. It will affect business operations and cause unexpected disasters.Copy the code

# rC-level non-repeatable read practice

Mysql set the isolation level of the current session to RU, set session Transaction Isolation level READ COMMITTEDCopy the code

Not repeatable read performance in the same transaction, two of the same flash-back statements do not match the data query out, this is because the two query contains other transactions modifying data submitted, lead to the first and second query data inconsistency, it and read difference is dirty, it reads the data must be after the success of the transaction execution of data, Dirty reads are data that the transaction has not committed yet. The difference between dirty read and unrepeatable read is proved in step 6 and step 8. If the data in step 6 is dirty read, it is inconsistent with the data in step 4.Copy the code