Database principles of the Business (ii)

This paper mainly analyzes a series of problems in database concurrency control . Transactions are the basic unit of concurrency control, and ensuring that the properties of a transaction's acid is an important task for transactional processing, while concurrent operations can disrupt their acid properties.

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 three types of data-reading problems (dirty reads, Phantom reads, and non-repeatable reads) and two 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.

First, data reading problems

1. Dirty Reading (Dirty read)

First, for example, there is a joke: a stuttering in front of the drinks shop counter, the boss warmly greeted: "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 .

Reading "Dirty" data means that transaction a modifies a data, and writes it back to disk, after transaction B reads the same data, a for some reason is removed, and at that time a changed the modified data back to the original value, b read the data and the data of the database inconsistent, B read the data is "dirty" data, that is, invalid data.

Consider the process of dirty reading in the case of access money:

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.

2, 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 this scenario, the T4 time point of the same transaction and the T7 point-in-time read account deposit balance are different.

3. Phantom Reading (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.

Second, data writing problems

1, the first category of 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)

In this scenario, when a transaction is revoked, "careless" erases the amount that the B transaction has transferred to the account.

2, the second category of 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 this 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 transfer transaction first, then the user account will lose 100 yuan.

Database principles of the Business (ii)