Transactions in the database

I. Concepts

Databasetransaction refers to a series of operations performed as a single logical unit of work. 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.

 

Ii. Attributes

Atomicity: a transaction is a complete operation. The operations in each step of the transaction cannot be divided (atomic); either executed or not executed

Consistency: when the transaction is completed, the data must be in the consistent state.

Isolation: all concurrent transactions that modify data are isolated from each other, which indicates that the transaction must be independent and should not depend on or affect other transactions in any way.

Durability: after the transaction is completed, its modifications to the database are permanently maintained, and the transaction log can keep the transaction permanent.

 

3. Procedure

Deepen understanding based on the transaction status chart

Connection. setautocommit (false); // open the transaction

Connection. Commit (); // submit the transaction

Connection. rollback (); // roll back the transaction

Based on the comments of the above three statements, I think my initial understanding is still insufficient.

Now I have this requirement: I want to deduct 10 yuan from the user whose ID is 1 and add it to the user whose ID is 2. But what if an exception occurs in the middle?

Code before modification:

When this method is called, it is found that the user whose ID is 1 has been deducted, but the user whose ID is 2 has not been increased, because in this process, due to exceptions, the subsequent statements are no longer executed normally. This is obviously a problem. No one wants to see it: I originally wanted to pay back the money of my classmates, I found that my money was deducted from my card, but my classmates said that he didn't receive the money. The Bank is responsible.

In order to avoid this situation from happening again, we can put the statement that executes the money and the money to be deducted into a transaction for processing. Once an exception occurs, we should not commit the entire transaction, make sure that the money in the account is correct.

Code after modification:

After this change, when an exception occurs, the user ID = 1 will not be charged more, and the user ID = 2 will not have more money for no reason, if no exception occurs, the transfer service will be correctly executed.

4. Processing Model

There are three transaction models:

1. Implicit transactions refer to each data operation statement automatically becoming a transaction. Each transaction has an explicit start and end mark.

2. explicit transactions refer to transactions with explicit start and end tags. The start of a transaction is implicit and the end of a transaction is clearly marked.

3. Automatic transactions are automatically defaulted by the system, and start and end are not marked.

Control category

A major feature of the database system is that multiple users share database resources, especially multiple users can access the same data at the same time. The following are the control categories:

1. serial control: if the transaction is executed sequentially, that is, after one transaction is completed, start another transaction.

2. Parallel Control: If the DBMS can accept multiple transactions at the same time, and these transactions can be executed in time overlap.

Concurrency Control Overview

Transactions are the basic unit of concurrency control. Ensuring ACID properties of transactions is an important task of transaction processing, and concurrent operations may destroy its ACID properties.

Responsibilities of the DBMS concurrency control mechanism:

Correct scheduling of concurrent operations ensures the isolation of transactions and Database Consistency.

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 have produced an electronic copy of the same document. Each editor independently changes its copy and saves the modified copy to overwrite the original document. Finally, the edited member who saves the change copy 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 an electronic document. 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 change was incorrect, and deleted the edit and saved the document. Documents distributed to users include edited content that no longer exists, and such edited content should never be considered as existent. This issue can be avoided if no one can read the modified document before the first editor determines the final change.

(3) Non-repeated read

After transaction T1 reads data, transaction T2 performs an update operation, so that T1 cannot read the previous result. There are three cases of non-repeated reading:

After transaction T1 reads a certain data, T2. when T1 reads the data again, it gets a different value from the previous one.

(4) generate Ghost data

After reading some records from the database based on certain conditions, T2 deletes some of the records. When T1 reads data again based on the same conditions, 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.

The above is a superficial understanding of transactions, hoping to be used slowly in the future and learn the truth in practice.