Database principles of the Business (ii)

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.