A good memory is better than a bad pen. 23-Database transactions (1)

A database transaction is a sequence of operations performed as a single logical unit of work, either completely or completely Transaction. 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 single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable. To become a transaction, a logical unit of work must satisfy the so-called acid (atomicity, consistency, isolation, and persistence) attributes. A transaction is a logical unit of work in a database run, which is handled by the transaction management subsystem in the DBMS.

Affairs, in our daily life, such as internet shopping, bank deposit and withdrawal, exist everywhere.

A common example is that bank a has 200 dollars and Bank B has only 100 dollars.

A for B to transfer 50 dollars, a only 150, B has 150 pieces. (expected)

If a turns the money, B does not receive it, and the money goes there?

If a turns the money, B receives, but the money of a does not submit; B turn back a 50 bucks? Eh, how much money?

There are countless unforeseen circumstances that occur ....

1. Four characteristics of transactions (ACID)

atomicity (atomicity)

atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either succeed or all fail. For example, in the same transaction , the SQL statement, or all of the execution succeeded, or all failed to execute

Consistency (consistency)

a transaction must transform the database from one consistent state to another consistent state. In the case of transfer, a to B transfer, assuming that before the transfer of the two users of the total amount of money is 2000, then A to B after the transfer, regardless of the two account how to turn, a user's money and b users of the total sum of 2000, this is the consistency of the transaction.

Isolation (Isolation)

The isolation of transactions is when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

persistence (Durability)

Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.

2. data inconsistencies caused by common concurrency operations

If there is no lock and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Data inconsistencies due to concurrency include loss of data modifications, reading of "dirty" data (dirty reads), non-repeatable reads, and phantom data generation.

(1) Loss of data modification

A missing update issue occurs when two or more transactions select the same row and then update the row based on the value originally selected. Every transaction is unaware of the existence of other transactions. The last update overrides updates made by other transactions, which results in data loss. As in the above example.

For example, two editors edit the same entry. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The last saved editor overrides the changes made by the first editor. If the second editor can make changes after the first editor finishes, you can avoid the problem.

(2) read "Dirty" data (Dirty Read)

read "Dirty" data refers to a transaction T1 modify a data, and write it back to disk, transaction T2 read the same data, T1 for some reason was removed, and at this time T1 the modified data back to the original value, T2 read the data and the data of the database inconsistent, then T2 read the data is "dirty "Data, that is, incorrect data.

For example, an editor is changing the entry. During the change, another editor copied the document (which contains all the changes made so far) and distributed it to the intended user. After that, the first editor thought the entry was wrong and deleted the newly modified document. However, the document that is distributed to the user contains content that no longer exists. You can avoid this problem if you do not read an entry that has no final confirmation.

(3) non-repeatable reading

Non-repeatable reading refers to reading a row of data from a table within a transaction, with multiple read results.

For example, the bank wants to query a account balance, the first query a account is 200 yuan, at this time a to the account memory of 100 and submitted, the bank then made a query, at this time a account is 300 yuan. Bank two queries inconsistent, may be very confused, do not know which query is accurate.

The difference between non-repeatable reads and dirty reads is that dirty reads are dirty data that was not committed by the previous transaction, and non-repeatable reads are re-reading the data submitted by the previous transaction.

Non-repeatable reading is not a technical problem, the key to solving this problem is to ensure that things are consistent. For example, the bank program needs to output the query results to the computer screen and write to the file, if in this moment the data has changed, the computer screen and file data inconsistent. The data is a surly change, but if what we're going to do is a thing. For example, in front of the computer screen to view and then print, then the output to the computer screen content to print, rather than to get the latest data from the database to print.

(4) generate Phantom data

T1 after some records have been read from the database according to certain criteria, T2 deleted some of the records, and when T1 again reads the data in the same condition, some records are found to disappear

T1 After some data records are read from the database on a certain condition, T2 inserts some records, and when T1 again reads the data in the same condition, it finds some more records.

A good memory is better than a bad pen. 23-Database transactions (1)