What is dirty reading, non repeatable reading, phantom reading

1.Dirty read: Dirty read means that when a transaction is accessing data and has made changes to the data, and the changes have not yet been committed to the database, another transaction also accesses the data and then uses the data.

2.Non-repeatable read: refers to the reading of the same data multiple times within a transaction.

Before the transaction ends, another transaction accesses the same data. Then, between the two reads in the first transaction, the data read twice in the first transaction may not be the same due to the modification of the second transaction. In this way, the data that is read twice in a transaction is not the same, so it is called non-repeatable.

For example, a person reads the same document twice, but between reads, the author rewrites the document. When a person reads the document for the second time, the document has changed. The original read is not repeatable.

This problem can be avoided if people can only read the document after the author has finished writing it.

3.Phantom Read: A phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table, and this modification involves all the data rows in the table.

At the same time, the second transaction also modifies the data in the table, which inserts a new row of data into the table. Then, the user who performed the first transaction will find that there are still unmodified rows of data in the table, as if they were hallucinating. For example, a person changes a document submitted by the author, but when the production department merges its changes into the master replica of the document, it is discovered that the author has added the new material that is not in the document.

The problem can be avoided if no one can add new material to the document until the personnel and production departments have finished working on the original document.

The isolation property supports a total of five transaction settings, which are described as follows:

l default uses the isolation level set by the database (default) to determine the isolation level by the dba default setting

l read uncommitted will produce dirty reads, non-repeatable reads, and phantom reads (the lowest isolation level and high concurrency performance).

l read committed will have unrepeatable read, phantom read issues (locking the line being read).

l repeatable read will produce a magic read (locks all lines read).

l serializable guarantees that all situations will not occur (table locking).

The point of non-repeatable readability is to modify :

Under the same conditions, the data you have read is read again and the values are different.

The point of phantom reading is to add or delete the same condition, and the number of records on the first and second reads is different.

All three are error conditions for database transactions.

1. Dirty read: Transaction A reads the data that transaction B has not committed.

2. Non-repeatable read: transaction A gets a row of record row1 for the first query, and after transaction B submits the modification, transaction A gets row1 for the second query, but the column content changes.

3. Phantom reading: Transaction A gets a row of records row1 for the first query, and after transaction B submits the modification, transaction A gets two rows of records row1 and row2 for the second query.

All three are error conditions for database transactions.

1. Dirty reading Mu Yan: Transaction A reads the data that transaction B has not submitted.

2. Non-repeatable read: transaction A gets a row of record row1 for the first query, and after transaction B submits the modification, transaction A gets row1 for the second query, but the content of the column has changed.

3. Phantom reading: Transaction A gets a row of records row1 for the first query, and after transaction B submits the modification, transaction A gets two rows of records row1 and row2 for the second query.

All three are error conditions for database transactions. 1. Dirty read: Transaction A reads the data that transaction B has not committed.

2. Non-repeatable read: transaction A gets a row of record row1 for the first query, and after transaction B submits the modification, transaction A gets imitation Tongchong row1 for the second query, but the column content has changed.

3. Phantom reading: Transaction A gets a row of records row1 for the first query, and after transaction B submits the modification, transaction A gets two rows of records row1 and row2 for the second query.