Database Transactions (ii)

Problems with data concurrency
A database may have multiple access clients that can access the database concurrently. The same data in the database may be accessed by multiple transactions at the same time, and if the necessary isolation measures are not taken, it can cause various concurrency problems and destroy the integrity of the data. These problems can be attributed to Class 5, which includes 3 types of data-read problems (dirty reads, Phantom reads, and non-repeatable reads), and 2 types of data update issues (first-class missing updates and second-class missing updates). Below, we explain the scenario that caused the problem separately by example.

Dirty Reads (Dirty read)
Before explaining the dirty reading, let's start with a joke: a stuttering person walking around in front of the drinks shop counter, the boss greeted warmly: "Drink a bottle?" , "stammered hurriedly said:" I ... Drink... Drink ... ", the boss quickly opened the cans handed to stutter, stuttering finally suppressed his words:" I ... Drink... Drink... I can't afford to drink it! ”。 In this joke, the owner of the beverage shop had a dirty reading of the stutter.
A transaction reads the change data that the B transaction has not yet committed and operates on the basis of this data. If the B transaction happens to be rolled back, then the data read by a transaction is not recognized at all. Look at the dirty read scenario that is raised when the withdrawal transaction and the transfer transaction are concurrent:

Time	Transfer Transaction A	Withdrawal transaction B
T1		Start a transaction
T2	Start a transaction
T3		Enquiry account balance of 1000 yuan
T4		Remove $500 and change the balance to $500
T5	Enquiry account balance of Meta (Dirty Read)
T6		Undo transaction balance restored to $1000
T7	Import $100 to change the balance to $600
T8	Commit a transaction

In this scenario, B wants to withdraw 500 yuan and then cancel the action, and a to the same account to transfer 100 yuan, because a transaction read the B transaction has not yet submitted data, resulting in the account lost 500 yuan.

Non-repeatable read (unrepeatable read)
Non-repeatable read refers to a transaction that reads the change data that the B transaction has committed. Suppose a in the process of withdrawal transaction, B transfers to the account 100 yuan, a two read the balance of the account is inconsistent:

Time	Withdrawal transaction A	Transfer Transaction B
T1		Start a transaction
T2	Start a transaction
T3		Enquiry account balance of 1000 yuan
T4	Enquiry account balance of 1000 yuan
T5		Remove $100 and change the balance to $900
T6		Commit a transaction
T7	Check account balance for yuan (and T4 inconsistent Read)

In the same transaction, the T4 time Point and the T7 point-in-time read Account deposit balance are not the same.
Phantom Read (Phantom Read)
A transaction reads the new data submitted by the B transaction, at which point a transaction will have phantom read. Phantom reading generally occurs in the transaction of computing statistics, for example, assuming that the banking system in the same transaction, the total amount of two statistics deposit accounts, in two statistics process, just add a deposit account, and deposited 100 yuan, then two times the total amount of statistics will be inconsistent:

Time	Statistic amount Transaction A	Transfer Transaction B
T1		Start a transaction
T2	Start a transaction
T3	Total number of deposits is $10000
T4		Add a deposit account with a deposit of $100
T5		Commit a transaction
T6	re-count the total deposit as 10100 yuan (Phantom Read)

If the new data just satisfies the query criteria of the transaction, the data enters the field of view, resulting in two statistical inconsistencies.
Phantom Read and non-repeatable reading are two confusing concepts, the former refers to other new data that has been committed to the transaction, while the latter refers to the change data (changes or deletions) that have been committed to the transaction, in order to avoid these two situations, the countermeasures are different to prevent reading to the change data, Simply adding row-level locks to the operational data, blocking data changes in the operation, and preventing reading to new data often requires adding table-level locks-locking the entire table to prevent new data.

First category missing updates
When a transaction is revoked, the updated data of the already committed B transaction is overwritten. This error may cause serious problems, as can be seen through the following account withdrawal transfer:

Time	Withdrawal transaction A	Transfer Transaction B
T1	Start a transaction
T2		Start a transaction
T3	Enquiry account balance of 1000 yuan
T4		Enquiry account balance of 1000 yuan
T5		Import $100 to change the balance to $1100
T6		Commit a transaction
T7	Remove $100 and change the balance to $900
T8	Revoking a transaction
T9	balance restored to Meta (missing update)

When a transaction is withdrawn, "careless" erases the amount of the B transaction that has been transferred to the account.
Category two missing updates
A transaction overwrites the data already submitted by the B transaction, causing the operation of the B office to be lost:

Time	Transfer Transaction A	Withdrawal transaction B
T1		Start a transaction
T2	Start a transaction
T3		Enquiry account balance of 1000 yuan
T4	Enquiry account balance of 1000 yuan
T5		Remove $100 and change the balance to $900
T6		Commit a transaction
T7	Import $100
T8	Commit a transaction
T9	Change the balance to 1100 Meta (missing update)

In the above example, because the cheque transfer transaction covers the withdrawal transaction to the deposit balance of the update, resulting in the bank lost 100 yuan, on the contrary, if the transaction is first submitted, then the user account will lose 100 yuan.

Database Transactions (ii)