Database, transactions, dirty data __ Database

A transaction of a database, which is a series of actions performed as a single logical unit of work. Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a unit that is either fully successful or all failed, you can simplify error recovery and make your application more reliable. To be a transaction, a logical unit of work must satisfy the so-called acid (atomicity, consistency, isolation, and durability) attribute.

Operation Process

Imagine a transaction of online shopping in which the payment process includes at least the following steps of the database operation:
· Update the inventory information of the goods purchased by the customer
· Save customer payment information-may include interaction with the banking system
· Generate the order and save it to the database
· Update user related information, such as shopping quantity, etc.
In normal circumstances, these operations will proceed smoothly, the final transaction is successful, and all database information related to the transaction is successfully updated. However, if anything goes wrong in any of these processes, such as an exception when updating inventory information on a commodity, a shortfall in the customer's bank account, and so on, it will result in a transaction failure. Once the deal fails, all information in the database must remain unchanged before the transaction, such as failure to update user information at the end of the transaction, so that the failed transaction does not affect the state of the database-the inventory information is not updated, the user is not paid, and the order is not generated. Otherwise, the information in the database will be confusing and unpredictable.
Database transactions are the techniques used to ensure the smoothness and predictability of transactions in this case.

ACID Properties for database transactions

Atomic Sex (atomic)
The transaction must be an atomic unit of work, or all of its data modifications, or none of them executed. Typically, the operations associated with a transaction have common goals and are interdependent. If the system only performs a subset of these operations, the overall goal of the transaction may be compromised. Atomicity eliminates the possibility that the system handles a subset of operations.
Consistency (consistent)
When a transaction completes, you must keep all the data in a consistent state. In the related database, all rules must be applied to the modification of the transaction to preserve the integrity of all data. When a transaction ends, all internal data structures, such as a B-tree index or a two-way list, must be correct. Some of the responsibility for maintaining consistency rests with the application developer, who must ensure that all known integrity constraints are enforced by the application. For example, when developing an application for transfer, avoid moving the decimal point during the transfer process.
Isolation (insulation)
Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state that the data is in when the transaction views the data, either when another concurrent transaction modifies its previous state, or when another transaction modifies its state, the transaction does not view the data in the middle state. This is called serializable because it can reload the starting data and replay a series of transactions so that the state of the data at the end is the same as that of the original transaction. The highest isolation level is obtained when the transaction is serializable. At this level, the results obtained from a set of transactions that can be executed in parallel are the same as those obtained by running each transaction continuously. Because height isolation limits the number of transactions that can be executed in parallel, some applications reduce isolation levels in exchange for greater throughput. Prevent data loss
Persistence (Durability)
After the transaction is completed, its impact on the system is permanent. This modification is maintained even if a fatal system failure occurs.

A database may have multiple access clients that can access the database in a concurrent manner. The same data in the database can be accessed by multiple transactions at the same time, and if the necessary isolation is not taken, it can cause a variety of concurrency problems and destroy the integrity of the data. These problems can be summed up into 5 categories, including 3 types of data-reading problems (dirty reads, phantom Read and non-repeatable reads), and 2 types of data update problems (first class loss update and second class loss update). Below, we explain the scenario that caused the problem, respectively, through an instance.

Dirty Read (dirty Read)  
    before explaining dirty reading, let's start with a joke: a stuttering person walking around the counter of the beverage shop, the boss greeted him warmly: "Drink a bottle." , Stammer hurriedly said: "I ... Drink... Drink ... ", the boss quickly opened the cans to stutter, stuttering finally suppressed his words:" I ... Drink... Drink... I can't afford to drink. ”。 In the joke, the owner of the beverage shop had dirty reading about the stutter. &NBSP
A transaction reads the changed data that is not yet committed by the B transaction and operates on the basis of this data. If the B transaction is rolled back, then the data read by a transaction is not recognized at all. Look at the cash-for-money transaction and the dirty read scene that was raised when the transfer transaction concurrency:
     

Time	Transfer Transaction A	Withdrawal Service B
T1		Start a transaction
T2	Start a transaction
T3		Inquiry account balance is 1000 yuan
T4		Take out 500 yuan to change the balance to 500 yuan
T5	Inquiry account balance is 500 yuan (dirty Read)
T6		Undo transaction balance restored to 1000 yuan
T7	Remit $100 to change the balance to $600
T8	Commit a transaction

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

Non-repeatable read (unrepeatable read)
Non-repeatable reading means that a transaction reads the changed data that B transaction has committed. Assuming that a in the process of withdrawing money, b transfer 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		Inquiry account balance is 1000 yuan
T4	Inquiry account balance is 1000 yuan
T5		Take out 100 yuan to change the balance to 900 yuan
T6		Commit a transaction
T7	Inquiry account balance is 900 yuan (inconsistent with T4 Read)

In the same transaction, the T4 time Point and T7 time point read account balances are different.
Phantom Read (Phantom Read)
A transaction reads the new data submitted by the B transaction, then a transaction will have an illusion read problem. The illusion of reading usually occurs in transactions that compute statistics, for example, assuming that in the same business, the total amount of two statistical deposit accounts in the same transaction, in the two statistics process, just add a deposit account, and deposit 100 yuan, at this time, the total amount of two statistics will be inconsistent:

time	statistic amount Transaction A	transfer transaction B
T1		Start Transaction
T2	start transaction	&nbs p;
T3	Statistics Total deposit count 10000 CNY	&nbs p;
T4		Add one save Account, deposit is 100 $
T5		COMMIT TRANSACTION
T6	again statistics Total deposit count is 10100 yuan (Phantom Read)

If the new data just satisfies the query criteria for the transaction, the data is entered into the business vision, resulting in two statistical inconsistencies.
Illusion reading and non-repeatable reading are two confusing concepts, the former refers to additional data that has been read to other committed transactions, and the latter refers to the change data (change or deletion) that has been committed to the transaction, in order to avoid both cases, the countermeasures taken are different to prevent reading to the change data. Simply adding row-level locks to the data in the operation, blocking the data in the operation from changing, and preventing the reading of new data, often requires adding table-level locks-locking the entire table and preventing new data.

First Class missing update
When a transaction is revoked, the updated data for the submitted B transaction is overwritten. This error can cause a serious problem, through the following account to withdraw money to be able to see:

Time	Withdrawal transaction A	Transfer Transaction B
T1	Start a transaction
T2		Start a transaction
T3	Inquiry account balance is 1000 yuan
T4		Inquiry account balance is 1000 yuan
T5		Remit $100 to change the balance to $1100
T6		Commit a transaction
T7	Take out 100 yuan to change the balance to 900 yuan
T8	Undo Transaction
T9	Balance restored to 1000 yuan (lost update)

When a transaction is revoked, "accidentally" the amount of the B transaction has been transferred to the account has been erased.