Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what are the knowledge points related to database transactions?" in the operation of actual cases, many people will encounter such a dilemma, and then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. introduction of affairs

Transaction is a concurrency control unit, is a user-defined sequence of operations, these operations are either done or not done, is an indivisible unit of work.

Transactions usually start with BEGIN TRANSACTION and end with COMMIT or ROLLBACK.

Second, the characteristics of the transaction

Atomicity: a transaction is the logical unit of work of a database, and either all or none of the operations in the transaction are done.

Consistency: the result of transaction execution must be to change the database from one consistency state to another.

Isolation: the execution of one transaction cannot interfere with other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

Persistence: once a transaction is committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not have any impact on the results of their execution.

III. Problems under concurrency

When multiple threads open the data in the transaction operation database, the database system should be able to carry out isolation operations to ensure the accuracy of each thread to obtain data. Before introducing the various isolation levels provided by the database, let's take a look at several problems that can occur without considering the isolation of transactions:

Dirty reading

Dirty reading refers to reading data from another uncommitted transaction during one transaction.

For example, a transaction has updated a piece of data, another transaction has read the same data at this time, and for some reason, the previous transaction rolls back the operation, then the data read by the latter transaction will be incorrect dirty data.

Non-repeatable

Unrepeatable reading means that for a certain data in the database, multiple queries within a transaction range return different data values, which is due to being modified and committed by another transaction during the query interval.

For example, transaction An is reading a certain data, and transaction B immediately modifies the data and commits the transaction to the database. Transaction A gets different results when it reads the data again, and can not be read repeatedly.

Illusory reading

The discrepancy in the number of data in two queries of a transaction is a phenomenon that occurs when the transaction is not executed independently.

For example, one transaction queries several columns of data, while another transaction inserts new columns of data at this time. In the following query, the previous transaction will find several columns of data that it did not have before.

Contrastive understanding

The difference between unrepeatable reading and dirty reading is that one transaction reads dirty data not committed by another transaction, while non-repeatable reading reads data committed by the previous transaction (problems may occur in some scenarios when reading the data committed by the previous transaction).

Both phantom reading and unrepeatable reading read another committed transaction, except that the unrepeatable query is the same data item, while phantom reading is aimed at a batch of data as a whole.

IV. Transaction isolation level

Read uncommit (read unsubmitted)

Read commit (read submitted)

Repeatable read (repeatable)

Serializable (serialization)

Read unsubmitted

At this isolation level, all transactions can see content data that has not been committed by other transactions. This isolation level does not solve any concurrency problems and is not commonly used.

Read submitted

At this isolation level, one transaction can only read content data that has been committed by other transactions. This isolation level addresses dirty reads, but not unrepeatable and phantom reads, and is the default isolation level for ORACLE.

Repeatable read

At this isolation level, it is guaranteed that multiple instances of a transaction can read the same data concurrently. This isolation level addresses dirty and non-repeatable reads and is the default level for MYSQL.

Serialization

This is the highest isolation level, where transactions can only be executed sequentially so that there are no conflicts. Serialization solves dirty reading, non-repeatable reading and phantom reading.

The higher the isolation level, the better the integrity and consistency of the data, but the lower the efficiency for concurrency. Therefore, it is not that the higher the isolation level, the better. The appropriate transaction isolation level should be selected according to the specific business scenario.

This is the end of the content of "what are the knowledge points related to database transactions"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.