Database transactions (II)

Data concurrency Problems
A database may have multiple access clients, which can access the database concurrently. The same data in the database may be accessed by multiple transactions at the same time. If necessary isolation measures are not taken, various concurrency problems may occur and data integrity may be damaged. These problems can be attributed to five categories, including three types of data read problems (dirty read, phantom read, and non-repeated read) and two types of data update problems (first type of loss update and second type of loss update ). Next, we will explain the scenarios that cause problems through examples.

Dirty read)
Before explaining dirty reading, let's start with a joke: a person with a jieba is wandering in front of the beverage shop counter, and the boss greeted him with enthusiasm: "Have a bottle ?", Jieba hurriedly said: "I... Drinking... Drink ...", The boss opened the cans and handed them to jieba. jieba finally gave up his words: "I... Drinking... Drinking... I can't afford it !". In this joke, the beverage shop owner had dirty reads on jieba.
Transaction A reads and operates on the data that transaction B has not committed. If transaction B is rolled back, the data read by transaction A is not recognized at all. Let's look at the dirty read scenarios caused by concurrent withdrawals and transfers:

Time	Transfer transaction	Withdrawal transaction B
T1		Start transaction
T2	Start transaction
T3		The account balance is USD 1000.
T4		Retrieve 500 yuan and change the balance to 500 yuan
T5	The account balance is 500RMB (dirty read)
T6		The withdrawal transaction balance is restored to 1000 yuan
T7	Remit 100 yuan to change the balance to 600 yuan
T8	Commit transactions

In this scenario, B wants to withdraw 500 yuan and then withdraw the action, while A transfers 100 yuan to the same account because transaction A reads data that transaction B has not committed, as a result, the account lost 500 yuan in vain.

Unrepeatable read)
Non-repeated read refers to transaction A reads the changed data that transaction B has committed. Assume that A transfers 100 yuan to the account during the withdrawal transaction, and the balance of the two read accounts of A is inconsistent:

 

Time	Withdrawal transaction	Transfer transaction B
T1		Start transaction
T2	Start transaction
T3		The account balance is USD 1000.
T4	The account balance is USD 1000.
T5		Retrieve 100 yuan and change the balance to 900 yuan
T6		Commit transactions
T7	The account balance is 900Yuan (and T4Read inconsistency)

In the same transaction, the account balance read at the T4 and T7 time points is different.
Phantom read)
When transaction A reads new data committed by transaction B, the phantom read problem occurs in transaction. Phantom reading generally occurs in transactions that calculate statistical data. For example, if the banking system is in the same transaction, the total amount of the deposit account is counted twice. During the two statistical processes, A new deposit account is added and 100 yuan is saved. At this time, the total amount of the two statistics will be inconsistent:

Time	Transaction	Transfer transaction B
T1		Start transaction
T2	Start transaction
T3	The total number of deposits is 10000 yuan.
T4		Add a new deposit account with a deposit of 100 yuan
T5		Commit transactions
T6	Counting the total number of deposits again is 10100Yuan (phantom read)

If the new data meets the transaction query conditions, the new data enters the transaction field of view, resulting in two statistical inconsistencies.
Phantom read and non-repeated read are two confusing concepts. The former refers to reading new data from other committed transactions, the latter means that the changed data (change or delete) of the committed transaction has been read. To avoid these two situations, the countermeasures are different to prevent reading the changed data, you only need to add row-level locks to the operation data to prevent changes in the operation data. To prevent reading new data, you often need to add a table-Level Lock to lock the entire table, prevents new data.

Category 1 lost updates
When transaction A is canceled, the updated data of transaction B that has been committed is overwritten. This error may cause serious problems. You can see it through the following account withdrawal transfer:

Time	Withdrawal transaction	Transfer transaction B
T1	Start transaction
T2		Start transaction
T3	The account balance is USD 1000.
T4		The account balance is USD 1000.
T5		Remit 100 yuan to change the balance to 1100 yuan
T6		Commit transactions
T7	Retrieve 100 yuan and change the balance to 900 yuan
T8	Cancel transaction
T9	Balance restored to 1000Metadata (lost updates)

When transaction A revokes the transaction, it accidentally erased the amount that transaction B has transferred to the Account.
Type 2 lost updates
Transaction A overwrites the data already submitted by transaction B, resulting in the loss of operations performed by transaction B:

Time	Transfer transaction	Withdrawal transaction B
T1		Start transaction
T2	Start transaction
T3		The account balance is USD 1000.
T4	The account balance is USD 1000.
T5		Retrieve 100 yuan and change the balance to 900 yuan
T6		Commit transactions
T7	Import 100 RMB
T8	Commit transactions
T9	Change balance to 1100Metadata (lost updates)

In the above example, the check transfer transaction overwrites the withdrawal transaction's updates to the deposit balance, resulting in the Bank's final loss of 100 yuan. On the contrary, if the transfer transaction is committed first, the user account will lose 100 yuan.