Poor memory: 23-database transactions (1), 23-

Poor memory: 23-database transactions (1), 23-

Database Transaction refers to a series of operations performed as a single logical unit of work, either completely or completely. Transaction Processing ensures that data-oriented resources are not updated permanently unless all operations in the transaction unit are successfully completed. By combining a set of related operations into a unit that either succeeds or fails, you can simplify error recovery and make the application more reliable. To become a transaction, a logical unit of work must meet the so-called ACID (atomicity, consistency, isolation, and durability) attributes. A transaction is a logical unit in the database operation. The transaction management subsystem in the DBMS is responsible for processing transactions.

Transactions exist everywhere in our daily life, such as Internet shopping, bank deposit and withdrawal.

A common example is that Bank A has 200 yuan and Bank B has only 100 yuan.

A Transfers 50 RMB to B, A only has 150 RMB, and B has 150 RMB. (Expected)

If A transfers the money, B does not receive it; where is the money going?

If A transfers the money, B receives it, but A does not submit the money. B Transfers 50 yuan back to? Ah, why is there more money?

There are countless unexpected situations ....

1. Four major features of transactions (ACID)

Atomicity)

Atomicity means that a transaction is an inseparable unit of work. All operations in a transaction either succeed or fail. For example, if the SQL statement in the same transaction is either successfully executed or all SQL statements fail to be executed

Consistency)

The transaction must change the database from a consistent state to another consistent state. Taking the transfer as an example, A transfers money to B. Assume that the total amount of money for the two users is 2000 before the transfer, no matter how the two accounts transfer money to B, the sum of user A's money and user B's money is 2000, which is the transaction consistency.

Isolation)

The isolation of transactions is the transactions opened by the database for each user when multiple users access the database concurrently. The transactions cannot be disturbed by the operation data of other transactions. multiple concurrent transactions must be isolated from each other.

Durability)

Durability means that once a transaction is committed, its changes to the data in the database are permanent. In the future, even if the database fails, it should not have any impact on it.

 

2. Data inconsistency caused by common concurrent operations

If it is not locked 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 inconsistency caused by concurrent operations includes: data loss and modification, "dirty" Data Reading (dirty reading), non-repeated reading, and ghost data.

(1) Modify lost data

When two or more transactions select the same row and update the row based on the originally selected value, the update will be lost. Every transaction does not know the existence of other transactions. The last update will overwrite the updates made by other firms, which will lead to data loss. For example.

For example, two editors edit the same entry. Each editor independently changes its copy and saves the modified copy to overwrite the original document. The last edited member overwrites the changes made by the first edited member. This problem can be avoided if the second editor can make changes only after the first editor is complete.

(2) read "dirty" data (dirty read)

Reading "dirty" data means that transaction T1 modifies a data and writes it back to the disk. After transaction T2 reads the same data, T1, at this time, T1 restores the modified data to the original value. If the data read by T2 is inconsistent with that of the database, the data read by T2 is "dirty, that is, incorrect data.

For example, an editor is changing the entry. During the change process, another editor copied the document (this copy contains all changes made so far) and distributed it to the expected users. After that, the first editor thought that the entry was incorrect and deleted the newly modified document. However, the documents that are distributed to users contain no longer existing content. This problem can be avoided if no final-confirmed entry is read.

(3) Non-repeated read

Non-repeated reading refers to reading a row of data in a table in a transaction, and reading results are different for multiple times.

For example, if A bank wants to query the balance of account A, the first query of Account A is 200 yuan. At this time, A has committed 100 yuan to the account memory, and the bank then queries again, at this time, account A is 300 yuan. The Bank may be confused when the two queries are inconsistent.

The difference between non-repeated reads and dirty reads is that dirty reads read reads the dirty data not committed by the previous transaction, and non-repeated reads reread the data committed by the previous transaction.

Repeatable reading is not a technical issue. The key to solving this problem is to ensure the consistency of things. For example, a bank program needs to output the query results to the computer screen and write them to a file. If the data changes at this moment, the computer screen and file data are inconsistent. Data has actually changed, but if we want to do something, it is one thing. For example, you can view the data in front of the computer screen and then print the data. Instead of getting the latest data from the database, you must print the data on the computer screen.

(4) generate Ghost data

After T1 reads some records from the database based on certain conditions, T2 deletes some of the records. When T1 reads data based on the same conditions again, it finds that some records disappear.

After T1 reads some data records from the database based on certain conditions, T2 inserts some records. When T1 reads data again based on the same conditions, it finds that there are more records.