Thoroughly understand "dirty reads" in database transactions from multiple perspectives. "non-repetitive reads" and "virtual reads"

Dirty readDirty reads: This event occurs when the transaction reads data that has not been committed. For example, transaction 1 modifies a row of data, and then transaction 2 reads the modified row before transaction 1 submits the modification operation. If transaction 1 rolls back the modification operation, the data read by transaction 2 can be viewed as never existed.
Non-repeated readNon-repeatable reads: This event occurs when the transaction reads the same row of data twice but the data obtained each time is different. For example, transaction 1 reads a row of data, then transaction 2 modifies or deletes the row, and submits the modification operation. When transaction 1 tries to read the row again, it will get different data values (if the row is updated) or find that the row no longer exists (if the row is deleted ).
Virtual readPhantom read: If a row of data that meets the search criteria appears in the subsequent read operation, but the row of data does not belong to the original data, this event occurs. For example, transaction 1 reads some rows that meet certain search conditions, and then transaction 2 inserts a new row that meets the search conditions of transaction 1. If transaction 1 re-executes the query to generate the original rows, different rows will be obtained.

Transaction scenarioYes:
For a bank account with a deposit of RMB 200, Party A shall make a withdrawal of RMB 100, and Party B shall make a transfer of RMB 100 to account B. If the transaction is not isolated, the concurrency may be as follows:
1. Update of the first type of loss: When a withdraws funds, the account contains 200 yuan, and the transfer from B is also 200 yuan. Then, a and B operate simultaneously. Operation a successfully removes 100 yuan, B fails to roll back, and the account is eventually 200 yuan. As a result, a's operation is overwritten and the bank loses 100 yuan.
2. Dirty reading: Party A has not submitted the withdrawal of RMB 100. Party B transfers the funds to check that there is RMB 100 left in the account. This is because party A gave up the rollback operation and Party B submitted the normal operation, the account is eventually 0 yuan. B reads the dirty data of Party A, and the customer loses 100 yuan.
3. Virtual read: similar to dirty read, it is intended to solve the reading problem during the insert operation. For example, if a deposit of 100 yuan is not submitted, the Bank will make a report to make a statistical query account of 200 yuan, then, C submitted the ticket. Then, the bank made statistics and found that the account was 300 yuan. The bank could not determine which one should prevail?
It seems that statistics must be updated from time to time, which is normal. However, if statistics are performed in a transaction, they are not normal, for example, a statistical application needs to output the statistical results to the computer screen and the disk files of a remote computer.
To improve the performance and user response, we divide the process into two threads. Then, the first and later statistical data may be inconsistent, and we do not know which one prevails.
4. Repeatable reading: At the same time, both the accounts of Party A and Party B are found to be RMB 200. Party A first withdraws RMB 100 and submits the funds. At this time, Party B makes another query when preparing for the final update, if the result is 100 yuan, then B will be very confused. I don't know whether to change the account to 100 or 0.
The difference between dirty read and dirty read is that dirty read reads are the dirty data not committed by the previous transaction, and non-repeated reads are the data committed by the previous transaction.
5. Type 2 lost updates: this is a special case that cannot be read repeatedly. As shown above, B directly performs the operation instead of performing the second query, and the final amount in the account is 100 RMB, a's operations were overwritten, causing a bank loss of 100 yuan. It is similar to the first type of loss update.

When multiple transactions concurrently perform database operations, if there is no effective avoidance mechanism, There will be various problems. There are three problems in general:
1. Update loss
If both transactions need to update a database field x, x = 100
Transaction a transaction B
Read x = 100
Read x = 100
Write x = x + 100
Write x = x + 200
Transaction end X = 200
Transaction end X = 300
Last x = 300
In this case, the update of transaction a is overwritten and lost.
The loss of updates indicates that the transaction may encounter issues during database write operations.
2. Non-repeated read
When a transaction does not update the database data, the results of two or multiple operations on the same query should be consistent. If they are inconsistent, they are non-repeated reads.
Or use the above example
Transaction a transaction B
Read x = 100
Read x = 100
Read x = 100
Write x = x + 100
Read x = 200
Transaction end X = 200
Transaction end X = 200
In this case, transaction a reads x multiple times and the result is inconsistent, that is, it cannot be read repeatedly.
Another situation is Phantom.
Transaction A reads 15 records, transaction B deletes (ADDs) one record during transaction a's execution, and transaction a changes to 14 (16) when it reads again) this is called phantom read.
Non-repeated read operations indicate possible database read operations.
3. Dirty read (uncommitted read)
Prevents one transaction from reading records that have not been committed by another transaction.
For example:
Transaction a transaction B
Read x = 100
Write x = x + 100
Read x = 200
Transaction rollback x = 100
Read x = 100
Transaction end X = 100

X lock exclusive lock the locked object can only be read and modified by the transaction holding the lock. Other transactions cannot add other locks to the object or read or modify the object.
Objects locked by the S lock shared lock can be read by the lock transaction, but cannot be modified. Other transactions can also be added with the S lock.
Blocking protocol:
Level 1 blocking protocol:
Apply the X lock when the transaction modifies the data until the transaction ends (commit or rollback) to release the X lock. The level-1 blocking protocol can effectively prevent the loss of updates, but cannot prevent the occurrence of non-repeated dirty reads.
Level 2 blocking protocol:
Apply the S lock when the transaction reads data based on the first-level blocking, and release the lock after reading the data. The level-2 blocking protocol Prevents Loss of updates and dirty reads. It cannot prevent repeated reads.
Level 3 blocking protocol:
Apply the S lock when the transaction reads data based on the first-level blocking until the transaction ends and is released. The level-2 blocking protocol Prevents Loss of updates, dirty reads, and repeated reads.