Understanding of database transactions

1. What business?

is a series of manipulations that are performed as a single logical unit of work, either completely or completely without executing them.  In layman's terms, such as accomplishing one thing is divided into 4 steps a,b,c,d; Each step is a logical unit, and accomplishing one thing is a transaction, which is done either in order or completely without execution. So maintain the integrity of the manipulation.

2. Four features of a transaction :

(1) Atomic atomic) (atomicity)

The transaction must be an atomic unit of work, either all executed or not executed for its data modification. Typically, the operations associated with a transaction have a common purpose and are interdependent. If the system only performs a subset of these operations, it may break the overall goal of the transaction. Atomicity eliminates the possibility of a system processing a subset of operations.

(2) The consistency (consistent) (consistency) transaction must be completed to keep all data in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct. Some of the responsibility for maintaining consistency is borne by application developers who must ensure that all known integrity constraints are enforced by the application. For example, when developing an application for transfer, avoid arbitrarily moving the decimal point during the transfer process.

(3) Isolation (insulation) (isolation) changes made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state in which the data is located when the transaction is viewing the data, either when another concurrent transaction modifies its state or after another transaction modifies it, and the transaction does not view the data in the middle state. This is called isolation because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution. 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 high isolation limits the number of transactions that can be executed in parallel, some applications reduce the isolation level in exchange for greater throughput.

(4) After the Persistence (Duration) (durability) transaction is complete, its effect on the system is permanent. This modification will persist even if a fatal system failure occurs.

3. Transaction 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.

Issues that may arise from transactional concurrency, and when 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 made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, overwriting the original document. The editor who last saved its change copy 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 the 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 the data read by T2 is "dirty" data, that is, incorrect data. For example, an editor is changing an electronic document. 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 changes were wrong, and then deleted the edits and saved the document. Documents that are distributed to users contain edits that no longer exist, and those edits should be considered to have never existed. This problem can be avoided if no one is able to read the changed document until the first editor determines the final change.

(3) Non-repeatable read refers to transaction T1 after reading the data, the transaction T2 perform the update operation, so that T1 cannot read the previous result. Non-repeatable reads include three situations: after a transaction T1 read a data, T2 modifies it, and when T1 reads the data again, it gets a different value than the previous one.

(4) Generate ghost data after some records have been read from the database according to certain conditions, T2 deleted some of the records, and when T1 again reads the data in the same condition, some records disappear T1 Some records are read from the database after certain conditions, T2 insert some record. When T1 again reads the data in the same condition, it finds some more records.

Understanding of database transactions