Before we learn about dirty reads, non-repeatability, and phantom reads, it is important to understand that these three conditions are associated with concurrent database transactions. If all reads and writes are performed in the form of queues, there will be no problem.

Noun resolution and solution dirty reading

Dirty reads are also called invalid data reads (dirty data is read). A transaction that reads data that another transaction has not committed is called a dirty read.

For example, if transaction T1 changes a row in a table, but does not commit it, then transaction T2 reads the data changed by transaction T1, and then transaction T1 rolls back for some reason, the data read by transaction T2 is dirty.

Solution: Change the transaction isolation level of the database to READ_COMMITTED (read commit/unrepeatable read)

Unrepeatable read

Non-repeatable reads are when the same query returns different results twice within the same transaction.

For example, transaction T1 reads data twice. After reading a piece of data for the first time, transaction T2 modifies the data and commits the transaction. At this time, T1 reads the data again and gets different results.

Change the transaction isolation level of the database to REPEATABLE_READ.

Phantom read

Phantoms are also illusory phenomena that occur when data currently affected by another transaction is inserted or deleted while the transaction is not executing independently.

For example, when system transaction A deletes all data from the database, transaction B inserts A new record at this time, and when transaction A finishes deleting, it finds another record, just like an illusion. This is called phantasm.

Solution: Change the transaction isolation level of the database to SERIALIZABLE_READ (serialized execution), or lock the database user.

Digression: Non-repeatable reading occurs mostly because of modification; Magic reading focus is to add, delete. REPEATABLE_READ mode in mysql introduced a GAP lock (GAP) to solve the phantom reading problem. No matter how to solve the illusion, will pay a price performance concession. Tradeoffs between business requirements and technical solutions are one of the most important skills a technician can master.

I hope we can exchange comments and learn from each other

Transfer: www.cnblogs.com/vinter/p/12…